✏️ Explanatory Question
NOT IN subquery return zero rows when the subquery contains a NULL?Level: Hard — One of the most infamous SQL traps; it silently returns wrong results with no error.
WHERE customer_id NOT IN (SELECT customer_id FROM orders) returns 0 rows — even though hundreds of customers clearly have no orders. No error is thrown. What went wrong?
The cause: The orders table has at least one row where customer_id is NULL. With NOT IN, MySQL expands the logic to x != a AND x != b AND x != NULL. Any comparison with NULL yields UNKNOWN, and something AND UNKNOWN can never be TRUE — so every row is filtered out.
NOT EXISTS.
| Expression | Evaluates To |
|---|---|
5 != 3 |
TRUE |
5 != NULL |
UNKNOWN (not TRUE!) |
TRUE AND UNKNOWN |
UNKNOWN |
| Row kept only if WHERE is | TRUE → so row is dropped |
-- orders has a row with customer_id = NULL
-- THE BUG: returns 0 rows because of the NULL in the subquery
SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
-- FIX 1 (BEST): NOT EXISTS is inherently NULL-safe
SELECT c.name FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- FIX 2: exclude NULLs from the subquery (works, but easy to forget)
SELECT name FROM customers
WHERE customer_id NOT IN (
SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);
-- FIX 3: LEFT JOIN ... IS NULL (the "anti-join")
SELECT c.name FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;NOT EXISTS over NOT IN whenever the subquery column could ever be NULL. IN is fine, but NOT IN + NULL is a landmine.
IN have the same problem?" → Answer: No. IN with a NULL just fails to match that NULL row but still returns other matches correctly. The danger is specifically with NOT IN, because of how the negation interacts with UNKNOWN.