为何第一条SQL查询返回0？NOT IN含NULL的内部机制解析

两条SQL的核心差异在于子查询是否过滤NULL值，第一条返回0的原因完全是SQL三值逻辑中NULL的特殊比较规则导致的，具体执行机制拆解如下：

核心问题：NOT IN遇到NULL的判断逻辑 #

SQL里的NULL代表「未知值」，任何与NULL的比较操作（包括=、!=、IN/NOT IN）结果都不是TRUE或FALSE，而是UNKNOWN。而WHERE子句只会保留判断结果为TRUE的行，UNKNOWN会被当作FALSE处理，直接过滤掉所有符合该条件的行。

第一条查询的执行流程 #

1. 子查询select distinct customer_id from test_table2返回的结果集中包含NULL（假设test_table2存在customer_id为NULL的记录）。
2. 对test_table1中的每一行customerId，执行customerId NOT IN (子查询结果)的判断，这个判断等价于：

   customerId != 值1 AND customerId != 值2 AND ... AND customerId != NULL
   

3. 其中customerId != NULL的结果是UNKNOWN，而逻辑与（AND）操作中只要有一个UNKNOWN，整个表达式的结果就会变成UNKNOWN。
4. WHERE子句不接受UNKNOWN的结果，所以test_table1里所有行都被过滤，最终count(distinct customerId)返回0。

第二条查询的执行流程 #

1. 子查询添加了customer_id is not null条件，直接过滤掉所有NULL值，返回的结果集只有非NULL的customer_id。
2. 此时customerId NOT IN (...)的判断等价于：

   customerId != 值1 AND customerId != 值2 AND ...
   

3. 所有比较结果都是明确的TRUE或FALSE，WHERE子句会保留判断为TRUE的行，最终统计出符合预期的结果。

额外建议 #

如果要避免这类NULL陷阱，除了明确过滤子查询的NULL，还可以用NOT EXISTS替代NOT IN——NOT EXISTS的判断逻辑是基于行是否存在，不会因为NULL出现全量过滤的问题，写法示例：

select count(distinct customerId) count
from `test_table1` t1
where not exists (
    select 1 from `test_table2` t2
    where t2.customer_id = t1.customerId
)

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