A report asks for customers outside an exclusion list and suddenly returns none. NOT IN with a NULL in the subquery can make every comparison unknown, even when most IDs plainly are not excluded. A five-row example makes the behavior visible and shows two safe rewrites.
Build a Five-Row Example
Use local temporary tables so the experiment is isolated and easy to repeat. Five customers have IDs 1 through 5. The exclusion list contains 2 and a NULL. The expected business answer is 1, 3, 4, and 5 if a blank exclusion entry is meant to exclude nobody. The data setup is explicit so we can compare both results and plans without relying on a live application table.
DROP TABLE IF EXISTS #Customers;
DROP TABLE IF EXISTS #Excluded;
CREATE TABLE #Customers (CustomerID int NOT NULL PRIMARY KEY);
CREATE TABLE #Excluded (CustomerID int NULL);
INSERT #Customers (CustomerID) VALUES (1),(2),(3),(4),(5);
INSERT #Excluded (CustomerID) VALUES (2),(NULL);
SELECT * FROM #Customers ORDER BY CustomerID;
SELECT * FROM #Excluded;This is a correctness test before it is a performance test. I start with the expected set written down. Without that, an empty grid can be mistaken for a fast query that found no eligible customers. What does NULL mean in the exclusion table: missing data, an unknown customer, or a deliberate marker? Resolve that business meaning first.
Watch NOT IN With a NULL Return Nothing
The direct query looks reasonable. It asks for a customer ID absent from the exclusion subquery. With the NULL present, the result is empty. The comparison to 2 excludes customer 2. For another customer, the comparison to NULL cannot be true or false; it is UNKNOWN. The WHERE clause keeps only TRUE rows, so none remain.
SELECT c.CustomerID
FROM #Customers AS c
WHERE c.CustomerID NOT IN
(SELECT e.CustomerID FROM #Excluded AS e)
ORDER BY c.CustomerID;Think of customer 1 as 1 <> 2 AND 1 <> NULL. The first part is TRUE, and the second is UNKNOWN, so the combined condition is UNKNOWN. SQL uses three-valued logic for NULL, rather than the two-valued logic many readers expect. This is why adding one bad row to a lookup table can make a report appear empty overnight.
Rewrite With NOT EXISTS
NOT EXISTS asks whether a matching exclusion row exists for this particular customer. A NULL in the exclusion table never matches a non-NULL CustomerID, so it leaves the other comparisons intact. The result is 1, 3, 4, and 5. This form also makes the matching relationship explicit when a real query has several key columns.
SELECT c.CustomerID
FROM #Customers AS c
WHERE NOT EXISTS
(
SELECT 1
FROM #Excluded AS e
WHERE e.CustomerID = c.CustomerID
)
ORDER BY c.CustomerID;I prefer this form when exclusion data can contain NULL and the intended rule is no matching row. It is still important to inspect the outer column. Here CustomerID never holds NULL. If the outer key can be NULL, decide whether a NULL outer row should be returned; the anti-join rewrite can return it because equality with NULL finds no match. The schema and the business rule must agree.
Or Filter the NULL Out Before NOT IN
If NOT IN reads most clearly for a particular query, make the subquery return only known IDs. Add IS NOT NULL inside the subquery. The result again is 1, 3, 4, and 5 in this example. The filter states that unknown exclusion values do not exclude a known customer. It should be a deliberate rule, not a patch pasted over unexamined data quality.
SELECT c.CustomerID
FROM #Customers AS c
WHERE c.CustomerID NOT IN
(
SELECT e.CustomerID
FROM #Excluded AS e
WHERE e.CustomerID IS NOT NULL
)
ORDER BY c.CustomerID;A constraint that rejects NULL on the underlying exclusion column also removes this particular risk and tells the optimizer more about the data. If NULL is an invalid entry, investigate the ingestion path and enforce that rule at the source. Filtering bad rows in every report leaves the bad data in place.
Compare Results and Actual Plans
Turn on Include Actual Execution Plan in SSMS and run the three SELECT statements together. Inspect the result grids first: zero, four, and four rows. Then inspect the plan shape. SQL Server can implement these predicates with anti semi joins, filters, probes, or spools depending on cardinality, nullability, statistics, indexes, and version. The exact icons are not the logical rule. The wrong result stays wrong even if its plan is faster.
SET STATISTICS IO ON;
-- Run the three SELECT statements above in the same session.
SET STATISTICS IO OFF;On five rows, cost differences are noise. On a real table, compare logical reads and duration using representative sizes and indexes. A NOT IN plan can include extra work to account for possible NULLs. Adding IS NOT NULL or using NOT EXISTS can change that plan, but do not promise a fixed speed improvement. Measure both correctness and performance.
Check the Production Column and Data
Before changing a report, count NULLs in the exclusion source and inspect why they exist. Use the same predicate and joins as the real subquery; a join can introduce NULL even when a base column is constrained. Check whether a parameter or expression changes the type or collation of the compared values. A test with only clean rows will not reproduce the failure.
I keep a regression case containing one NULL and several normal keys. It is a tiny test with a large payoff: the expected four rows make the intended semantics unambiguous. After the fix, compare historical report counts and verify that only customer 2 is excluded in the sample. A query that returns rows again can still be wrong if the business meaning of the blank entry was different.
Compare NOT IN With a NULL to an Empty Subquery
There is another boundary case worth testing: an empty exclusion set. NOT IN with an empty subquery returns all five customers, and NOT EXISTS does too. That is different from a subquery containing a single NULL, which gives the empty result in our first query. The distinction helps when debugging an incident that appeared after a loading job inserted its first incomplete row.
If the exclusion subquery returns duplicate IDs, duplicates do not change the logical answer for any of the three versions. They can change plan cost and cardinality estimates. Add an index on the exclusion key when the real workload warrants it, and compare plans at scale. Do not add DISTINCT reflexively; the optimizer can use an anti semi join without materializing a distinct list.
Avoid a Misleading Patch
A tempting fix is COALESCE(e.CustomerID, -1) in the subquery. That invents a sentinel value and can collide with real data or change type behavior. An explicit IS NOT NULL predicate states the rule more clearly. If an outer customer ID is nullable, test that case separately and define whether it should be included. Correctness comes from a written rule for unknown values, not from a replacement constant chosen in a hurry.
Related reading on this blog: SQL Puzzle: Solution to Strange Results: IN and IS NOT NULL and Are Not Equal to Operators Equal to Not In? SQL in Sixty Seconds #102.
NOT IN is not safe with an unknown NULL in its set, it is safe only with explicit NULL rules.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
