SQL 陷阱：NOT IN、NULL 和三值逻辑

一句话结论（30s）

反向过滤永远用 NOT EXISTS，别用 NOT IN——因为 SQL 是三值逻辑（TRUE/FALSE/UNKNOWN），子查询里一旦出现 NULL，NOT IN 展开后的 != NULL 恒为 UNKNOWN，整个 WHERE 条件不成立，结果静默变成空集。

核心原理（2min）

• 三值逻辑：SQL 里任何和 NULL 的比较都不是 TRUE/FALSE，而是 UNKNOWN（“未知”）。
• NOT IN 的展开：5 NOT IN (1,2,NULL) 等价于 5!=1 AND 5!=2 AND 5!=NULL = TRUE AND TRUE AND UNKNOWN = UNKNOWN，不满足 WHERE → 返回空。
• NOT EXISTS 为什么安全：它是”半关系判断”，只问”是否存在匹配行”；NULL = c.id 永远是 UNKNOWN（不是 TRUE），产生不了匹配，NULL 行自然被排除，不影响结果。
• 记忆点：NOT IN 问的是”值在不在列表里”（NULL 一票否决）；NOT EXISTS 问的是”有没有匹配行”（NULL 不参与）。

底层深入（5-10min）

问题

-- 找"没被任何人选修的课程"
SELECT * FROM courses
WHERE id NOT IN (SELECT course_id FROM enrollments);

看起来正确。但如果 enrollments.course_id 中有 NULL，结果永远是空集——哪怕有没被选过的课。

根本原因：三值逻辑

SQL 的逻辑不是二值（TRUE/FALSE），而是三值：TRUE、FALSE、UNKNOWN（因为 NULL 表示”未知”）。

💭 思考：为什么 x != NULL 不是 x = NULL 的简单取反，两者结果却都是 UNKNOWN？——直觉里「不等于」是「等于」的反面，但 NULL 的意思是「不知道是什么值」。x = NULL 问「这个未知值等于 x 吗」，答不出是/否；x != NULL 问「这个未知值不等于 x 吗」，同样答不出。于是两个方向都落进 UNKNOWN，二值逻辑里的「取反」关系在这里断掉了。这正是 NOT IN 坑的根：你默认 NOT IN 是 IN 的否定，结果 NULL 一来，两个方向都「不确定」，而 WHERE 只认 TRUE，于是什么都不返回。

5 NOT IN (1, 2, NULL)
等价于: 5 != 1 AND 5 != 2 AND 5 != NULL
       = TRUE AND TRUE AND UNKNOWN
       = UNKNOWN  -- ← NOT FALSE 也不是 TRUE，不满足 WHERE 条件！

只要子查询结果中有 NULL，NOT IN 永远返回空集。 这不是 bug，是 SQL 三值逻辑的必然结果。

💡 思考穿插：为什么 NOT IN 遇到一个 NULL，整个查询就静默返回空？ 因为 NOT IN 的本质是「与列表中每个值都不相等」的一串 AND 判断，而 x != NULL 的结果既不是 TRUE 也不是 FALSE，而是 UNKNOWN——它像一张「不确定票」，AND 链里只要混进一个 UNKNOWN，整体就成了 UNKNOWN，而 WHERE 只接受 TRUE，于是该行被丢弃。注意 NULL 不是「一票否决」的 FALSE，而是「拖累全链」的 UNKNOWN，所以结果不是「报错」而是「静默为空」。

解决方案：NOT EXISTS

SELECT * FROM courses c
WHERE NOT EXISTS (
    SELECT 1 FROM enrollments e WHERE e.course_id = c.id
);

NOT EXISTS 是半关系操作——只检查是否存在匹配行，不存在时返回 TRUE。NULL 值不会产生匹配（NULL = c.id 永远是 UNKNOWN，不是 TRUE），所以 NULL 行自然被排除，不影响结果。

💭 思考：为什么同样一句「NULL 比较落进 UNKNOWN」，在 NOT IN 里「毁掉一切」、在 NOT EXISTS 里却「无害」？——关键看这个 UNKNOWN 被用在什么逻辑结构里。NOT IN 是 != 的 AND 链，要求「每个都不等」全部为 TRUE，一个 UNKNOWN 就把整条链拖成 UNKNOWN（TRUE AND UNKNOWN = UNKNOWN）；NOT EXISTS 问的是「是否存在至少一行能匹配」，每个 NULL = c.id 的 UNKNOWN 只是「这一行不算匹配」，只要没有 TRUE 的匹配行，NOT EXISTS 就成立。同一个 UNKNOWN，一个被 AND 传染、一个被「存在性」忽略——所以遇到这类题，先问「这个 UNKNOWN 是参与 AND/OR 运算，还是只作为一次匹配尝试」。

💡 思考穿插：为什么 NOT EXISTS 就不怕 NULL？ 因为它不问「值等不等」，而问「是否存在能匹配的行」。对每一行，NULL = c.id 是 UNKNOWN、不会让 EXISTS 判定「存在匹配」，所以含 NULL 的行根本没资格成为匹配项，自然不影响「不存在匹配行」的判断。一个是「值比较」被 UNKNOWN 污染，一个是「存在性判断」天然免疫 NULL——问法不同，对 NULL 的敏感度就不同。

判断集合相等

-- 找出选修了和"张三"完全一样课程的学生
SELECT student_id
FROM enrollments
GROUP BY student_id
HAVING GROUP_CONCAT(course_id ORDER BY course_id) =
       (SELECT GROUP_CONCAT(course_id ORDER BY course_id)
        FROM enrollments WHERE student_id = 1);

GROUP_CONCAT 将每个学生的课程 ID 拼接为有序字符串，再比较两个字符串是否相等——“集合是否相等”转化为”字符串是否相等”。

💡 思考穿插：为什么「集合相等」要转成字符串比较，而不是直接比？ 因为 SQL 没有直接比较两个集合是否相等的运算符，而集合无序、可能重复、还可能为空，直接比列很难；GROUP_CONCAT ... ORDER BY 把「无序集合」规范化成「唯一的有序字符串」，相等判断就退化成一行字符串比较。核心技巧是「用 ORDER BY 消除顺序差异」——否则 {语文,数学} 和 {数学,语文} 会被误判为不等。

总结

生产代码中，永远用 NOT EXISTS 替代 NOT IN。 子查询中的 NULL 是不可预测的——今天没 NULL，下个月有人插了一条 NULL，整个查询静默返回空集，排查极难。

章末提问

追问 1：NOT IN 和 NOT EXISTS 的本质区别是什么？生产为什么用 NOT EXISTS？

结论先行：NOT IN 是「值比较」，遇到子查询里的 NULL 会因三值逻辑 UNKNOWN 导致结果静默变空；NOT EXISTS 是「存在性判断」，NULL 不产生匹配、天然免疫。因为 生产数据 NULL 不可控，一条 NULL 就能让 NOT IN 的查询毫无征兆地返回空集，且不报错、极难排查，所以一律用 NOT EXISTS 更安全。

追问 2：NOT IN 里出现 NULL 为什么会导致空集，而不是「把 NULL 那行忽略掉」？

结论先行：因为 x NOT IN (...) 展开后是 x!=a AND x!=b AND x!=NULL，而 x!=NULL 是 UNKNOWN，UNKNOWN 与 TRUE 做 AND 仍是 UNKNOWN，WHERE 只保留 TRUE 的行。因为 NULL 表示「未知」而不是「不相等」，它污染的是整条 AND 链，而不是被跳过，所以没有任何一行能满足 WHERE，结果为空。

追问 3：除了 NOT EXISTS，还有什么办法防 NULL 坑？性能上两者谁更优？

结论先行：可以 WHERE col NOT IN (SELECT x FROM t WHERE x IS NOT NULL) 手动过滤 NULL，但更推荐 NOT EXISTS；性能上子查询列有索引时 NOT EXISTS 走 index lookup，NOT IN 常需全表扫描。因为 NOT IN 要逐一比对列表里的每个值，NOT EXISTS 是相关子查询、找到一条匹配即可短路，配合索引效率更高。