Why these two queries have different result? Why these two queries have different result?


SELECT lf_transit_new.trayNo 
FROM lf_transit_new 
WHERE lf_transit_new.trayNo NOT IN
(
    SELECT lf_transit_new.trayNo 
    FROM lf_transit_new 
    WHERE lf_transit_new.trayNo IN
        (SELECT lf_transit_cage.trayNo
         FROM lf_transit_cage)
)

SELECT lf_transit_new.trayNo 
FROM lf_transit_new 
WHERE lf_transit_new.trayNo NOT IN
(SELECT lf_transit_cage.trayNo 
FROM lf_transit_cage) 


First query show 561 rows but second query show none.
I suspect that lf_transit_cage.trayNo contains some NULL values, which causes problems for a NOT IN check but not an IN check. This is an issue that we've all hit at some point as it doesn't seem intuitive, you expect that the two checks will work more similarly.

The quick fix is to alter SELECT lf_transit_cage.trayNo FROM lf_transit_cage to SELECT lf_transit_cage.trayNo FROM lf_transit_cage WHERE lf_transit_cage.trayNo IS NOT NULL, and/or if there shouldn't be NULL values in that column fix the data and perhaps set the column to not accept NULLs.

With ANSI_NULLS set off NOT IN does work as you were expecting. See this answer for an explanation as to why in both cases.