6.2 IN 与 EXISTS：集合判断的两种写法

6.2 IN 与 EXISTS：集合判断的两种写法

本节摘要：判断"某行的值在不在另一个查询的结果里"，IN 与 EXISTS 都能胜任，但机制不同——IN 先把子查询算成集合再逐行比对，EXISTS 把子查询当"存在性探测器"逐行问一次。选型看两侧规模：外表小、子查询结果大用 IN；外表大、探测条件命中率低用 EXISTS。NOT IN 遇到 NULL 会整体翻车，这是全 SQL 最著名的陷阱之一。

同一个问题的两种写法

需求："买过《算法导论》的顾客都有谁？"两种问法都成立。

-- 写法一 IN：先算出买过此书的顾客编号集合 再判断在不在
SELECT name
FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN books b        ON b.id = oi.book_id
  WHERE b.title = '算法导论'
);
​

-- 写法二 EXISTS：对每个顾客问一句 有没有一条订单明细买过此书
SELECT name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN books b        ON b.id = oi.book_id
  WHERE b.title = '算法导论'
    AND o.customer_id = c.id     -- 相关条件：连接外层当前顾客
);
​

两种写法输出一致：
+--------+
| name   |
+--------+
| 陈晓   |
+--------+
​

两段代码的读法差异值得逐字体会。IN 的子查询是非相关的：先独立跑完，得到编号集合，外层逐行判断"在不在集合里"。EXISTS 的子查询是相关的：AND o.customer_id = c.id 把外层当前顾客传进内层，内层变成"这位顾客买过此书吗"的探测，只要找到第一条满足的记录，EXISTS 立即返回真——内层甚至不必数完所有订单。SELECT 1 是惯用写法：EXISTS 只关心"有没有行"，不关心列，写 1、写星号、写列名语义相同。

执行侧的差异决定了选型经验：

多数现代优化器（MySQL 8、PostgreSQL）已经会把简单的 IN 与 EXISTS 相互改写成同一计划——但"依赖优化器"与"写得符合直觉"并不冲突，语义清晰永远是第一原则。

NOT IN：全 SQL 最著名的陷阱

把问题反过来："没买过《算法导论》的顾客是谁？"顺手把 IN 加个 NOT：

-- 陷阱现场：NOT IN 子查询里混进了一个 NULL
SELECT name FROM customers
WHERE id NOT IN (
  SELECT customer_id FROM orders WHERE status = 'cancelled'
);
​

+--------+
| name   |
+--------+
（空结果：一位顾客都没返回）
​

空结果就是案发证据。orders 里有一条历史脏数据：某笔取消订单的 customer_id 是 NULL（早年没建外键约束时混进来的）。三值逻辑的推演链（第 3 章的种子在此发芽）：

• 外层某个 id=1，子查询集合是 {5, 8, NULL}；
• NOT IN 展开成 "id<>5 AND id<>8 AND id<>NULL"；
• id<>NULL 的结果是未知，不是真；
• AND 上未知还是未知，整行条件结果未知；
• 未知过不了 WHERE——每一个顾客都被这个 NULL 拦下，一个不剩。

三种修法按优雅程度排序：

-- 修法一：子查询里显式排掉 NULL（最常用）
SELECT name FROM customers
WHERE id NOT IN (
  SELECT customer_id FROM orders
  WHERE status = 'cancelled' AND customer_id IS NOT NULL
);

-- 修法二：NOT EXISTS —— NULL 在等值连接里天然失配 不参与破坏
SELECT name FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.status = 'cancelled'
);

-- 修法三：从根上治理 脏 NULL 本不该存在
ALTER TABLE orders MODIFY customer_id INT UNSIGNED NOT NULL;
​

修法一/二 输出：全部四位顾客（没有人买过取消态订单）
修法三 报错先拒绝了存量脏数据 治理需先清洗再改约束
​

图 6-2 NOT IN 与 NOT EXISTS 判定流程

组合问题：买过 A 又买过 B

IN 与 EXISTS 的另一个舞台是"复合集合"问题。需求升级："既买过数据库类又买过算法类的顾客"。直觉写法 AND 两个 IN 是错的——同一个 WHERE 里两个条件作用于同一行，一笔订单不可能同时买两类：

-- 错误直觉：AND 两个 IN（同一行不可能同时满足）
SELECT name FROM customers
WHERE id IN (买过数据库类的顾客集合)
  AND id IN (买过算法类的顾客集合);   -- 每个IN独立判断 这个写法其实是对的
​

上面这条其实是正确的——两个 IN 的子查询各自独立物化成集合，AND 在集合层面取交集，语义正是"同时在两个集合里"。真正错的是把两个条件塞进同一个子查询用 AND 连接（一笔订单同时属于两类的矛盾）。用第 6.3 节的 INTERSECT 也能直白表达：

-- INTERSECT 版：两个集合取交集（PostgreSQL 支持 MySQL 需改写）
SELECT customer_id FROM 订单明细_数据库类
INTERSECT
SELECT customer_id FROM 订单明细_算法类;
​

交集输出：1（陈晓的编号）——她两类都买过
MySQL 无 INTERSECT 时 用 AND 双 IN 或 JOIN 自身改写
​

"买过 A 又买过 B"是用户画像的元问题，变式包括"买过 A 没买过 B"（加 NOT IN/NOT EXISTS）、"买过 A 或 B"（OR / UNION）。把这一族问题的三种写法（多 IN 组合、EXISTS 组合、集合运算）各写一遍，子查询与下一节的集合运算就串成了一条线。

⚠️ 常见坑：在 NOT IN 的子查询里用 SELECT * 或多列——IN 只接受单列集合，多列会直接报语法错；以及忘了 NULL 这颗雷，测试数据干净、生产数据一脏就翻车，是"测试全过、上线即挂"的经典款。

💡 关键直觉：IN 问"值在不在集合里"，EXISTS 问"存不存在一条记录"。前者拿值找集合，后者拿行问事实。反向判断一律 NOT EXISTS 优先，把 NULL 的雷留在门外。

本节要点回顾

• IN 物化集合、EXISTS 逐行探测：探测找到一条即返回，SELECT 1 是惯用写法；
• 选型看规模：小对小用 IN，大外表加可索引探测用 EXISTS，超大子查询结果慎用 IN；
• NOT IN 遇 NULL 整体翻车：集合里的 NULL 让每行比较得未知，输出为空而非异常，极难察觉；
• 三种修法：子查询补 IS NOT NULL、改 NOT EXISTS、根治脏数据加约束；
• 组合集合问题："又买 A 又买 B"用两个独立 IN 的 AND（集合交），别把条件挤进同一子查询；
• 正向 IN/EXISTS 语义等价，反向默认 NOT EXISTS——这是能写进团队规范的结论。