CREATE TABLE #A(ID INT,NAME VARCHAR(10))
INSERT INTO #A VALUES(1,'ABC')
INSERT INTO #A VALUES(2,'XYZ')
CREATE TABLE #B(ID INT,NAME VARCHAR(10))
INSERT INTO #B VALUES(NULL,'ABC')
INSERT INTO #B VALUES(2,'XYZ')
Select * from #A where ID NOT in (select ID from #B)
Jignesh TrivediPosted Jul 20, 2012, 6:25 AM
coreect my understanding.
you got null out put of above query.
I think reason is #B table contain null value.
othere reason is #B table contain First null record.
try ..
Select * from #A where ID NOT in (select isnull(ID,0) from #B)
hope this will help you.
Santhosh Kumar JayaramanPosted Aug 5, 2012, 9:19 AM
LOKESH CHENNAMSETTIPosted Aug 5, 2012, 7:55 AM
The out put for above query returns NULL. Is correct or not?
Santhosh Kumar JayaramanPosted Jul 20, 2012, 8:17 AM
or
Select * from #A where Id not in (select Id from #B where ID is not null )
Rahul BhattPosted Jul 20, 2012, 8:06 AM
Use LEFT OUTER JOIN
Select #A.ID,#A.NAME from #A LEFT OUTER join #B on #A.ID = #B.ID where #B.ID is null
Santhosh Kumar JayaramanPosted Jul 20, 2012, 5:22 AM