SQL中NOT IN在子查询与列中表现不同的原因探究

我们知道，当对包含NULL值的子查询使用NOT IN条件时，查询不会返回任何结果，示例如下：

CREATE TABLE movie (
  title TEXT
);

INSERT INTO movie (title) VALUES (NULL);
INSERT INTO movie (title) VALUES ('ABC');

SELECT * FROM movie
WHERE 'Titanic' NOT IN (select title from movie)

上述查询无结果返回。

但如果执行以下查询：

SELECT * FROM movie
WHERE 'Titanic' NOT IN (title)

则会返回title为ABC的行。为何这两个查询结果不同？难道NOT IN条件在两种场景下不都会被转换为WHERE 'Titanic' != 'ABC' AND 'Titanic' != NULL吗？

核心差异解析 #

这两种写法的本质逻辑完全不同，并非简单的等价转换：

1. 子查询版的NOT IN逻辑
   当NOT IN后跟子查询时，数据库会先执行子查询得到所有结果（此处为NULL和'ABC'），再将'Titanic'与子查询的每一个结果做比较。由于SQL中任何值与NULL比较的结果都是UNKNOWN（既非真也非假），而NOT IN要求所有比较结果都为真才会返回行。只要子查询结果里存在NULL，整个条件就会变成'Titanic' != 'ABC' AND 'Titanic' != NULL，其中第二个比较结果为UNKNOWN，最终整体条件结果为UNKNOWN，无法匹配任何行。

2. 直接字段引用的NOT IN逻辑
   当NOT IN后跟单个字段（title）时，这个写法等价于'Titanic' != title。此时数据库逐行处理表中记录：

• 对于title = 'ABC'的行：'Titanic' != 'ABC'结果为真，该行被返回；
• 对于title = NULL的行：'Titanic' != NULL结果为UNKNOWN，不满足条件，该行被过滤。

简单来说，子查询版是将目标值与子查询所有结果做逻辑与比较，只要存在一个NULL就会导致整体条件不成立；而直接字段引用版是逐行做单个值的比较，仅过滤掉比较结果不为真的行。

内容的提问来源于stack exchange，提问作者hitchhiker