Question discussion

What does the following query return?CREATE TABLE a(x INT); INSERT INTO a VALUES (1),(2),(3); CREATE TABLE b(y INT); INSERT INTO b VALUES (…

2544Medium DSSSB TGT Computer Science 0 replies 1 views

What does the following query return?

CREATE TABLE a(x INT);
INSERT INTO a VALUES (1),(2),(3);
CREATE TABLE b(y INT);
INSERT INTO b VALUES (2),(3),(4);
SELECT COUNT(*), COUNT(b.y) FROM a LEFT JOIN b ON a.x = b.y AND b.y > 2;

A3 3
B1 1
C3 1
D2 1

Correct answer: C

Explanation

The extra condition b.y > 2 in the ON clause only decides which rows match, and every row of a is still kept, so COUNT(*) = 3, but only x = 3 finds a partner, so COUNT(b.y) = 1.

Join the discussion Report an error

Discussion 0

Nobody has replied yet. Start the discussion!