IN clause, NULL handling in TSQL/SQL Server?

Question

Suppose there is a table like this:

f1      f2
----------
1       3
4       8
6       4
NULL    1

The following query works as expected :

SELECT f2
  FROM Table_1 a
 WHERE NOT EXISTS (SELECT *
                     FROM Table_1 
                    WHERE a.f2 = f1)

...and result set is:

f2
---
3
8

...but similar query with IN returns nothing:

SELECT f2
  FROM Table_1 a
 WHERE f2 NOT IN (SELECT b.f1 
                    FROM Table_1 b)

What's the problem ?

The inner-most SELECT goes against `Table_1` in your first query, and against `tbltemp b` in your second query - just a typo?? — marc_s, Aug 28 '11 at 09:29

score 10 · Accepted Answer · edited May 23 '17 at 09:59

10

It is because of the null value in f1. Try this instead.

SELECT      f2
FROM         Table_1 a
WHERE f2 NOT IN (select b.f1 
                 from Table_1 b
                 where b.f1 is not null)

Here is a great explanation as to why it is so. NOT IN clause and NULL values

edited May 23 '17 at 09:59

Community

answered Aug 28 '11 at 08:12

Mikael Eriksson

Similar question from a few years ago. Common problem with tri-state logic. http://stackoverflow.com/questions/129077/sql-not-in-constraint-and-null-values – RonnieDickson Aug 28 '11 at 08:20
1

[A more detailed explanation by Joe Celko here](http://www.simple-talk.com/sql/learn-sql-server/sql-and-the-snare-of-three-valued-logic/) – Martin Smith Aug 28 '11 at 10:51
by the way as my fault , "tbltemp" was typo. It should be replaced with "Table_1" – Mostafa Armandi Aug 30 '11 at 10:13

1 Answers1