首页 > mysql > NOT IN子查询如何使用NULL值?

NOT IN子查询如何使用NULL值? (How does NOT IN subquery work with NULL values?)

2012-06-29 mysql

问题

我很困惑以下如何在MySQL中工作。在下面的查询中,第一个SELECT返回所有行,table2而第二个SELECT返回任何行。是否有NULLNOT IN运营商合作的解释。有没有文件可以解释这个?

CREATE TABLE table1 (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (id)
);

CREATE TABLE table2 (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   table1_id INT UNSIGNED,
   PRIMARY KEY (id)
);

INSERT INTO table2 (id, table1_id) VALUES (1, NULL);

SELECT COUNT(*) FROM table2 WHERE table1_id NOT IN (SELECT id FROM table1);
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+

INSERT INTO table1 (id) VALUES (1);

SELECT COUNT(*) FROM table2 WHERE table1_id NOT IN (SELECT id FROM table1);
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

解决方法

原因是根据SQL规范,Foo IN(A,B,C)转换为( Foo = A Or Foo = B Or Foo = C )。因此,如果Foo In(Null, 1, 2)我们得到了Foo = Null Or Foo = 1 Or Foo = 2。由于Foo = Null总是UNKNOWN并且False为了过滤而进行评估,因此IN表达式中的Null将不返回任何结果。

问题

I am confused how the following works in MySQL. In the queries below, the first SELECT returns all rows from table2 while the second SELECT returns none of the rows. Is there an explanation of how NULL works with the NOT IN operator. Is there any documentation to explains this?

CREATE TABLE table1 (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (id)
);

CREATE TABLE table2 (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   table1_id INT UNSIGNED,
   PRIMARY KEY (id)
);

INSERT INTO table2 (id, table1_id) VALUES (1, NULL);

SELECT COUNT(*) FROM table2 WHERE table1_id NOT IN (SELECT id FROM table1);
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+

INSERT INTO table1 (id) VALUES (1);

SELECT COUNT(*) FROM table2 WHERE table1_id NOT IN (SELECT id FROM table1);
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

解决方法

The reason is that according to the SQL specification, Foo IN(A,B,C) translates to ( Foo = A Or Foo = B Or Foo = C ). Thus, if we have Foo In(Null, 1, 2) we get Foo = Null Or Foo = 1 Or Foo = 2. Since Foo = Null is always UNKNOWN and evaluated to False for purposes of filtering, Nulls in your IN expression will return no results.

相似信息