{

Here was an interesting issue we ran into this week. I haven't run into this before and thought I should share - a friend was using a subquery to filter and because of NULL results in the field his result was an empty set.

My approach to things like this is usually to use a join rather than a subquery and ultimately that's how we saw how Microsoft SQL Server was processing things. But this behavior is quite subtle so I'm posting since a person running into the same issue may Google there way to this entry:



1 CREATE TABLE #TRAN(


2 TRANID INT,


3 TRANNAME VARCHAR(50)


4 )


5 GO


6


7 CREATE TABLE #ACH(


8 ACHID INT IDENTITY(1,1),


9 ACHNAME VARCHAR(50),


10 TRANID INT NULL


11 )


12 GO


13


14 INSERT INTO #TRAN VALUES(1,'TRANSACTION A')


15 INSERT INTO #TRAN VALUES(2,'TRANSACTION B')


16 INSERT INTO #TRAN VALUES(3,'TRANSACTION C')


17 INSERT INTO #TRAN VALUES(4,'TRANSACTION D')


18 INSERT INTO #TRAN VALUES(5,'TRANSACTION E')


19


20


21 INSERT INTO #ACH VALUES('ACH 1A', 1)


22 INSERT INTO #ACH VALUES('ACH 2A', 2)


23 INSERT INTO #ACH VALUES('ACH 3A', 3)


24 INSERT INTO #ACH VALUES('ACH 1B', 1)


25 INSERT INTO #ACH VALUES('ACH 1C', 1)


26 INSERT INTO #ACH VALUES('ACH 3B', 3)


27 INSERT INTO #ACH VALUES('TEST',NULL)


28


29 -- RETURNS NOTHING


30 SELECT * FROM #TRAN


31 WHERE


32 TRANID NOT IN


33 (SELECT TRANID FROM #ACH)


34


35 -- RETURNS TRANID 4,5


36 SELECT * FROM #TRAN


37 WHERE


38 TRANID NOT IN


39 (SELECT TRANID FROM #ACH WHERE TRANID IS NOT NULL)





}