NOT IN With a NULL Returns Nothing
You have a short list of categories you are phasing out, and you want every product that is not in one of them. You write the obvious NOT IN query, run it, and get zero rows back. Not the products still in a phase-out category. Every product, including the ones in categories you are keeping.
This is Part 2 of The UNKNOWN Problem, a series on NULL traps in T-SQL. Part 1 was about a change that never happened. This one is about a query that returns nothing, and it is one of the best known NULL traps.
Here is the setup, using the product and category tables from Part 1 plus a small phase-out list.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE dbo.phase_out ( category_id int NULL ); INSERT INTO dbo.phase_out (category_id) VALUES (4), (7), (NULL); INSERT INTO dbo.product (product_id, sku, product_name, category_id, list_price) VALUES (100, 'BOLT-08', 'M8 Hex Bolt', 1, 0.12) , (101, 'NUT-08', 'M8 Hex Nut', 4, 0.05) , (102, 'WSHR-08', 'M8 Washer', 7, 0.02); |
That NULL in phase_out is not exotic. Someone imported a spreadsheet with a blank cell, or a catch-all row got left behind. The column allows it, so there it sits.
Now the query. Show me every product whose category is not being phased out. Product 100 is in category 1, which is not in the list, so it should come back.
|
1 2 3 4 |
SELECT product_id, product_name, category_id FROM dbo.product WHERE category_id NOT IN (SELECT category_id FROM dbo.phase_out); /* (0 rows) */ |
Nothing comes back. Product 100 included.
The cause is three-valued logic again, this time hiding in what NOT IN expands to.[1] category_id NOT IN (4, 7, NULL) is the same as this.
|
1 |
category_id <> 4 AND category_id <> 7 AND category_id <> NULL |
For product 100, category_id is 1. So 1 <> 4 is TRUE, 1 <> 7 is TRUE, and 1 <> NULL is UNKNOWN. TRUE AND TRUE AND UNKNOWN is UNKNOWN, and WHERE keeps a row only when its predicate is TRUE. The row is dropped. That last comparison is UNKNOWN for every product, whatever its category, so every row is dropped and you get an empty result.
The positive form does not have this problem. category_id IN (4, 7, NULL) expands to category_id = 4 OR category_id = 7 OR category_id = NULL, and OR needs only one TRUE. A product in category 4 matches on the first term, and the trailing OR UNKNOWN cannot pull a TRUE back down to UNKNOWN. So IN with a NULL still works, while NOT IN with a NULL empties the result. Testing IN will not show you the problem, and it is easy to assume NOT IN is just its mirror image.
The fix is NOT EXISTS.[2]
|
1 2 3 4 5 6 7 8 9 |
SELECT p.product_id, p.product_name, p.category_id FROM dbo.product AS p WHERE NOT EXISTS ( SELECT 1 FROM dbo.phase_out AS x WHERE x.category_id = p.category_id ); /* 100 M8 Hex Bolt 1 */ |
NOT EXISTS asks a different question. Not “is this value absent from a list of values”, but “does a matching row exist”. The NULL row in phase_out has nothing to match, because x.category_id = p.category_id is never TRUE when either side is NULL, so it joins to nothing and is ignored. Product 100 has no match in phase_out, so it survives. SQL Server can implement NOT EXISTS as an anti semi join[3], though as always you should read the actual plan rather than assume it is faster.
You will also see this written as a LEFT JOIN with a WHERE clause that keeps the non-matches. That works too, as long as the column you test for NULL on the right side is one that cannot itself be NULL, usually the key. Join to a nullable column, check that for NULL, and you are back in a NULL trap of your own making.
You can rescue NOT IN by adding WHERE category_id IS NOT NULL to the subquery, and that does work. The catch is that every NOT IN needs the filter, and a missing one produces an empty result with no error. I reach for NOT EXISTS instead, because there is no NULL filter to forget.
The two forms also differ on the outer side. A product with category_id = NULL has no match in phase_out, so NOT EXISTS returns it as “not being phased out”. NOT IN never returns that product, even with the IS NOT NULL filter in place, because NULL NOT IN (...) is itself UNKNOWN. You still have to decide whether an uncategorized product counts as “not being phased out”, but NOT EXISTS gives you a definite row to decide about.
Next up is Part 3, an inequality filter that drops your NULL rows, where WHERE status <> 'closed' loses every row whose status is not set.
If NOT IN has ever handed you an empty grid and a confused half hour, tell me about it. You can find me on Bluesky and LinkedIn.
References
- IN (Transact-SQL) - Microsoft Learn. Documents the IN and NOT IN predicates and how NOT IN expands to a chain of comparisons. ↩
- NOT IN, NOT EXISTS, and the anti semi join - SQLPerformance, Aaron Bertrand. Explains why NOT IN against a nullable column can return no rows, and why correlated NOT EXISTS is the safer pattern. ↩
- EXISTS (Transact-SQL) - Microsoft Learn. Documents the EXISTS predicate used for the NULL-safe rewrite. ↩