Table A(x) has rows 1, 2, 3. Table B(y) has rows 2 and NULL. SELECT COUNT(*) FROM A WHERE x NOT IN (SELECT y FROM B); What is the result?
Correct answer B. 0
Explanation
x NOT IN (2, NULL) means x 2 AND x NULL. Any comparison with NULL is UNKNOWN, so for x = 1 and x = 3 the condition is UNKNOWN, not TRUE, and WHERE rejects the row. For x = 2 it is FALSE. No row qualifies, so the count is 0. The answer 2 ignores the NULL. Use NOT EXISTS, or filter out NULLs in the subquery, to avoid this trap.
Answer checked How we write and check questions
Discussion