T-SQL：关于NOT IN的问题

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)

2 个解决方案

#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...

啊。原来它是选项列表中的NULL,打破了整个该死的东西。您必须关闭ANSI NULL或将查询更改为此...

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

#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.

如果任何Project.ProjectStatusKey值为NULL,则NOT IN子句也将计算为NULL,而不是TRUE / FALSE。

#1