0

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.

  • 2
    Which result is correct? Are any nulls involved? – eckes Dec 6 '17 at 7:30
  • 2
    Are there NULL values in lf_transit_cage.trayNo? – ypercubeᵀᴹ Dec 6 '17 at 7:32
  • First query should be correct. Yes, lf_transit_cage.trayNo has null value. This should be the answer. Thanks. – Pop Dec 6 '17 at 7:33
  • 2
    Another way to write the query: SELECT trayNo FROM lf_transit_new EXCEPT SELECT trayNo FROM lf_transit_cage ; – ypercubeᵀᴹ Dec 6 '17 at 7:35
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.