MySQL EXISTS 和 IN 遇到 NULL 条件时有什么区别

先记住一个判断：EXISTS 问的是“子查询有没有返回行”，IN 问的是“外层值能不能在子查询返回的值集合中找到匹配”。集合里出现 NULL 时，IN 可能得到 UNKNOWN，而 EXISTS 不会因为返回列是 NULL 就失效。

EXISTS 看行，IN 看值：NULL 让判断分叉

假设有订单表和黑名单表：

-- orders.order_no 是外层订单号；blocked_orders.order_no 允许为 NULL
SELECT o.order_no
FROM orders AS o
WHERE EXISTS (
    SELECT 1
    FROM blocked_orders AS b
    WHERE b.order_no = o.order_no
);

-- IN 需要把子查询返回的 order_no 当作值集合进行比较
SELECT o.order_no
FROM orders AS o
WHERE o.order_no IN (
    SELECT b.order_no
    FROM blocked_orders AS b
);

两条语句在“有相同非空订单号”时通常都能找到该订单。但如果黑名单只有一行 NULL，EXISTS 仍然只会在相关条件实际匹配时返回真；IN 则会把外层值与 NULL 的比较视为未知。MySQL 文档也明确说明，EXISTS 的选择列表会被忽略，子查询只要返回任意行，谓词就为真。

三值逻辑决定了 IN 的“没匹配”不总是 FALSE

SQL 条件不只有真和假，还有 UNKNOWN。对非空值来说，下面三种情况最容易混淆：

NULL 不是“一个特殊的字符串”或“一个永远不相等的值”，它表示未知或缺失。因此 1003 = NULL 不是 FALSE，而是 UNKNOWN。判断空值要写 IS NULL，不能写 = NULL。

-- 用 IS NULL 明确判断缺失值；不要用 = NULL
SELECT b.order_no
FROM blocked_orders AS b
WHERE b.order_no IS NULL;

NOT IN 为什么会把结果筛空

真正危险的往往是 NOT IN。它等价于对 IN 取反，但 NOT UNKNOWN 仍然是 UNKNOWN。例如：

-- 黑名单中只要混入 NULL，1003 NOT IN (...) 可能不是 TRUE
SELECT o.order_no
FROM orders AS o
WHERE o.order_no NOT IN (
    SELECT b.order_no
    FROM blocked_orders AS b
);

-- 如果业务定义是“没有匹配的非空黑名单记录”，先排除 NULL
SELECT o.order_no
FROM orders AS o
WHERE o.order_no NOT IN (
    SELECT b.order_no
    FROM blocked_orders AS b
    WHERE b.order_no IS NOT NULL
);

另一种更贴近业务语义的写法是相关 NOT EXISTS。它判断的是“有没有一行满足关联条件”，不会把无关的 NULL 行变成整条外层记录的未知状态：

-- 只排除确实匹配当前订单号的黑名单行
SELECT o.order_no
FROM orders AS o
WHERE NOT EXISTS (
    SELECT 1
    FROM blocked_orders AS b
    WHERE b.order_no = o.order_no
);

按业务语义选择写法

性能上不要只凭“EXISTS 一定快”或“IN 一定会物化”下结论。MySQL 优化器可能对 IN 和 EXISTS 采用半连接、物化或 EXISTS 策略，先用 EXPLAIN 看当前数据分布下的计划；语义正确比套用固定口诀更重要。

相关问题

子查询返回空集合时，IN 是什么结果？

对非空外层值，IN 返回 FALSE，NOT IN 返回 TRUE。这是空集合与含 NULL 集合的区别。

EXISTS 里写 SELECT 1 还是 SELECT *？

在 EXISTS 语义上没有区别，MySQL 不使用选择列表判断是否存在行。写 SELECT 1 通常更直观。

可以用 COALESCE 把 NULL 替成特殊值吗？

只有在业务上确认该特殊值不可能与真实数据冲突时才可以。否则优先用 IS NULL、IS NOT NULL 或 NOT EXISTS 显式表达语义。