Why does "non exists" SQL query work and "not in" doesn't

Question

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.

Answer 1

If you write out the syntactic sugar, x not in (1,2,3) becomes:

x <> 1 AND x <> 2 AND x <> 3

So if the ssn column contains a null value, the first query is the equivalent of:

WHERE ESPSSN <> NULL AND ESPSSN <> ...

The result of the comparison with NULL is unknown, so the query would not return anything.

Answer 2

As Andomar said, beware of NULL values when using NOT IN

Also note that a query using the NOT IN predicate will always perform nested full table scans, whereas a query using NOT EXISTS can use an index within the sub-query, and be much faster as a result.