SELECT * FROM orders o WHERE o.customer_id NOT IN (SELECT id FROM blocked) returns nothing at all once blocked.id holds a single NULL. No error. Zero rows.
The reason is mechanical. x NOT IN (a, b, NULL) means x <> a AND x <> b AND x <> NULL. The last comparison is unknown, never true. An AND with an unknown operand can come out false or unknown, but never true, and WHERE keeps only the rows where the condition is true.
There are three repairs and they are not equivalent.
- Filter the subquery with
WHERE id IS NOT NULL. Correct, but every future author has to remember it. - Rewrite as
NOT EXISTS (SELECT 1 FROM blocked b WHERE b.id = o.customer_id). A NULL inblockednever matches anything, so it blocks nobody. This is the one I reach for. - Declare
blocked.idNOT NULL. Best of the three if the column was never meant to hold unknowns, and for a list of blocked ids it wasn't.
IN has the mirror problem in a milder form. It can return unknown where you expected false, which only hurts once something negates it.
