Microsoft SQL Server NULL with NOT IN SubQuery


Example

While handling not in sub-query with null in the sub-query we need to eliminate NULLS to get your expected results

create table #outertable (i int)
create table #innertable (i int)

insert into #outertable (i) values (1), (2),(3),(4), (5)
insert into #innertable (i) values (2), (3), (null)

select * from #outertable where i in (select i from #innertable)
--2
--3
--So far so good

select * from #outertable where i not in (select i from #innertable)
--Expectation here is to get 1,4,5 but it is not. It will get empty results because of the NULL it executes as {select * from #outertable where i not in (null)}

--To fix this 
select * from #outertable where i not in (select i from #innertable where i is not null)
--you will get expected results
--1
--4
--5

While handling not in sub-query with null be cautious with your expected output