SQL中IN与NOT IN包含NULL时的查询异常及解决方法

SQL遵循三值逻辑：判断结果除了TRUE和FALSE，还有UNKNOWN（处理NULL时产生）。WHERE子句只会返回判断结果为TRUE的行，FALSE和UNKNOWN的行都会被过滤。

关于SELECT * FROM data WHERE number IN(1,2,NULL) #

这个条件等价于：

number = 1 OR number = 2 OR number = NULL

• 当number是1或2时，前两个比较会返回TRUE，整个OR表达式结果为TRUE，所以这部分行被返回。
• 当number是NULL时，number = NULL的结果是UNKNOWN，OR表达式需要至少一个TRUE才会返回TRUE，因此这部分行被过滤。
  这就是你看到“不含number为NULL的行”的原因。

关于SELECT * FROM data WHERE number NOT IN(1,2,NULL) #

这个条件等价于：

NOT (number = 1 OR number = 2 OR number = NULL)

根据逻辑运算规则展开后是：

number != 1 AND number != 2 AND number != NULL

核心问题在于：任何值和NULL做不等于比较，结果都是UNKNOWN。而AND表达式中只要有一个UNKNOWN，整个表达式的结果就是UNKNOWN。WHERE子句不会返回UNKNOWN的行，所以不管number是什么值，这个条件都无法得到TRUE，自然没有结果。

根据实际需求，有三种可靠的实现方案：

方案1：排除1、2，同时保留number为NULL的行 #

直接拆分条件，明确处理NULL：

SELECT * FROM data WHERE number NOT IN(1,2) OR number IS NULL

方案2：用NOT EXISTS替代NOT IN（子查询场景首选） #

当实际场景是子查询可能返回NULL时，NOT EXISTS不受三值逻辑影响，是更稳妥的选择。比如子查询为SELECT num FROM sub_table，可以写成：

SELECT * FROM data d 
WHERE NOT EXISTS (
    SELECT 1 FROM sub_table s 
    WHERE s.num = d.number
)

NOT EXISTS的逻辑是：只要子查询中没有匹配到相等的非NULL值，就返回TRUE，完美避开NULL带来的UNKNOWN问题。

方案3：仅排除1、2，不保留NULL行 #

如果不需要NULL行，直接过滤掉IN列表中的NULL即可：

SELECT * FROM data WHERE number NOT IN(1,2)

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