Why does "non exists" SQL query work and "not in" doesn't
Posted
by Josh
on Stack Overflow
See other posts from Stack Overflow
or by Josh
Published on 2010-04-02T17:34:05Z
Indexed on
2010/04/02
17:53 UTC
Read the original article
Hit count: 209
I spent some time trying to figure out why this query isn't pulling the results i expected:
SELECT * FROM NGS WHERE ESPSSN NOT IN (SELECT SSN FROM CENSUS)
finally i tried writing the query another way and this ended up getting the expected results:
SELECT * FROM NGS n WHERE NOT EXISTS (SELECT * FROM CENSUS WHERE SSN = n.ESPSSN)
The first query seems more appropriate and "correct". I use "in" and "not in" all the time for similar selects and have never had a problem that i know of.
© Stack Overflow or respective owner