One NULL in the List and NOT IN Returns Nothing
NOT IN and NOT EXISTS read like the same thing. Find the rows that are not in that other set.
They agree until the other set contains a NULL, at which point NOT IN returns no rows at all.
Five items, two exclusions
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE #items ( [item_id] int NOT NULL ); CREATE TABLE #excluded ( [item_id] int NULL ); INSERT INTO #items ([item_id]) VALUES (1), (2), (3), (4), (5); INSERT INTO #excluded ([item_id]) VALUES (2), (4); |
Three items survive the exclusion, and both forms agree:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT [not_in_rows] = ( SELECT COUNT_BIG(1) FROM #items AS [i] WHERE [i].[item_id] NOT IN (SELECT [e].[item_id] FROM #excluded AS [e]) ) , [not_exists_rows] = ( SELECT COUNT_BIG(1) FROM #items AS [i] WHERE NOT EXISTS ( SELECT 1 FROM #excluded AS [e] WHERE [e].[item_id] = [i].[item_id] ) ); |
|
1 2 3 |
not_in_rows not_exists_rows ----------- --------------- 3 3 |
Now add one NULL to the exclusion list and run exactly the same query:
|
1 2 |
INSERT INTO #excluded ([item_id]) VALUES (NULL); |
|
1 2 3 |
not_in_rows not_exists_rows ----------- --------------- 0 3 |
Verified on SQL Server 2019 (15.0.4480.2) and SQL Server 2025 (17.0.1125.2). Same result on both.
Why zero
NOT IN is defined as the negation of the IN predicate, which expands to a chain of inequality comparisons joined by AND.[2] The predicate
|
1 |
[i].[item_id] NOT IN (2, 4, NULL) |
means
|
1 |
[i].[item_id] <> 2 AND [i].[item_id] <> 4 AND [i].[item_id] <> NULL |
Comparing anything to NULL yields UNKNOWN rather than true or false. 1 <> NULL is UNKNOWN. And TRUE AND UNKNOWN is UNKNOWN, which a WHERE clause treats as not qualifying.[1]
So every row fails, including the ones that are obviously not 2 or 4. The predicate cannot return true for any row once a NULL is present.
NOT EXISTS asks a different question. It tests whether the subquery produced any row and returns true or false, with no third outcome.[3] A NULL in #excluded fails to match [e].[item_id] = [i].[item_id], contributes nothing, and the outer row survives.
This is a data-dependent bug
The query is correct until the data changes.
A nullable column with no NULLs in it today behaves identically under both forms. The code passes review, passes test, and runs for a year. Then one NULL arrives, from an import, a new code path, or a column that was made nullable in a later release, and a report starts returning nothing.
There is no error to catch and no plan regression to notice. The result set is simply empty, and empty is a plausible answer for plenty of queries.
The plan shape
The correctness argument is enough on its own, but the plans differ too, and by more than I expected.
I built 100,000 items and 10,000 exclusions, indexed the exclusion column, and compiled all three variants under SET SHOWPLAN_XML ON:
| Query | Estimated subtree cost | Plan |
|---|---|---|
NOT IN, nullable column |
10.60 | Parallelism, Hash Match, Row Count Spool, Nested Loops |
NOT EXISTS, same column |
0.51 | Merge Join |
NOT IN, NOT NULL column |
0.51 | Merge Join |
On SQL Server 2025 the numbers were 10.79, 0.51, and 0.51. Roughly a twentyfold difference in estimated cost between the first two, and the NOT IN variant went parallel to do it.
The Row Count Spool is the reason for the cost. It exists to answer the question “did the subquery produce any NULL”, because the answer determines whether the entire predicate collapses to UNKNOWN. That check has to happen, so the optimizer builds machinery for it.
NOT EXISTS needs none of that. It expresses an anti-semi-join directly and the optimizer picks a merge join over the two ordered inputs.
Declare the subquery column NOT NULL and NOT IN produces a plan identical to NOT EXISTS, cost and all. The optimizer knows the NULL branch is impossible and drops the spool and the operators feeding it.
That explains why the difference sometimes fails to reproduce in testing. On a NOT NULL column there is nothing to be careful about, and the two forms are interchangeable.
Relying on that is fragile in a different way. The correctness and the cost of your query then depend on a column staying NOT NULL, which is a schema property somebody can change without reading your query.
One caveat on those numbers: I first ran this against empty tables and got identical plans for all three, which would have led me to say the difference did not exist. Cardinality matters for this comparison, so test it with data that resembles yours rather than with a toy table.
The rule
Use NOT EXISTS for exclusion subqueries. Reserve NOT IN for lists of literals you wrote yourself, where you can see there is no NULL.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
/* Exclusion against another table */ WHERE NOT EXISTS ( SELECT 1 FROM [dbo].[excluded_items] AS [ex] WHERE [ex].[item_id] = [t].[item_id] ) /* A short literal list is fine */ WHERE [t].[status_code] NOT IN (N'CANCELLED', N'ARCHIVED') |
SELECT 1 inside NOT EXISTS is conventional and the column list is never evaluated, so it makes no difference what you put there.
The same three-valued logic underlies a related annoyance: comparing two nullable columns for inequality when you want NULLs to count as different. IS DISTINCT FROM covers that, and the NULL bitmap covers how the engine records nullability on the page.
This is one of a series on the T-SQL conventions I actually use and why. Also in it: ISNULL Truncates Your Replacement Value; COALESCE Doesn’t and Double-Hyphen Comments Can Comment Out Your WHERE Clause.
Have you had a NOT IN start returning nothing after a data change? Bluesky or LinkedIn.
References
- NULL and UNKNOWN (Transact-SQL) – Microsoft Learn. Comparisons involving NULL evaluate to UNKNOWN, and a WHERE clause returns only rows for which the predicate is true. ↩
- IN (Transact-SQL) – Microsoft Learn. Defines NOT IN as the negation of the IN predicate, which is what produces the AND chain of inequality comparisons. ↩
- EXISTS (Transact-SQL) – Microsoft Learn. EXISTS tests for the presence of rows and returns true or false, with no third outcome. ↩