IN 子查询与半连接
IN 子查询表达「左表的行在子查询结果中存在」，语义上属于半连接：每行最多输出一次，不会像普通 JOIN 那样按匹配条数放大结果。优化器通常把它改写成半连接来执行——MySQL 8 提供 FirstMatch、LooseScan、Materialization 等多种策略，PostgreSQL 常用哈希半连接，所以「子查询一定比 JOIN 慢」是过时的说法。判断依据始终是执行计划，而不是直觉。
基本信息
| 语义 | 存在即保留该行，每行至多出现一次 |
|---|---|
| MySQL 8 策略 | 半连接（FirstMatch / LooseScan / Table Pullout 等）或物化后连接 |
| PostgreSQL | 通常改写为哈希半连接或嵌套循环半连接 |
| 语法限制 | IN 子查询只能返回一列，多列会直接报错 |
| 与 JOIN 的区别 | JOIN 输出两侧列并按匹配行数展开，IN 只做筛选 |
典型使用场景
- 白名单筛选：只保留存在于名单表中的用户或订单
- 只筛不取：需要的列全在主表，不需要副表的任何字段
- 替代 EXISTS 的等价写法：语义相同，写法更短
- 分页列表的主过滤条件：先确定候选主键，再取明细
- 数据校验：检查主表里有没有「明细表完全缺失」的孤儿行
三种等价写法
三种等价写法
SELECT u.id, u.name
FROM users u
WHERE u.id IN (SELECT o.user_id FROM orders o WHERE o.amount > 100);
# 等价写法：EXISTS 更直观地表达「存在」，大子查询下也常被优化得更好
SELECT u.id, u.name
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o
WHERE o.user_id = u.id AND o.amount > 100);
# 需要副表字段时才用 JOIN，并注意行数放大
SELECT DISTINCT u.id, u.name
FROM users u JOIN orders o ON o.user_id = u.id
WHERE o.amount > 100;常见报错
常见报错
# 错误：子查询返回多列
SELECT * FROM users WHERE id IN (SELECT id, name FROM vip_users);
# ERROR 1241: Operand should contain 1 column(s)
# 修正：只取需要的单列，或改用 EXISTS 相关子查询
SELECT * FROM users WHERE id IN (SELECT id FROM vip_users);几种写法对比
| 写法 | 行数是否放大 | 说明 |
|---|---|---|
| IN 子查询 | 不放大 | MySQL 半连接或物化；PG 哈希半连接 |
| EXISTS | 不放大 | 半连接，找到首行即可短路 |
| JOIN | 会放大 | 需要输出副表列，必要时配合 DISTINCT |
| NOT IN | 不放大 | 子查询含 NULL 时结果直接为空，需格外小心 |
MySQL 的半连接能力受 optimizer_switch 中的 semijoin / materialization 开关影响；出现性能异常时可以对比开关前后的执行计划，但不要在线上长期关闭它们。
改用 NOT IN 时要警惕 NULL：只要子查询结果里出现一个 NULL，整个 NOT IN 就会返回空集，筛选条件形同失效。