SQL NOT IN not working

Question

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
        ,stock.Descr       
FROM    [Inventory].[dbo].[Stock] stock
WHERE   stock.IdStock NOT IN
        (SELECT foreignStockId FROM
         [Subset].[dbo].[Products])

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?

Are you sure you have stock.IdStock which are not in foreignStockId at all? — CloudyMarble, Mar 08 '11 at 11:20
Do you really have any items in `Inventory` that is not in `Subset`? — Albin Sunnanbo, Mar 08 '11 at 11:20
Explain what the query should return. Currently it returns all the stock items where there is no product in the subset referencing it. — Stefan Steinegger, Mar 08 '11 at 11:22

score 258 · Accepted Answer · edited May 23 '17 at 12:02

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,
       stock.Descr
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,
       stock.descr
FROM   [Inventory].[dbo].[Stock] stock
WHERE  NOT EXISTS (SELECT *
                   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.

This fixed it for me. Select * From x Where x.y not in( Select Distinct ISNULL(c,0)) — Spaceman, Aug 07 '14 at 01:01
oh my god, indeed select returned null and NOT IN was not working properly. 6 years later I salute you — qki, Jul 28 '20 at 18:03

score 4 · Answer 2 · answered Mar 08 '11 at 15:42

4

If NOT IN does not work, you may always try to do LEFT JOIN. Then filter by WHERE using one of the values from the joined table, which are NULL. Provided, the value you were joining by does not contain any NULL value.

answered Mar 08 '11 at 15:42

MPękalski

6,873
4
26
36

score 2 · Answer 3 · answered Jul 12 '20 at 16:39

Adding my 2 cents:

I've seen SQL Server returning wrong results even when switching to not exists and left join - in corrupt databases. Run DBCC CHECKTABLE on the tables involved, also look at the NOT IN query execution plan and rebuild the indexes involved, this should help.

score 0 · Answer 4 · answered Jun 03 '18 at 05:07

You can also use Case clause to tackle such issues

SELECT  stock.IdStock
        ,stock.Descr        
FROM    [Inventory].[dbo].[Stock] stock
WHERE   (Case when stock.IdStock IN
        (SELECT foreignStockId FROM
        [Subset].[dbo].[Products]) then 1 else 0 end) = 0

this syntax works in SQL Server, Oracle and postgres

SQL NOT IN not working

4 Answers4

Linked

Related