The NOT IN + NULL Trap — Solution & ExplanationProblems / The NOT IN + NULL Trap / EditorialApproach

The tempting query is WHERE customer_id NOT IN (SELECT customer_id FROM orders) — and here it returns zero rows.

Why: orders.customer_id contains NULLs (guest checkouts), and x NOT IN (list) is sugar for x <> v1 AND x <> v2 AND ... AND x <> NULL. Under SQL's three-valued logic any comparison with NULL is UNKNOWN, so the whole conjunction is at best UNKNOWN, never TRUE — and WHERE only keeps rows that evaluate to TRUE. Every customer is silently filtered out.

Fix it with NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id). NOT EXISTS only asks "did any row match the correlation predicate?" — NULL customer_ids never match o.customer_id = c.customer_id, so they are harmlessly ignored. (Alternatively, keep NOT IN but filter WHERE customer_id IS NOT NULL inside the subquery.)

Order by customer_id.

Key InsightNOT IN against a set containing NULL is the classic silent SQL failure: no error, no warning, just an empty result. IN degrades gracefully with NULLs (TRUE still wins the OR-chain), but NOT IN needs every comparison to be TRUE, and NULL poisons the chain. NOT EXISTS — and PySpark's left_anti join — implement anti-join semantics that treat NULLs as non-matches, which is what you almost always want.Hints

Run the naive NOT IN (SELECT customer_id FROM orders) version first — it returns zero rows even though several customers clearly never ordered. The data is not the problem.

x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL. What does x <> NULL evaluate to?

Any comparison with NULL is UNKNOWN, so the AND-chain can never be TRUE and WHERE keeps nothing. Use NOT EXISTS with a correlated subquery — or keep NOT IN but add WHERE customer_id IS NOT NULL inside the subquery.

In PySpark, a left_anti join from customers to orders sidesteps the trap entirely: NULL keys simply never match.

Solve The NOT IN + NULL Trap yourself →