Not In vs Exists in TSQL

When using “Not In” to check for data between source and target tables, beware of Nulls in the target table. The correct output depends on the ANSI_NULLs property of the session and\or the way query is written(using exists;Not NULL and Not IN;ANSI_NULLS OFF and Not In).

Try the below example :

declare @TestA table (sno int)
declare @TestB table (sno int)

INSERT INTO @TestA(sno)
Values (1),(3)
INSERT INTO @TestB(sno)
Values (1),(2),(Null)

— Case#1 : Not In with Ansi_Nulls turned ON
Select * from @TestA where sno not in
(Select sno from @TestB)

— Case#2 : Not Exists with Ansi_Nulls turned ON
Select * from @TestA A where not exists
(Select 1 from @TestB B Where A.sno=B.Sno)

— Case#3 : Not In with Ansi_Nulls turned OFF
Set ANSI_NULLS OFF
Select * from @TestA where sno not in
(Select sno from @TestB)
Set ANSI_NULLS ON

— Case#4 : Not In with Ansi_Nulls turned ON
Select * from @TestA where sno not in
(Select sno from @TestB where sno is not null)

Advertisement