SQL NOT IN not working

Question

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

The following SQL statement does not work:

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

Doesn't work. Removing NOT gives the correct results, that is, products that are in both databases. However, using NOT IN does not return any results.

What am I doing wrong, any ideas?

Answer 1

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

Probably returns a NULL .

A NOT IN query will not return rows if any NULL exists in the NOT IN list of values. You can explicitly exclude them using IS NOT NULL , as shown 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 .

 SELECT stock.idstock, stock.descr FROM [Inventory].[dbo].[Stock] stock WHERE NOT EXISTS (SELECT * FROM [Subset].[dbo].[Products] p WHERE p.foreignstockid = stock.idstock) 

Besides the semantics you want, the execution plan for NOT EXISTS often simpler as shown here .

The reason for the difference in behavior comes down to the three-valued logic used in SQL. Predicates can be evaluated to True , False or Unknown .

Sentence

A WHERE must be evaluated to True in order for the string to be returned, but this is not possible when using NOT IN when NULL present, as described below.

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

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

True AND True AND Unknown ranks Unknown for truth tables for three-valued logic .

The following links provide additional information on the performance of various parameters.

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

Answer 2

If NOT IN does not work, you can always try to make a LEFT JOIN . Then filter WHERE using one of the values ​​from the joined table that is NULL . Provided that the value you connected to does not contain a NULL value.