Why Do My SQL `NOT IN` Queries Fail When NULL Values Are Present?

Troubleshooting NOT IN Queries and NULL Values

When querying a database to find records absent in a subset, using NOT IN can yield unexpected empty results. This often occurs when the subquery contains NULL values.

The Problem: NOT IN and NULLs

Consider this SQL query designed to retrieve records from [Inventory].[dbo].[Stock] that are not present in [Subset].[dbo].[Products]:

<code class="language-sql">SELECT stock.IdStock, stock.Descr
FROM   [Inventory].[dbo].[Stock] stock
WHERE  stock.IdStock NOT IN (SELECT foreignStockId
                            FROM   [Subset].[dbo].[Products])</code>

If foreignStockId contains NULLs, this query may return no rows, even if matches exist. This is due to SQL's three-valued logic; a comparison with NULL results in UNKNOWN, affecting the NOT IN evaluation.

The Solution: Handling NULLs

Two effective solutions avoid this issue:

1. Filtering NULLs from the Subquery: Modify the subquery to exclude NULLs using IS NOT NULL:

<code class="language-sql">SELECT stock.IdStock, stock.Descr
FROM   [Inventory].[dbo].[Stock] stock
WHERE  stock.IdStock NOT IN (SELECT foreignStockId
                             FROM   [Subset].[dbo].[Products]
                             WHERE  foreignStockId IS NOT NULL)</code>

2. Using NOT EXISTS: Replace NOT IN with NOT EXISTS for a potentially more efficient and clearer approach:

<code class="language-sql">SELECT stock.idstock, stock.descr
FROM   [Inventory].[dbo].[Stock] stock
WHERE  NOT EXISTS (SELECT *
                   FROM   [Subset].[dbo].[Products] p
                   WHERE  p.foreignstockid = stock.idstock)</code>

Performance:

NOT EXISTS often produces simpler execution plans, potentially leading to better performance, especially when dealing with NULLs.

Further Reading:

For more in-depth comparisons of different approaches to this problem, including LEFT JOIN and other alternatives, see these resources:

• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server
• Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?

The above is the detailed content of Why Do My SQL `NOT IN` Queries Fail When NULL Values Are Present?. For more information, please follow other related articles on the PHP Chinese website!