Why does NOT IN (subquery) return zero rows when the subquery contains a NULL, and how do you fix it?

How to think about it

This is one of SQL’s most dangerous silent bugs — the query runs without error, looks right, and returns zero rows when it should return many. Interviewers ask it to see whether you understand three-valued logic and whether you reach for the safe pattern reflexively.

Why it breaks — step by step

Say you want customers who did not order in month 6:

SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id FROM orders WHERE order_month = 6
);

If even one row in that subquery has a NULL customer_id, the engine expands the predicate to:

NOT (customer_id = 1 OR customer_id = NULL)

customer_id = NULL evaluates to UNKNOWN — not true, not false. Then x OR UNKNOWN is UNKNOWN (unless x is true), and NOT UNKNOWN is still UNKNOWN. Since a WHERE only passes rows that are true, every row is filtered out:

customer_id	name
(zero rows)

No error, no warning — just an empty result that looks like a data problem. With a NULL in the orders table, the query that should list three customers lists none.

The fix — NOT EXISTS is NULL-safe

NOT EXISTS tests row-by-row existence instead of building a value list, so a NULL simply fails to match rather than poisoning the whole predicate:

SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.order_month = 6
);

customer_id	name
2	Bea
3	Chen
4	Dara

Three customers, correctly. Aarav is excluded because he did order in month 6; Bea, Chen, and Dara did not — note Bea’s only order was in month 5, so she belongs here too. The NULL-keyed order is simply ignored rather than wiping out the result.

The LEFT JOIN anti-join is equally NULL-safe and sometimes reads better to analysts from a pandas background:

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
 AND o.order_month = 6
WHERE o.customer_id IS NULL;

NOT EXISTS often wins on speed because it short-circuits at the first match. A third option — appending AND customer_id IS NOT NULL to the subquery — works but is fragile: you must remember it every time, and a future nullable-column change silently reintroduces the bug.