MySQL使用NOT IN查询时意外忽略NULL值的问题咨询

SQL采用三值逻辑体系，布尔运算结果共有三种：TRUE、FALSE、UNKNOWN，WHERE子句仅会保留条件判断结果为TRUE的行，FALSE和UNKNOWN结果对应的行都会被排除。

你写的NOT COLUMN1 IN ('A','B')运算逻辑可以拆解为：

• COLUMN1 IN ('A','B')等价于 COLUMN1 = 'A' OR COLUMN1 = 'B'
• 当COLUMN1为NULL时，NULL = 任意值的运算结果都是UNKNOWN，两个通过OR连接的UNKNOWN结果运算后还是UNKNOWN
• 对UNKNOWN取NOT，结果仍然是UNKNOWN，不满足WHERE的TRUE要求，所以所有COLUMN1为NULL的行都会被过滤。

如果需要保留COLUMN1为NULL的行，可以选择以下两种写法：

1. 显式补充NULL判断，写法最直观：

SELECT * FROM TABLE WHERE COLUMN1 NOT IN ('A','B') OR COLUMN1 IS NULL

2. 改用NOT EXISTS写法，规避三值逻辑陷阱，尤其推荐IN后面接子查询的场景使用：

SELECT * FROM TABLE t1
WHERE NOT EXISTS (
    SELECT 1 FROM (SELECT 'A' AS val UNION ALL SELECT 'B') t2 
    WHERE t2.val = t1.COLUMN1
)

日常写SQL涉及NULL判断时，不要使用= NULL或者!= NULL这类写法，统一使用IS NULL/IS NOT NULL，可以避免触发三值逻辑的隐藏问题。

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