NOT IN Clause and NULL Values

When ANSI_NULLS setting is ON, be careful when using NOT IN clause if any of the values listed for the clause has NULL value. The scripts below will explain:

create table Table1 (x integer)
insert into Table1 Values (1)
insert into Table1 Values (2)
insert into Table1 Values (3)
insert into Table1 Values (4)

create table Table2 (x integer)
insert into Table2 Values (1)
insert into Table2 Values (Null)
insert into Table2 Values (2) 
insert into Table2 Values (5)

create table Table3 (x integer)
insert into Table3 Values (Null)
insert into Table3 Values (Null)
insert into Table3 Values (Null)

-- These scripts will not return any result (it would if ANSI_NULLS is OFF):
select * from Table1
where x NOT IN (select x from Table2)

select * from Table1 where x NOT IN (select x from Table3)


-- However, these ones are fine:
select * from Table1
where x IN (select x from Table2)

select * from Table1
where x IN (select x from Table3)


-- This one is also fine:
select * from Table2
where x NOT IN (select x from Table1)
-- (returns '5', however if ANSI_NULLS is OFF this would return 'NULL' and '5')