IN, NULL processing 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 a set of results:

 f2 --- 3 8 

... but a similar query with IN returns nothing:

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

What is the problem?

Answer 1

This 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 why this is so. NOT IN and NULL values