Sam Sam - 1 year ago 69
SQL Question

SQL NOT IN not working

I have two databases, one which holds the inventory, and another which contains a subset of the records of the primary database.

The following SQL statement is not working:

SELECT stock.IdStock
FROM [Inventory].[dbo].[Stock] stock
WHERE stock.IdStock NOT IN
(SELECT foreignStockId FROM

The not in does not work. Removing the NOT gives the correct results, i.e. products that are in both databases. However, using the NOT IN is not returning ANY results at all.

What am I doing wrong, any ideas?

Answer Source
SELECT foreignStockId
FROM   [Subset].[dbo].[Products]  

Probably returns a NULL.

A NOT IN query will not return any rows if any NULLs exists in the list of NOT IN values. You can explicitly exclude them using IS NOT NULL as below.

SELECT stock.IdStock,
FROM   [Inventory].[dbo].[Stock] stock
WHERE  stock.IdStock NOT IN (SELECT foreignStockId
                             FROM   [Subset].[dbo].[Products]
                             WHERE  foreignStockId IS NOT NULL) 

Or rewrite using NOT EXISTS instead.

SELECT stock.idstock,
FROM   [Inventory].[dbo].[Stock] stock
                   FROM   [Subset].[dbo].[Products] p
                   WHERE  p.foreignstockid = stock.idstock) 

As well as having the semantics that you want the execution plan for NOT EXISTS is often simpler as looked at here.

The reason for the difference in behaviour is down to the three valued logic used in SQL. Predicates can evaluate to True, False, or Unknown.

A WHERE clause must evaluate to True in order for the row to be returned but this is not possible with NOT IN when NULL is present as explained below.

'A' NOT IN ('X','Y',NULL) is equivalent to 'A' <> 'X' AND 'A' <> 'Y' AND 'A' <> NULL)

  • 'A' <> 'X' = True
  • 'A' <> 'Y' = True
  • 'A' <> NULL = Unknown

True AND True AND Unknown evaluates to Unknown per the truth tables for three valued logic.

The following links have some additional discussion about performance of the various options.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download