SQL NOT IN not working

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

More Related Contents:

  1. Case statement for multiple columns
  2. SQL where clause odd behavior [closed]
  3. SQL Server Exec issue [closed]
  4. Count(*) vs Count(1) – SQL Server
  5. How do I escape a single quote in SQL Server?
  6. Only inserting a row if it’s not already there
  7. Unpivot with column name
  8. Selecting data from two different servers in SQL Server
  9. Formatting Numbers by padding with leading zeros in SQL Server
  10. Emulate MySQL LIMIT clause in Microsoft SQL Server 2000
  11. SQL Call Stored Procedure for each Row without using a cursor
  12. What’s the best way to capitalise the first letter of each word in a string in SQL Server
  13. SQL string comparison, greater than and less than operators
  14. Postgres Case Sensitivity
  15. Splitting the string in sql server
  16. How do I query for all dates greater than a certain date in SQL Server?
  17. Check If the string contains accented characters in SQL?
  18. Need to list all triggers in SQL Server database with table name and table’s schema
  19. Count based on condition in SQL Server
  20. Get previous and next row from rows selected with (WHERE) conditions
  21. Does Sql JOIN order affect performance?
  22. Best equivalent for IsInteger in SQL Server
  23. Syntax of for-loop in SQL Server
  24. Using tuples in SQL “IN” clause
  25. Why does ISNUMERIC(‘.’) return 1?
  26. Can SQL Server Express LocalDB be connected to remotely?
  27. Backup a single table with its data from a database in sql server 2008
  28. Try_Convert for SQL Server 2008 R2
  29. SQL Server variable scope in a stored procedure
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment