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)
}