NOT IN 子查询如何处理 NULL 值? [英] How does NOT IN subquery work with NULL values?
问题描述
我很困惑以下在 MySQL 中是如何工作的.在下面的查询中,第一个 SELECT
返回 table2
中的所有行,而第二个 SELECT
不返回任何行.是否有关于 NULL
如何与 NOT IN
运算符一起使用的说明.是否有任何文档可以解释这一点?
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 |
+----------+
推荐答案
原因是根据 SQL 规范,Foo IN(A,B,C)
翻译成 ( Foo= A 或 Foo = B 或 Foo = C )
.因此,如果我们有 Foo In(Null, 1, 2)
我们得到 Foo = Null Or Foo = 1 Or Foo = 2
.由于 Foo = Null
始终是 UNKNOWN
并且为了过滤而计算为 False
,因此 IN 表达式中的 Null 不会返回任何结果.
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.
这篇关于NOT IN 子查询如何处理 NULL 值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!