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.
第一个查询似乎更合适并且“正确”。我一直使用“in”和“not in”进行类似的选择，并且从未遇到过我所知道的问题。
2 个解决方案
#1
11
If you write out the syntactic sugar, x not in (1,2,3) becomes:
如果你写出语法糖，x不在（1,2,3）中变成：
x <> 1 AND x <> 2 AND x <> 3
So if the ssn column contains a null value, the first query is the equivalent of:
因此，如果ssn列包含空值，则第一个查询等效于：
WHERE ESPSSN <> NULL AND ESPSSN <> ...
The result of the comparison with NULL is unknown, so the query would not return anything.
与NULL比较的结果是未知的，因此查询不会返回任何内容。
#2
2
As Andomar said, beware of NULL values when using NOT IN
正如Andomar所说，使用NOT IN时要小心NULL值
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.
另请注意，使用NOT IN谓词的查询将始终执行嵌套的全表扫描，而使用NOT EXISTS的查询可以使用子查询中的索引，因此结果要快得多。
#1
11
If you write out the syntactic sugar, x not in (1,2,3) becomes:
如果你写出语法糖，x不在（1,2,3）中变成：
x <> 1 AND x <> 2 AND x <> 3
So if the ssn column contains a null value, the first query is the equivalent of:
因此，如果ssn列包含空值，则第一个查询等效于：
WHERE ESPSSN <> NULL AND ESPSSN <> ...
The result of the comparison with NULL is unknown, so the query would not return anything.
与NULL比较的结果是未知的，因此查询不会返回任何内容。
#2
2
As Andomar said, beware of NULL values when using NOT IN
正如Andomar所说，使用NOT IN时要小心NULL值
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.
另请注意，使用NOT IN谓词的查询将始终执行嵌套的全表扫描，而使用NOT EXISTS的查询可以使用子查询中的索引，因此结果要快得多。