子查询是什么
子查询是嵌套在另一条语句里的 SELECT，可以出现在 SELECT 列表（标量子查询，必须返回单值）、WHERE（IN、EXISTS、比较运算）、FROM（派生表）里。相关子查询要对外层每一行各执行一次，代价与外层行数挂钩；能等价改写为 JOIN 时通常更快，但两者的去重与 NULL 行为并不完全相同。
基本信息
| 适用版本 | MySQL 5.7 / 8.0，写法通用 |
|---|---|
| 主要类型 | 标量、IN、ANY/ALL、EXISTS、派生表 |
| 执行方式 | 非相关子查询可先求值；相关子查询逐行求值 |
| 8.0 优化 | 半连接、子查询物化、派生表合并 |
| 改写方向 | JOIN、派生表加索引、窗口函数替代分组取前 N |
典型使用场景
- 标量对比：把全表平均值放进 SELECT 列表，与每行金额比较
- 集合匹配：用 IN 子查询把另一张表的 id 集合作为条件
- 存在性判断：用 EXISTS 判断关联表是否存在满足条件的行
- 反连接：用 NOT EXISTS 找出没有对应记录的用户
- 聚合后关联：先用派生表做分组统计，再与主表 JOIN
- 数据核对：对比两个来源的差异行，定位同步遗漏
常见写法
常见写法
SELECT id, amount, (SELECT AVG(amount) FROM orders) AS avg_all FROM orders; -- 标量子查询
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE city='Hangzhou');
SELECT u.id FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id=u.id AND o.amount>1000);
SELECT u.id FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id=u.id); -- 反连接
SELECT t.channel, t.cnt FROM (SELECT channel, COUNT(*) cnt FROM orders GROUP BY channel) t
WHERE t.cnt > 100; -- 派生表
EXPLAIN SELECT id FROM orders WHERE user_id IN (SELECT id FROM users WHERE city='Hangzhou');变体与改写要点
| 类型 | 语义 | 注意点 |
|---|---|---|
| 标量子查询 | 返回单值参与比较 | 返回多行会直接报错 |
| IN 子查询 | 判断是否属于集合 | 子查询含 NULL 时结果可能为 UNKNOWN |
| EXISTS | 只判断是否存在，命中即可停 | 大表场景通常优于 IN |
| 派生表 | 把子查询当临时表用 | 8.0 可能合并到外层，需看执行计划 |
| NOT EXISTS | 反连接，找不匹配行 | NOT IN 遇 NULL 会返回空结果 |
MySQL 8 对 IN 子查询有半连接优化；但大结果集仍建议用 JOIN 或 EXISTS 做等价改写并对比执行计划。
NOT IN 遇到子查询结果里有 NULL 时会整体返回空结果，这是最常见的隐性事故；改用 NOT EXISTS 或显式加 IS NOT NULL 条件即可避免。
用 IN 子查询替代 JOIN 时要注意去重语义：JOIN 可能把行数放大，IN 的结果天然去重；反过来，确实需要关联表的其他列时，必须改写为 JOIN 或 EXISTS 加子查询取值。