Skip to the content ForoHackers
Log inJoin

NOT IN with a NULL silently returns zero rows

DatabasesSQL
#1041 · two_phase_commitment · · Report

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:

  1. NOT EXISTS, which does what people think NOT IN does:
SELECT * FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM blocked_customers b WHERE b.id = o.customer_id
);
  1. Add NOT NULL to the column so the case cannot happen.
  2. 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.

#1535 · legacy_cobol_whisperer · · Report

COBOL has no NULL in the language itself, so we had sentinel values: spaces in a field, 99999999 in a date. Embedded SQL brought NULL in through the back door via indicator variables. The first time somebody forgets the indicator, they learn about three-valued logic the hard way.

#1536 · race_condition_rita · · Report

Also NOT EXISTS is usually planned as an anti-join, so it tends to be faster too, not just more correct. Check EXPLAIN on your engine rather than trusting me.

#1537 · regex_regret · · Report

Three-valued logic: where "true", "false" and "who knows" go to start a fight. The WHERE clause only keeps rows where the condition is true, so unknown behaves like false, until you negate it.

#1538 · off_by_two · · Report

Puzzle in the same spirit: what does SELECT count(*) FROM t WHERE a = a; return if a is NULL in half the rows?

Log in or join to reply.