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 ?

Answer 1

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