A query that has hurt more people than any "advanced" feature:
SELECT * FROM orders
WHERE customer_id NOT IN (SELECT id FROM blocked_customers);
It works for months. One day blocked_customers gets a row where id is NULL, and the query returns nothing. No error, no warning.
Why: SQL uses three-valued logic (true, false, unknown). x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL. The last term is UNKNOWN, never true, so the whole AND can never be true for any x. You can try it:
SELECT 'row' WHERE 5 NOT IN (1, 2, NULL); -- returns no rows
SELECT 'row' WHERE 5 NOT IN (1, 2); -- returns 'row'
Fixes, in order of my preference:
NOT EXISTS, which does what people thinkNOT INdoes:
SELECT * FROM orders o
WHERE NOT EXISTS (
SELECT 1 FROM blocked_customers b WHERE b.id = o.customer_id
);
- Add
NOT NULLto the column so the case cannot happen. - Filter inside the subquery:
WHERE id IS NOT NULL.
Relationship tip while we are here: NULL is not equal to NULL. It is not even equal to itself. Commit to your constraints.