我实际上尝试了一些类似于子查询的东西，但不明白出了什么问题。我已经设法将问题简化为以下内容。
我有一个简单的表，其中一列可能包含NULL：
DROP TABLE IF EXISTS data;
CREATE TEMP TABLE data (
id INT PRIMARY KEY,
number INT,
string TEXT
);
INSERT INTO data(id,number,string)
VALUES (1,1,'Apple'),(2,1,'Accordion'),(3,2,'Banana'),(4,2,'Banjo'),(5,NULL,'Whatever');
SELECT * FROM data WHERE number IN(1,2,NULL);
SELECT * FROM data WHERE number NOT IN(1,2,NULL);在https://dbfiddle.uk/KhTzbX_E有一个实时版本。
查找与number列匹配的行时：
SELECT * FROM data WHERE number IN(1,2,NULL);我得到了一些预期的结果，其中不包括number等于NULL的行，但我假设IN表达式是WHERE a = b的缩写。
如果查找不匹配项：
SELECT * FROM data WHERE number NOT IN(1,2,NULL);我什么都得不到。
我看不出这怎么可能是对的，表达式IN(1,2,NULL)必须返回一个有效的列表，否则第一个列表就不能工作。
这是怎么回事，有正确的方法吗？
注：我知道放入NULL是愚蠢的，但这个想法是，列表应该是一个子查询，可能会返回一个新的NULL。我也知道，我可以过滤掉子查询中的NULL。但这似乎是一个变通办法。
我已经在PostgreSQL、MariaDB和Microsoft SQL Server中尝试过了。
4条答案
按热度按时间lyfkaqu11#
这是一个针对没有NULL经验的人的老陷阱：如果
NOT IN列表包含NULL，则结果集始终为空。为了理解原因，让我们重写
到语义上等价的
然后注意
number <> NULL将总是返回NULL，如果你认为“NULL”是“unknown”，这是最容易理解的：当询问未知数是否与任何给定数不同时，根据未知数的未知值，答案可能是“真”或“假”。因此答案一定是“未知”，即boolean值为空。现在，
*something* AND NULL可以是FALSE（如果*something*是FALSE）或NULL（如果*something*是NULL或TRUE），但决不能是TRUE。而且WHERE条件只传递条件为TRUE的行。FALSE和NULL都不会传递。i86rm4rw2#
基本上，如果涉及到任何
null值，NOT IN的用途非常有限。这并不完全是一个新的见解。请参见：翻译为：
nr = null始终为null，并且由于false OR null的结果为null，因此表达式永远不会变为true。在WHERE子句中使用时，此类筛选器永远不会返回任何行。3gtaxfhh3#
任何涉及NULL的相等性检查都将返回null，并且null不等于null。Null需要使用
is/is not的特殊语言我的建议是在where子句中添加另一个部分，使其工作，同时保持可读性：
尝试使用null，您将看到它的行为：
zf9nrax14#
IN()是一个快捷方式。number IN(1,2,NULL)等于：（编号= 1或编号= 2或编号= NULL）
不能对NULL使用equality，必须使用
IS NULL，因此IN快捷方式不能像您预期的那样对NULL起作用。正如您所建议的，如果使用子查询，建议您去掉所有NULL。