Browse Teach Curate

EXISTS, IN, and the NOT IN Trap When the Subquery Returns NULL

EXISTS stops at the first matching row, IN builds the whole list first, and modern planners usually treat the two the same way — but NOT IN is different, because a single NULL in the subquery's results makes the whole condition return no rows at all. NOT EXISTS has no such trap and is usually planned as an anti-join. The rule is: use EXISTS and NOT EXISTS for existence tests, and never use NOT IN against a column that can be NULL.

Questions this Concept answers

Why does a single NULL empty out a `NOT IN` result?

jeremy Video

How a NULL in a Subquery Makes SQL NOT IN Return Zero Rows Under Three-Valued Logic

SQL evaluates predicates in three-valued logic, where any comparison involving NULL yields UNKNOWN rather than TRUE or FALSE, and a WHERE clause admits a row only when its predicate evaluates to TRUE…