Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL NOT IN not working

Tags:

sql

sql-server

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?

like image 502
Sam Avatar asked Mar 08 '11 11:03

Sam


People also ask

Why not in is not working in SQL Server?

A NOT IN query will not return any rows if any NULL s exists in the list of NOT IN values. You can explicitly exclude them using IS NOT NULL as below. Or rewrite using NOT EXISTS instead. As well as having the semantics that you want the execution plan for NOT EXISTS is often simpler as looked at here.

Is not in in SQL Server?

The SQL Server NOT IN operator is used to replace a group of arguments using the <> (or !=) operator that are combined with an AND. It can make code easier to read and understand for SELECT, UPDATE or DELETE SQL commands.

What can I use instead of not in SQL?

An alternative for IN and EXISTS is an INNER JOIN, while a LEFT OUTER JOIN with a WHERE clause checking for NULL values can be used as an alternative for NOT IN and NOT EXISTS.

What is not in clause in SQL?

NOT IN clause in SQL Server is nothing but a series of NOT EQUAL TO. One of the values from the subquery is a NULL. The result set (custname from tbl_customers) contains A, B & NULL. Every value from the outer query is compared with every value from the inner query.


2 Answers

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.

  • 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
like image 124
Martin Smith Avatar answered Sep 22 '22 22:09

Martin Smith


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.

like image 23
MPękalski Avatar answered Sep 23 '22 22:09

MPękalski