MySQL SELECT x FROM WHERE NOT IN（SELECT x FROM b） - 意外的结果

问题描述：

我期望下面第三个查询的结果包含id = 732。它没有。这是为什么？MySQL SELECT x FROM WHERE NOT IN（SELECT x FROM b） - 意外的结果

 
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条目。

全表/查询可以在 http://pastebin.ca/1462094

答

从documentation：

为了符合SQL标准，IN回报NULL不仅如果在左侧的表达式是NULL，而且如果不匹配，在列表中找到和的一个列表中的表达式是NULL。

这正是你的情况。

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

重写查询如下：

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

这是OP的情况吗？我的理解是，“如果找不到匹配并且列表中的一个表达式为NULL”不是这种情况，因为应该找到732，从而使该条款无效，对吧？ – Eric 2009-06-16 12:35:30

@Eric：732在电子邮件中找不到。 – Quassnoi 2009-06-16 12:37:52

答

可以看出我有点脱节与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

第二个查询看起来非常直观，但它执行了连接条件，然后是where条件。这种情况下，连接和where子句是不等价的。

我的理解是，使用左外连接对于性能也更好。 – 2014-05-27 23:46:45

答

...或者，如果你真的想使用NOT IN可以使用

SELECT * FROM match WHERE id NOT IN (SELECT id FROM email WHERE id IS NOT NULL)

答

下面是一些SQL实际意义：

SELECT m.id FROM match m LEFT JOIN email e ON e.id = m.id WHERE e.id IS NULL

简单总是比较好。

MySQL SELECT x FROM WHERE NOT IN（SELECT x FROM b） - 意外的结果

相关推荐