The NOT IN + NULL Trap: Why Your SQL Query Returns No Rows

Continuing in the same spirit as my previous article, “Silent SQL Bugs,” I wanted to keep the series going with another one from the same family of sneaky issues, the NOT IN + NULL trap.

Even though many developers call it a “silent SQL bug”, technically it is not a SQL bug. It just feels like one because the query runs successfully, SQL throws no errors or warnings, yet the result is wrong. That’s what makes it quite dangerous, because everything looks valid while the mistake is purely logical.

But the key point here is that SQL is doing exactly what it was designed to do. The unintuitive behaviour comes from SQL’s three-valued logic, which I want to briefly cover first, because it explains why this is “correct” SQL behaviour with confusing consequences

SQL’s Three-Valued Logic (Why NULL Breaks Your Intuition)

The first time I ran into this, it was really hard to debug because I was convinced my logic was correct, but no results came back. Then I learned that SQL doesn’t only work with TRUE and FALSE. It has three logical values:

• TRUE
• FALSE
• UNKNOWN → this is what you get when NULL is involved in a comparison

So when you use NOT IN (subquery) and that subquery returns even a single NULL, the comparison for every value becomes UNKNOWN, not TRUE. And because a WHERE clause only returns rows where the condition is TRUE, everything gets filtered out , even valid rows.

Let’s break it down through the examples.

The first example returns nothing:

SELECT LOGIN, *
FROM dbo.EMPLOYEES
WHERE LOGIN NOT IN (
    SELECT LOGIN
    FROM dbo.TERMINATED_EMPLOYEES
);

Now, if we use the ISNULL(LOGIN, ''), it works fine:

SELECT LOGIN, *
FROM dbo.EMPLOYEES
WHERE LOGIN NOT IN (
 SELECT ISNULL(LOGIN, '')
 FROM dbo.TERMINATED_EMPLOYEES
);

This works because ISNULL(LOGIN, '') removes NULLs from the subquery, so the NOT IN list contains only real values or empty strings.

Or, even better, if we rewrite the query using NOT EXISTS:

SELECT e.LOGIN, *
FROM dbo.EMPLOYEES e
WHERE NOT EXISTS (
 SELECT 1
 FROM dbo.TERMINATED_EMPLOYEES te
 WHERE e.LOGIN = te.LOGIN
);

Why This Happens

If your subquery includes even one NULL, this condition:

value NOT IN (1, 2, 3, NULL)

becomes logically equivalent to:

value <> 1  -- TRUE 
AND value <> 2 -- TRUE
AND value <> 3 -- TRUE
AND value <> NULL --UNKNOWN

As soon as SQL hits that UNKNOWN, the whole condition stops being TRUE, and the row is filtered out. So if there’s even a single NULL in that subquery, your NOT IN condition will return no rows at all.

The correct and safe way is to use NOT EXISTS. It handles NULLs safely and is logically correct. Also, it is usually better for performance and a more professional way to write the query. NOT EXISTS checks row by row whether a matching row exists in the subquery. If there is no matching row, the condition is TRUE; if there is at least one match, it’s FALSE.

Also, EXISTS/NOT EXISTS do not depend on building a list that can contain NULLs, so they don’t get confused by NULL in the same way NOT IN does.

Rule to remember

• **IN + NULL → fine**
• **NOT IN + NULL → dangerous**
• **NOT EXISTS → safe and recommended**

NOT IN + NULL = unpredictable and often empty results, even when you expect rows back.

This is why many SQL developers avoid NOT IN completely and prefer NOT EXISTS as it is safer, clearer, and less prone to “silent bugs”.

Skip NOT IN when NULLs are possible. Use NOT EXISTS.

To wrap it up, I just want to emphasize one more time how dangerous this pattern is precisely because everything looks fine: the query runs, there are no errors, no warnings, just quietly wrong results. Once you really understand how NULL and three-valued logic interact with NOT IN, choosing NOT EXISTS stops being a “trick” and becomes the natural, safer default for writing reliable SQL.

Thank you for reading!

References:

1. https://www.sqlservercentral.com/blogs/not-exists-vs-not-in
2. https://www.geeksforgeeks.org/sql-server/what-is-the-difference-between-not-exists-and-not-in-sql-server/
3. https://stackoverflow.com/questions/129077/null-values-inside-not-in-clause
4. https://stackoverflow.com/questions/41180547/is-sql-unknown-identical-to-null
5. https://stevengong.co/notes/Three-Valued-Logic
6. https://modern-sql.com/concept/three-valued-logic
7. https://sqlchitchat.com/sqldev/tsql/three-value-logic-null-in-sql/