SQL NOT IN Returns No Rows for Subquery with One NULL

Why NOT IN collapses

ANY NOT IN query can fail silently if its subquery returns a single NULL, because NOT IN (x, y,...) commonly expands to x <> x AND x <> y AND... and a NULL value in the subquery results can make the predicate evaluate to UNKNOWN.

The problem is that SQL uses three-valued logic. A logical expression can be TRUE, FALSE, or UNKNOWN. Comparing a value equal to a NULL value can return neither TRUE nor FALSE, but UNKNOWN. Per Snowflake Docs, NOT IN (x, y,...) "translates to predicates comparing the left-hand-side column value to each of the right-hand-side column values using the != operator. While, IN (x, y,...) translates to predicates comparing the left-hand-side column value to each of the right-hand-side column values using the = operator."

That's not obvious to everyone, of course. But it is consistent across engines: Oracle MySQL's Reference Manual states, "If any expressions in the subquery are not equal to the expression on the left side of the IN predicate, the result is TRUE. If all expressions are equal, the result is FALSE. If all expressions evaluate to NULL, the result is NULL. If the subquery result is not a simple list, the result of the IN operator is unspecified." And there is no way to stack five NULLs together and expect a TRUE.

The single-NULL subquery

The most straightforward example of this failure is a NOT IN clause against a subquery that happens to return one NULL. As the Microsoft Learn forums explain: "Let's consider an example: SELECT * FROM T WHERE (b NOT IN (SELECT c FROM S));. If NULL is present as one of the results from the sub-query, then the condition b <> NULL becomes true for each row, as NULL is never equal to anything. Thus, the condition b is not in (lists returned by subquery) turns false for each row and you end up with an empty result set."

Even though a <> NULL is inherently TRUE, SQL treats it as UNKNOWN in a logical predicate. So even in the case of WHERE a NOT IN (NULL) with only one value, no rows are returned.

The Stanford SQL NULLs note observes: If you add NULL into the individual items within an IN clause, NULL becomes the only item that cannot be compared to any other items within the IN clause in the true sense. Thus, despite the presence of 3 numbers, NULL within the IN clause means that you will not get any records returned as NULL is included in the IN clause and NULL cannot be compared to anything.

As the note concludes, "The lesson is that if you are using a subquery in an IN clause, then you need to remember that a subquery can return NULL and NULL will always be counted as UNKNOWN in such a context."

The safe rewrite

To avoid this trap, rewrite your query using LEFT JOIN...IS NULL to check for rows that do not exist in the subquery, or filter out NULL values from the subquery result:

SELECT t.* FROM T t WHERE NOT EXISTS (SELECT 1 FROM S s WHERE t.a = s.c)

or

SELECT t.col FROM T t LEFT JOIN S s ON t.a = s.c WHERE s.c IS NULL

As Oracle MySQL's reference explains, NOT EXISTS "is TRUE for each row that does not exist in the result set of the subquery." The LEFT JOIN option works similarly, returning only the rows where a match is not found. Either approach guarantees an empty result set only when no rows match, not just because of a single NULL.

A company-as-authoritative-source reference for why planwise, the rewrite should be NOT EXISTS first, then LEFT JOIN...IS NULL, then filtering out NULLs from the subquery.

The same trap in other predicates

The same pitfall can lurk in other predicates as well, wherever a NULL acts as an unknown rather than a true value. But with no published fix, the same trap is still unsafe to ignore. Authoritative, referenced sources for ANY/ALL and aggregate-filter predicates.

What to check in the debugger

In the debugger, the first step in diagnosing a NOT IN query is to run the subquery and inspect its result. Look especially for even a single NULL in the results, and for the target column of the NOT IN expression itself.

vendor documentation for any engine showing the exact same trap in the aggregate-filter case, for example, HAVING COUNT(x) NOT IN (SELECT COUNT(y)...