NOT IN 子查询如何处理 NULL 值? [英] How does NOT IN subquery work with NULL values?

查看:57
本文介绍了NOT IN 子查询如何处理 NULL 值?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我很困惑以下在 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆