MySQL SELECT x FROM WHERE NOT IN(SELECT x FROM b)-意外的结果 [英] MySQL SELECT x FROM a WHERE NOT IN ( SELECT x FROM b ) - Unexpected result

问题描述

我希望下面第三个查询的结果包含id = 732.没有.为什么会这样?

I expect the result of the third query below to contain id=732. It doesn't. Why is that?

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)

表email.id中有三个NULL条目，而match.id中没有NULL条目.

There are three NULL entries in table email.id, and no NULL entries in match.id.

可以在 http://pastebin.ca/1462094

推荐答案

来自

为了符合SQL标准，IN不仅在左侧的表达式为NULL时返回NULL，而且在列表中找不到匹配项并且其中的一个表达式也返回NULL.列表是NULL.

To comply with the SQL standard, IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.

这正是您的情况.

IN和NOT IN都返回NULL，这不是WHERE子句的可接受条件.

Both IN and NOT IN return NULL which is not an acceptable condition for WHERE clause.

重写查询，如下所示:

SELECT  *
FROM    match m
WHERE   NOT EXISTS
        (
        SELECT  1
        FROM    email e
        WHERE   e.id = m.id
        )

这篇关于MySQL SELECT x FROM WHERE NOT IN(SELECT x FROM b)-意外的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！