T-SQL: Question about NOT IN

Question

Why would these queries return difference results:

SELECT * 
  FROM ProjectStatus PS 
 WHERE 0 = (SELECT COUNT(*) 
              FROM Project P 
             WHERE P.ProjectStatusKey = PS.ProjectStatusKey)

SELECT * 
  FROM ProjectStatus PS 
 WHERE PS.ProjectStatusKey NOT IN (SELECT P.ProjectStatusKey 
                                     FROM Project P)

Answer 1

Ugh. Turns out it is the NULL in the list of options that breaks the whole damn thing. You have to either turn off ANSI NULLs or change the query to this...

SELECT * FROM ProjectStatus PS WHERE 
    PS.ProjectStatusKey NOT IN (SELECT P.ProjectStatusKey FROM Project P WHERE P.ProjectStatusKey IS NOT NULL)

Answer 2

If any of your Project.ProjectStatusKey values are NULL, then the NOT IN clause would evaluate to NULL as well instead of TRUE/FALSE.