The NOT IN NULL trap: why a result becomes empty

NOT IN compares the left value with every value in its set. If a subquery returns one NULL and the left value finds no definite match, the combined condition becomes UNKNOWN. WHERE keeps only TRUE, so every apparently unmatched row can disappear.

This behavior follows SQL three-valued logic. x NOT IN (1, NULL) is equivalent to x <> 1 AND x <> NULL, and the second comparison is never TRUE. The defect often appears only when production data contains a NULL absent from test fixtures.

When the business question is “find objects without a related row,” NOT EXISTS usually states the intent directly. Put the relationship predicate inside the correlated subquery; each left row is checked for a match and an unrelated NULL cannot poison the entire set.

If NOT IN remains appropriate, back the subquery column with a trusted NOT NULL constraint or filter NULL explicitly. Handle an application-generated empty list separately too, because NOT IN () is invalid MySQL syntax and an empty array cannot simply be expanded.

Tests should cover a NULL in the subquery, an empty subquery, a NULL on the left, duplicate keys, and both matched and unmatched rows. These boundaries keep an anti-join correct as data changes.