positions has 4,000 rows and universe(symbol) has 8,000 rows of which 3 are NULL. What does SELECT count(*) FROM positions WHERE symbol NOT IN (SELECT symbol FROM universe) return, and what do NOT EXISTS and a LEFT JOIN return instead?

positions has 4,000 rows and universe(symbol) has 8,000 rows of which 3 are NULL. What does SELECT count(*) FROM positions WHERE symbol NOT IN (SELECT symbol FROM universe) return, and what do NOT EXISTS and a LEFT JOIN return instead?

Approach: Expand NOT IN into a chain of inequality comparisons and evaluate the truth value of that chain when one of the comparisons is unknown.

0. NOT IN expands to symbol <> u1 AND symbol <> u2 AND so on over every subquery row, and a comparison against NULL evaluates to UNKNOWN under three valued logic. UNKNOWN ANDed with TRUE is UNKNOWN, and WHERE keeps only rows evaluating to TRUE, so every row is discarded and the count is 0 whatever the data holds. NOT EXISTS returns the correct anti join count, because the correlated subquery asks only whether a matching row exists and a NULL in universe simply fails to match. LEFT JOIN universe u ON p.symbol = u.symbol WHERE u.symbol IS NULL returns the same correct count for the same reason. The null semantics also change the plan: most planners rewrite NOT EXISTS and the left join pattern into a hash anti join, while NOT IN over a nullable column blocks that rewrite and leaves a correlated scan, so the query is both wrong and slower. Declaring universe.symbol NOT NULL removes the trap at the source and is the fix to prefer, since the next person to write NOT IN will otherwise repeat it.

Follow-up: How does the answer change if the outer column is nullable and the subquery column is not?

Key concepts: three valued logic, anti join, not exists, null semantics.