Why SQL doesn’t exist and SQL doesn’t

Question

I spent some time trying to understand why this query does not pull the expected results:

SELECT * FROM NGS WHERE ESPSSN NOT IN (SELECT SSN FROM CENSUS)

Finally, I tried to write the query in a different way, and this led to 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 such choices and have never had a problem that I know of.

Answer 1

If you prescribe syntactic sugar, x not in (1,2,3)it becomes:

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

So, if the column ssncontains a null value, the first query is equivalent:

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

NULL , .

Answer 2

, NULL NOT IN

, NOT IN , NOT EXISTS .