Caso Abierto Español

Field manual · SQL traps

Why does NOT IN return zero rows?

NOT IN against a list with a single NULL throws out the whole query without a warning. It is the worst possible failure: it doesn't crash, it gives false confidence.

Where it shows up

Any anti-join (what is missing, what has no match, what never got logged) written with NOT IN over a column that allows NULL. A classic SQL interview question, because it looks harmless.

Example

Wrong: NOT IN with a NULL in the list

SELECT name
FROM people
WHERE name NOT IN (
  SELECT name FROM alibis
);
-- alibis has one row with name NULL (an unsigned receipt):
-- the whole query returns zero rows, even though suspects with no alibi exist

Right: NOT EXISTS, which isn't afraid of nulls

SELECT name
FROM people p
WHERE NOT EXISTS (
  SELECT 1 FROM alibis a WHERE a.name = p.name
);
-- or, filtering the NULL out of the list itself:
WHERE name NOT IN (
  SELECT name FROM alibis WHERE name IS NOT NULL
)

x NOT IN (a, b, NULL) is not a list with a gap in it: it's x<>a AND x<>b AND x<>NULL, and that last comparison evaluates to NULL, not true or false. A conjunction with a NULL in it never evaluates to true, so no row passes the filter, no matter how many names are genuinely missing. NOT EXISTS doesn't compare against the whole list at once: it asks row by row, so one NULL in the subquery doesn't poison the rest. The other fix is stripping the NULL before it reaches NOT IN, but that means remembering to do it every time; NOT EXISTS is the safe default.