MySQL SELECT x FROM a WHERE NOT IN (SELECT x FROM b) - 意外结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1001144/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL SELECT x FROM a WHERE NOT IN ( SELECT x FROM b ) - Unexpected result
提问by Chris Burgess
I expect the result of the third query below to contain id=732. It doesn't. Why is that?
我希望下面第三个查询的结果包含 id=732。它没有。这是为什么？
mysql> SELECT id FROM match ORDER BY id DESC LIMIT 5 ; +------------+ | id | +------------+ | 732 | | 730 | | 655 | | 458 | | 456 | +------------+ 5 rows in set (0.00 sec) mysql> SELECT id FROM email ORDER BY id DESC LIMIT 5 ; +------------+ | id | +------------+ | 731 | | 727 | | 725 | | 724 | | 723 | +------------+ 5 rows in set (0.00 sec) mysql> SELECT * FROM match WHERE id NOT IN ( SELECT id FROM email ) ; Empty set (0.00 sec)
There are three NULL entries in table email.id, and no NULL entries in match.id.
表email.id 中有3 个NULL 条目，match.id 中没有NULL 条目。
The full table / queries can be seen at http://pastebin.ca/1462094
完整的表/查询可以在 http://pastebin.ca/1462094
回答by Quassnoi
From documentation:
从文档：
To comply with the
SQLstandard,INreturnsNULLnot only if the expression on the left hand side isNULL, but also if no match is found in the list and one of the expressions in the list isNULL.
为了符合
SQL标准，不仅在左侧的表达式为 时IN返回，而且在列表中未找到匹配项且列表中的表达式之一为 时返回。NULLNULLNULL
This is exactly your case.
这正是你的情况。
Both INand NOT INreturn NULLwhich is not an acceptable condition for WHEREclause.
二者IN并NOT IN返回NULL其不是一个可接受的条件WHERE子句。
Rewrite your query as follows:
将您的查询改写如下：
SELECT *
FROM match m
WHERE NOT EXISTS
(
SELECT 1
FROM email e
WHERE e.id = m.id
)
回答by auris
... or if you really want to use NOT INyou can use
...或者如果你真的想使用NOT IN你可以使用
SELECT * FROM match WHERE id NOT IN ( SELECT id FROM email WHERE id IS NOT NULL)
回答by Eric
I'm a little out of touch with the details of how MySQL deals with nulls, but here's two things to try:
我有点不了解 MySQL 如何处理空值的细节，但这里有两件事要尝试：
SELECT * FROM match WHERE id NOT IN
( SELECT id FROM email WHERE id IS NOT NULL) ;
SELECT
m.*
FROM
match m
LEFT OUTER JOIN email e ON
m.id = e.id
AND e.id IS NOT NULL
WHERE
e.id IS NULL
The second query looks counter intuitive, but it does the join condition and then the where condition. This is the case where joins and where clauses are not equivalent.
第二个查询看起来违反直觉，但它先执行连接条件，然后执行 where 条件。这就是连接和 where 子句不等价的情况。
回答by DrupalFever
Here is some SQL that actually make sense:
下面是一些真正有意义的 SQL：
SELECT m.id FROM match m LEFT JOIN email e ON e.id = m.id WHERE e.id IS NULL
Simple is always better.
简单总是更好。
