Why Does My SQL `NOT IN` Query Return Unexpected Results?

SQL NOT IN Operators: Expected vs. Actual

The NOT IN operator in SQL often presents challenges. This article explores a scenario where the NOT IN expected functionality appears to fail, analyzes the problem and provides alternatives.

Suppose there are two databases, one containing the main inventory and the other containing a subset of these records. A query using NOT IN attempts to find the differences between these two databases:

<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>

However, this query failed to provide the expected results. It excludes all records, even if they exist in the subset database. The lack of data raises the question: “What went wrong?”

The answer lies in the possibility of NULL values ​​in the subset database. When a NULL value is encountered, the NOT IN query will evaluate to NULL, causing those records to be excluded. This behavior stems from the three-valued logic used by SQL, where predicates can return True, False, or Unknown.

To correct this problem, two alternatives can be considered:

Use 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>

Use NOT EXISTS instead of NOT IN:

<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>

Both methods handle NULL values ​​efficiently, ensuring only valid comparisons are made. The latter approach using NOT EXISTS usually provides better performance by simplifying the execution plan.

Remember, when dealing with NULL values ​​in SQL, always consider three-valued logic and use appropriate techniques to avoid unexpected results.

The above is the detailed content of Why Does My SQL `NOT IN` Query Return Unexpected Results?. For more information, please follow other related articles on the PHP Chinese website!