Null Trouble with Subqueries in SQL

                       Null Trouble with Subqueries in SQL

You often see Nulls troubling with Subqueries

Let me explain through examples

--create two tables

create table table1

(empid int ,empname nvarchar(50),orderid int,MangrID int)

create table table3

(empid int,MangrID int)

--insert values in tables

insert into table1 values(1,'Test',10,2),(2,'Test1',9,3),(3,'Test2',8,4),(4,'Test3',7,5),(5,'Test4',6,7),(6,'Test5',5,8),(7,'Test6',4,9),(8,'Test7',null,null),(9,'Test8',null,null)

insert into table3 values(4,1),(5,2),(6,3),(7,4),(9,9)

write a simple subqueries 

The query will return empid which are not present in table3

select empid,empname,orderid from table1 where empid not in (select o.empid from table3 o)

output

empid empname orderid

1 Test        10

2 Test1 9

3 Test2 8

8 Test7 NULL

these are the empid from Table 1  not present in Table 3

--now insert a new record in table 3

insert into table3 values(null,null)

again run the same sub  query

select empid,empname,orderid from table1 where empid not in (select o.empid from table3 o)

output

empid empname orderid

No records why it happened so the reason behind this is that with Null in table 3 because with null you cannot tell for sure wheather the empid  appear in the set because the Null could represent a empid or anything esle

think the expression in this way Not(1=4 or 1=5 or 1=6 or 1=7 or 1=9 or 1=null)

when it compares with null the expression went to NOT unknown

This is the reason it was showing no Data to handle this :
one way is to directly in subquery you can take only Not Null values

select empid,empname,orderid from table1 where empid not in (select o.empid from table3 o where o.empid is not null) 

--or

Second You can use Not Exists Predicate

select empid,empname,orderid from table1 where not exists (select o.empid from table3 o  where o.empid =table1 .empid )

Both query wiill return same output

output

empid empname orderid

1 Test        10

2 Test1 9

3 Test2 8

8 Test7 NULL