Lessons · SQL · NOT IN meets a NULL
NOT IN and a single NULL
x NOT IN (list) is false if x is in the list and unknown if the list contains a NULL, so one NULL in the subquery makes every row disappear.
Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.
What it is for
'Customers who never ordered' written with NOT IN returns nothing the day an order arrives with a NULL customer_id. It looks like a bug in the data; it is a rule of three-valued logic.
How to think about it
Prefer NOT EXISTS, which asks 'is there a matching row' and is never fooled by NULL. If you keep NOT IN, filter the NULLs out of the subquery explicitly.
Worked example
SELECT name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);Empty as soon as one order has a NULL customer_id.
SELECT name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);Works: the NULL is kept out of the list.
SELECT name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);The robust form: no list, no NULL problem.
Your turn
Films nobody has reviewed, robustly.
SELECT title FROM films f WHERE NOT (SELECT 1 FROM reviews r WHERE r.film_id = f.id);
Run a query against real tables
The trap
IN is fine with NULLs in the list (a match is still a match); it is NOT IN that collapses. The asymmetry is what catches people.