Blog article
NOT EXISTS vs NOT IN: the NULL trap in SQL Server
An anti-join should find rows with no match. A subquery that returns NULL can make NOT IN hide every row; use a small, runnable example to see why and choose the right pattern.
On this page7 sections
A report asks for customers without orders. A NOT IN query looks reasonable but returns no customers. The orders list contains one NULL, and that changes the truth value of every unmatched comparison. This is a three-valued logic problem, not a database error.
The small data set
The following examples build three customer IDs and an order list containing customer 1 plus one missing customer ID. They do not read or change your practice database. The expected customers without a matching non-NULL order are 2 and 3.
NOT IN returns no rows
This query returns no rows. For customer 2, 2 <> 1 is true but 2 <> NULL is UNKNOWN. True AND UNKNOWN is UNKNOWN, and WHERE keeps only TRUE. Customer 1 is excluded because it matches 1.
WITH Customers(CustomerID) AS (SELECT v.CustomerID FROM (VALUES (1), (2), (3)) AS v(CustomerID)),
Orders(CustomerID) AS (SELECT v.CustomerID FROM (VALUES (1), (NULL)) AS v(CustomerID))
SELECT c.CustomerID
FROM Customers AS c
WHERE c.CustomerID NOT IN (SELECT o.CustomerID FROM Orders AS o)
ORDER BY c.CustomerID;Use NOT EXISTS for the anti-join
NOT EXISTS returns the unmatched customers
The correlated predicate looks for a matching order for each customer. The NULL order ID matches no customer ID, so it cannot hide customers 2 and 3.
WITH Customers(CustomerID) AS (SELECT v.CustomerID FROM (VALUES (1), (2), (3)) AS v(CustomerID)),
Orders(CustomerID) AS (SELECT v.CustomerID FROM (VALUES (1), (NULL)) AS v(CustomerID))
SELECT c.CustomerID
FROM Customers AS c
WHERE NOT EXISTS (
SELECT 1 FROM Orders AS o WHERE o.CustomerID = c.CustomerID
)
ORDER BY c.CustomerID;| CustomerID |
|---|
| 2 |
| 3 |
When NOT IN is safe
Filtering NULL from the subquery makes NOT IN work for this example, provided the outer CustomerID is also non-NULL. If the outer value can be NULL, NOT EXISTS and NOT IN still have different semantics: NOT EXISTS can retain that outer row when no equality matches it.
A filtered NOT IN gives the same result here
The inner list now contains only 1, while all outer customer IDs are non-NULL.
WITH Customers(CustomerID) AS (SELECT v.CustomerID FROM (VALUES (1), (2), (3)) AS v(CustomerID)),
Orders(CustomerID) AS (SELECT v.CustomerID FROM (VALUES (1), (NULL)) AS v(CustomerID))
SELECT c.CustomerID
FROM Customers AS c
WHERE c.CustomerID NOT IN (
SELECT o.CustomerID FROM Orders AS o WHERE o.CustomerID IS NOT NULL
)
ORDER BY c.CustomerID;| CustomerID |
|---|
| 2 |
| 3 |
Duplicate matches do not change the answer
An anti-join asks whether at least one matching inner row exists. If a customer has ten orders, NOT EXISTS still excludes that customer once; it does not multiply the outer customer rows. Conversely, a customer with no orders appears once if Customers contains one row for that customer. If the outer input itself contains duplicates, the anti-join preserves them. Add DISTINCT only when the report actually requires distinct outer keys.
What if the outer key is NULL?
The example deliberately uses non-NULL customer IDs, like a primary key. If an outer CustomerID is NULL, the equality predicate o.CustomerID = c.CustomerID never becomes TRUE for that row under normal SQL Server NULL comparison semantics. NOT EXISTS would therefore retain that outer row. A filtered NOT IN still evaluates NULL NOT IN (1) to UNKNOWN and removes it. Decide explicitly whether missing outer IDs should be included; a WHERE c.CustomerID IS NOT NULL condition can enforce exclusion.
Check the real schema
A declared NOT NULL constraint on the inner column removes this particular NULL hazard, but do not assume one exists. For an anti-join, NOT EXISTS expresses the intention clearly and avoids the nullable-list trap. Compare plans and indexes separately if performance matters; syntax alone does not guarantee a faster plan.
A check for a real report
Before shipping an anti-join report, inspect nullability on both keys and test three cases: a matching row, an unmatched row, and a NULL inner key if the schema allows it. If the source has an unexpected NULL foreign key, investigate its meaning instead of merely filtering it away. The small example isolates SQL semantics; a production query also needs an appropriate index and a plan check on real data.