1. 子查询解决的业务问题和它的执行直觉
1.1 同一个需求，三次查询与一条SQL的差别
带新人的时候，我经常用这样一个需求开场：查出工资高于公司平均工资的所有员工。让新人先用三条SQL做，写出来大概是这样的：
SELECT AVG(salary) FROM emp;拿到结果比如是 7820.50，然后手动填进第二条查询：
SELECT ename, salary FROM emp WHERE salary > 7820.50;如果平均工资变了，或者换一个部门、换一个时间段，又得重新查一次、重新填一次。这个流程最大的问题是：你让数据库多干了一轮活，更麻烦的是中间结果靠人肉搬运，容易出错。你要是把这条SQL直接写进报表系统，明天平均工资一变，报表数字就错得莫名其妙。
子查询干的事情，就是把这"三次查询"压缩成一条SQL：
SELECT ename, salary FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);内层的SELECT AVG(salary) FROM emp先执行，算出平均值，把结果交给外层的 WHERE 条件继续比较。对 MySQL 来说，它会在内部帮你安排好执行顺序，不需要你手动维护中间结果。
这就是子查询的第一直觉理解：一个查询的结果，作为另一个查询的输入数据源。
1.2 子查询的本质：结果集作为另一个查询的输入
再往深一层想。SQL 是声明式语言，你告诉数据库"我要什么"，而不是"怎么找"。子查询就是在这种思路下自然而然长出来的东西：构造查询的时候，你会发现很多过滤条件本身不是一个固定值，而是一个"需要先算一算才知道的结果"。
比如"查工资比所有财务部员工都高的人"，这里的"财务部员工的工资"不是一个常量，而是一组数据，你必须先查出来才能继续比。子查询就是把这组数据"内联"进了条件里。
我自己的一个经验是：学子查询最容易卡住的不是语法，而是脑子里的执行模型。你只需要记住一个简化模型——MySQL 先执行子查询，把结果返回给外层查询去用，大部分语法问题都能想通。至于实际执行时优化器会不会把子查询改写，那是另一层面的事，后面第五部分专门讲性能时再说。
搞懂这个本质之后，第二个问题接踵而至：子查询返回的结果有好几种形状。返回一个数字、返回一列数字、返回一整行数据、返回一张临时表，这四种情况在 MySQL 里写法完全不同。这就是接下来要说的四类子查询。
2. 四类子查询：标量、列、行、表，各自怎么认怎么写
2.1 标量子查询：必须保证返回一个值
标量子查询是最简单的一种：子查询只返回一个值（一行一列）。它最常见的使用位置是 WHERE 条件里跟=、>、<这样的单值比较运算符搭配。
SELECT ename, salary FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);这个子查询返回一个平均值，所以外层能拿它跟 salary 直接比较。
标量子查询还可以出现在 SELECT 子句里，作为一个计算列：
SELECT ename, salary, (SELECT AVG(salary) FROM emp) AS avg_salary FROM emp;这样每行记录后面都会带一个全公司的平均薪资做对照，非常适合做报表。
写标量子查询最需要留意的是：它必须保证只返回一行一列。只要子查询里没写好条件、返回了两行，MySQL 就会报错：
-- 如果表里有两个部门都叫财务部，这条SQL直接报错 SELECT ename, salary FROM emp WHERE dept_id = (SELECT dept_id FROM dept WHERE dname = '财务部');报错信息我后面会详细讲。这里先说结论：凡是用了=的标量子查询，子查询结果一定得是单行，否则等于告诉 MySQL "我不知道你想比哪一行"。
2.2 列子查询与 IN 的黄金搭档
列子查询返回的是单列多行，可以用 IN、ANY、ALL 这些关键字来承接。最基础的就是 IN：
SELECT ename, salary FROM emp WHERE dept_id IN (SELECT dept_id FROM dept WHERE city = '上海');子查询返回上海所有部门的 dept_id，比如是 10、20、30 三行，外层再用dept_id IN (10,20,30)去过滤。这其实就是把一个"之前需要两三次 JOIN 才能完成的查询"变得非常直白。
遇到 NOT IN 时有一个特别隐蔽的坑：如果子查询结果里含有 NULL，NOT IN 会整体失效，一条数据都查不出来。这个我放到第六部分的翻车现场里详细拆，这里先记住：能用 NOT EXISTS 就用 NOT EXISTS，别轻易碰 NOT IN。
2.3 行子查询：整行对齐的比较
行子查询返回一行多列，写法上必须用圆括号把多列包在一起，整体跟另一个"行"比较：
SELECT ename, salary FROM emp WHERE (job, salary) = ( SELECT job, salary FROM emp WHERE eid = 10086 );这条SQL的意思是：去找所有"岗位和工资都跟10086号员工一样"的人。子查询查出来的是一行，包含 job 和 salary 两个字段；外层把每一行员工的 (job, salary) 组成一个行值，跟子查询那一行做整体比较。
行子查询看起来是 MySQL 支持的语法里用得最少的一种，习惯之后你会发现它其实很适合"按多列条件找相似记录"的场景。但注意，如果子查询返回的不只一行，照样报 "Subquery returns more than 1 row"。
2.4 表子查询：FROM 子句的派生表
表子查询（也叫派生表）返回多行多列，整体像一张临时表，常出现在 FROM 子句里：
SELECT t.dept_id, t.cnt FROM ( SELECT dept_id, COUNT(*) AS cnt FROM emp GROUP BY dept_id ) t WHERE t.cnt > 5;这里子查询先按部门统计人数，外层再从这个统计结果里捞"人数大于5的部门"。注意这条SQL里我给派生表起了个别名t，没有别名 MySQL 会直接报错：
Every derived table must have its own alias
原因很简单：外层的t.dept_id必须有一个可以引用的表名。就算你不引用列名，MySQL 也强制要求派生表必须带别名，这个语法规定从老版本到 8.0 一直存在，别在这上面交学费。
表子查询的应用场景非常广，比如分页后用外部条件过滤、从聚合结果里再聚合、取每组前 N 条之类的复杂需求，基本都是靠派生表实现的。
3. WHERE 只是入口——SELECT、FROM、HAVING 里的子查询各有各的脾气
3.1 SELECT 子句的标量关联子查询
最容易被忽略的子查询位置其实是 SELECT 子句。它通常配合关联条件使用：子查询内部引用外层查询的列，逐行去计算。
SELECT e.ename, e.salary, e.dept_id, (SELECT d.dname FROM dept d WHERE d.dept_id = e.dept_id) AS dept_name FROM emp e;对 emp 的每一行，MySQL 都拿当前的e.dept_id去 dept 表里找对应的部门名，拼在当前行后面。
这种写法的执行代价要格外小心：如果外层有 10 万行，且子查询里的 dept_id 没有索引，就要做 10 万次查找。所以我在实践里用 SELECT 子句中的关联子查询时，一定会确认关联字段有索引，否则宁可用 LEFT JOIN 把它改写掉：
SELECT e.ename, e.salary, e.dept_id, d.dname FROM emp e LEFT JOIN dept d ON d.dept_id = e.dept_id;什么时候必须保留 SELECT 里的子查询？比如你想在每行后面附上全公司的平均工资做对比，而这个平均值是个聚合结果，用 JOIN 会放大行数，用子查询反而干净：
SELECT e.ename, e.salary, (SELECT AVG(salary) FROM emp) AS company_avg FROM emp e;3.2 FROM 子句的派生表：给小结果集起个名字
FROM 子句的子查询本质上就是派生表，前面已经提到基本写法。这一小节的实操意义在于：派生表很多时候是一次性中间结果，不要重复去物化一份很大的数据。
看一个实际场景。你想查各部门平均工资，以及平均工资高于全公司平均的部门：
SELECT t.dept_id, t.avg_salary FROM ( SELECT dept_id, AVG(salary) AS avg_salary FROM emp GROUP BY dept_id ) t WHERE t.avg_salary > (SELECT AVG(salary) FROM emp);注意这里派生表t只做了一层聚合，数据量不会太大，MySQL 会把物化后的结果缓存起来，外层再过滤。如果你在派生表里写复杂 JOIN，再在外层做同样复杂的查询，那就等于让 MySQL 多跑一遍，白白浪费。
另一个细节：派生表里能不能用 ORDER BY？能用，但很多情况下没意义。如果一个派生表只是用来被外层过滤，里面的排序结果除非配合 LIMIT 做"取前几条再过滤"，否则最终顺序由外层决定，内层排序经常被优化器忽略。
MySQL 8.0.14 之后还支持了 LATERAL 派生表，可以引用同一 FROM 子句中前面表的列，这让每组的 TOP N 查询能写得更简洁：
SELECT d.dname, sub.eid, sub.salary FROM dept d, LATERAL ( SELECT e.eid, e.salary FROM emp e WHERE e.dept_id = d.dept_id ORDER BY e.salary DESC LIMIT 3 ) sub;这对选手动的"分组建 TOP N"需求是很大的语法解放，不过使用场景相对进阶，先把普通派生表用熟再上不迟。
3.3 HAVING 子句：子查询做分组门槛
HAVING 是对分组后的结果做过滤，它也支持子查询。典型的用法是把"全体平均水平"作为分组门槛：
SELECT dept_id, AVG(salary) AS avg_salary FROM emp GROUP BY dept_id HAVING AVG(salary) > (SELECT AVG(salary) FROM emp);这条SQL查的是：哪些部门的平均工资高于全公司平均水平。这里子查询的结果是一个标量（全公司平均），它跟 HAVING 里再聚合出来的AVG(salary)比较。
我见过的初学者错误是：在 HAVING 里直接写salary > (SELECT ...)，把原始行字段跟聚合结果混用。记住 HAVING 里你只能引用分组列和聚合函数，原始行的 salary 在这里是没有意义的。
4. EXISTS、ANY、ALL：这三个关键字最难缠，语义差异必须先理清
4.1 EXISTS 只关心有没有，和 IN 不是一回事
EXISTS 的子查询不返回具体数据，只返回"有没有记录"这个布尔结果。子查询写 SELECT 什么字段都无所谓，SELECT 1甚至SELECT NULL都行，MySQL 只判断"是否能查到一行"。
SELECT d.dept_id, d.dname FROM dept d WHERE EXISTS ( SELECT 1 FROM emp e WHERE e.dept_id = d.dept_id );这条SQL的语义是：只要 dept 表里的某个部门有至少一个员工，就返回这个部门。
对比 IN 的写法：
SELECT dept_id, dname FROM dept WHERE dept_id IN (SELECT dept_id FROM emp);看起来结果一样，实际执行差异可能很大。EXISTS 走的是"从外层表取一行，到内层去探测"的逻辑，所以它特别适合外层结果集小、内层关联字段有索引的场景。IN 则常常被 MySQL 改写成半连接（semi-join），跟 EXISTS 的差距在优化器层面会不断变化，不能一概而论说谁一定快。
但有一个场景我必须强调：NOT IN 遇到 NULL 会翻车，而 NOT EXISTS 不会。这是无数人在生产库里踩过的坑，我专门在第六部分展开。
4.2 ANY 与 ALL：比较运算符的"放大镜"
ANY 和 ALL 必须跟比较运算符配合使用，它们的语义直白地讲就是：
> ANY(子查询)：大于子查询返回的任意一个值，也就是大于其中最小的值> ALL(子查询)：大于子查询返回的所有值，也就是大于其中最大的值< ANY(子查询)：小于任意一个值，即小于最大的值< ALL(子查询)：小于所有值，即小于最小的值
举个例子。查工资比销售部任意一名员工都高的非销售部员工：
SELECT ename, salary FROM emp WHERE salary > ANY ( SELECT salary FROM emp WHERE dept_id = 20 ) AND dept_id <> 20;再查工资比销售部所有人都高的：
SELECT ename, salary FROM emp WHERE salary > ALL ( SELECT salary FROM emp WHERE dept_id = 20 ) AND dept_id <> 20;如果是处理"大于所有"的需求，我建议直接改成聚合函数写法，语义更清晰，性能也通常更好：
SELECT ename, salary FROM emp WHERE salary > (SELECT MAX(salary) FROM emp WHERE dept_id = 20) AND dept_id <> 20;4.3 等价改写关系：IN、ANY、ALL与聚合函数的换算
这一节整理一个等价关系表，我平时改写 SQL 时经常用到，直接给了结论：
| 原写法 | 等价写法 |
|---|---|
x IN (子查询) | x = ANY (子查询) |
x NOT IN (子查询) | x <> ALL (子查询) |
x > ALL (子查询) | x > (SELECT MAX(x) FROM ...) |
x < ALL (子查询) | x < (SELECT MIN(x) FROM ...) |
x > ANY (子查询) | x > (SELECT MIN(x) FROM ...) |
x < ANY (子查询) | x < (SELECT MAX(x) FROM ...) |
特别警告一个易错点：x <> ANY (子查询)并不等于x NOT IN (子查询)。<> ANY表示"只要与其中一个值不相等就成立"，很可能把本来应该过滤掉的记录全部放进来。举个例子，如果子查询返回 [10, 20, 30]，salary <> ANY(...)对于 salary=10 的记录来说：10<>20 为真，所以这条记录会被选出来——这显然不是你写 NOT IN 的意图。
还有一个隐蔽细节：ALL 在子查询返回空集合时返回 TRUE，所以salary > ALL (空结果)会变成全表都满足条件；ANY 在空集合时返回 FALSE。改写成语义明确的聚合查询（MAX/MIN 对空结果返回 NULL，比较结果为 UNKNOWN，条件不成立）能帮你规避这个逻辑坑。
5. 子查询的性能账：为什么慢、怎么用 EXPLAIN 看清真相、如何改写
5.1 相关子查询重复执行的代价
子查询慢的最大根源，是关联子查询对外层每一行都要执行一次。
拿一个典型 SQL 举例：
SELECT e.ename, (SELECT d.dname FROM dept d WHERE d.dept_id = e.dept_id) AS dept_name FROM emp e;如果 emp 有 20 万行，子查询在有索引的情况下执行 20 万次单点查询可能还好；但如果 dept_id 上没有索引，这 20 万次子查询全是全表扫描，性能直接雪崩。
在 EXPLAIN 的输出里，这种子查询会被标记为DEPENDENT SUBQUERY，一看就知道是关联子查询。看到这个词，第一反应就应该是：这里可能有一张被反复全表扫的关联表，赶紧看内层关联字段有没有索引。
5.2 EXPLAIN 怎么读子查询部分
用 EXPLAIN 看子查询相关执行计划，重点看几类关键词：
| 输出标记 | 含义 | 处理建议 |
|---|---|---|
DEPENDENT SUBQUERY | 相关子查询，外层每行执行一次 | 优先检查关联字段索引，或改写为 JOIN |
SUBQUERY | 非相关子查询，一般执行一次 | 相对安全，关注物化开销 |
MATERIALIZED | 子查询被物化成临时表 | 关注物化结果大小，加好索引 |
UNCACHEABLE SUBQUERY | 子查询每次都要重算（如用了 RAND() 等非确定性函数） | 尽量避免这类写法 |
MySQL 8.0 之后还可以用EXPLAIN ANALYZE直接看实际执行时间和循环次数：
EXPLAIN ANALYZE SELECT ename, salary FROM emp WHERE dept_id IN (SELECT dept_id FROM dept WHERE city = '上海');它会输出类似Nested loop inner join这样的执行细节，你能直观看到优化器到底把 IN 子查询改写成了什么连接方式。实话讲，把EXPLAIN ANALYZE养成习惯之后，你会发现自己对 SQL 性能的直觉会准很多，至少不会再凭感觉瞎猜。
5.3 实用改写套路：JOIN、EXISTS、临时表
我根据自己的实战经验，整理了几条子查询改写的主路径，大家在现场遇到慢查询直接按这个方向排查：
第一，把 SELECT 子句里的关联子查询改成 LEFT JOIN。因为前者本质上是逐行去查，后者一次连接搞定。注意改完之后要检查有没有行数放大问题，必要时加 DISTINCT。
-- 原写法 SELECT e.ename, (SELECT d.dname FROM dept d WHERE d.dept_id = e.dept_id) AS dept_name FROM emp e; -- 改写 SELECT e.ename, d.dname FROM emp e LEFT JOIN dept d ON d.dept_id = e.dept_id;第二，EXISTS 与 IN 的选择要基于数据分布。外层表小、内层表大且有索引时，EXISTS 风格通常表现更好；外层表大、内层结果集小，IN 或半连接路径可能更优。MySQL 8.0 的优化器会自动做一些重写，但搞清楚原理仍然能帮你判断执行计划是否合理。
第三，带聚合的慢子查询，先物化再加工。如果 IN 子查询里带着 GROUP BY、聚合函数，优化器往往只能物化子查询结果。这时候你可以在应用层先把子查询跑一遍，把结果作为常量列表传入 IN，或者让派生表走哈希连接，看看 EXPLAIN 里有没有出现 hash join，通常物化后的连接效率并不差。
第四，不要无脑改成 JOIN。JOIN 会带来行数放大，一个部门里 10 个员工，你 JOIN 完就多出 9 行重复数据，有时还得 DISTINCT 回去，反而更慢。我见过不少把简单的IN子查询改成 JOIN 反而拖慢全表的案例。
6. 实践中最常见的翻车现场与排查思路
6.1 NOT IN 遇到 NULL：看起来对的 SQL 查不到数据
这是子查询里最经典、影响面最大的坑。
假设 emp 表里有员工，dept 表里有部门，但恰好 dept 表中有一个dept_id为 NULL 的记录（或者 emp 中有员工没分配部门，dept_id 为 NULL）。你想查"没有分配在任何部门记录里"的员工：
SELECT * FROM emp WHERE dept_id NOT IN (SELECT dept_id FROM dept);这条SQL在很多人眼里天经地义，但执行结果经常是空集，甚至一条都不返回。
原因在于 SQL 的三值逻辑。NOT IN等价于<> ALL，当子查询结果里出现 NULL 时，dept_id <> NULL的结果是 UNKNOWN，不是 TRUE。而 WHERE 只保留 TRUE 的记录，UNKNOWN 全部被过滤掉，于是整个查询返回空。
排查思路很简单：把子查询单独跑一遍，看看有没有 NULL，或者直接用这条能防坑的写法：
SELECT * FROM emp WHERE dept_id NOT IN (SELECT dept_id FROM dept WHERE dept_id IS NOT NULL);但我更推荐直接养成用 NOT EXISTS 的习惯：
SELECT e.* FROM emp e WHERE NOT EXISTS ( SELECT 1 FROM dept d WHERE d.dept_id = e.dept_id );NOT EXISTS 的语义是"不存在这样一条部门记录"，完全不涉及 NULL 的比较，逻辑上更不容易出错，性能上配合索引也往往表现更好。
6.2 子查询返回多行：Subquery returns more than 1 row
这个报错几乎所有写子查询的人都会遇到，而且第一次遇到时最容易懵。它的意思很简单：你用了=这种单值比较，但子查询返回了两行以上。
最常见的场景是：
SELECT ename FROM emp WHERE dept_id = (SELECT dept_id FROM dept WHERE city = '上海');如果上海有多个部门，这个子查询返回 10、20 两行，MySQL 不知道拿哪一行跟 dept_id 比较，直接报错。
排查步骤我建议按顺序走：
- 把子查询单独摘出来跑一遍，数一下返回多少行；
- 确认需求到底是想"匹配一个值"还是"匹配一组值"——前者用子查询加条件保证单行，后者改成 IN、ANY 或 EXISTS；
- 如果是聚合类需求，检查子查询里是不是漏了 GROUP BY，或者漏了限定条件（比如限定某个具体部门）。
顺手说一种容易漏的情况：子查询里忘加限制条件，碰巧表数据量小的时候不报错，数据量一大立刻炸。所以上线前一定要在接近生产数据量的环境里跑一遍。
6.3 别踩的界限：子查询里 LIMIT 和 ORDER BY 的版本差异
子查询里用 LIMIT，在不同版本里态度完全不同。
老版本 MySQL（5.x 时代）里，很多包含 LIMIT 的子查询直接不让用，报错信息常是：
This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
你要是在 WHERE dept_id IN (子查询) 里写 LIMIT，大概率会撞上这个错误。处理办法是包一层派生表：
SELECT ename FROM emp WHERE dept_id IN ( SELECT dept_id FROM ( SELECT dept_id FROM dept ORDER BY dept_id LIMIT 2 ) x );再说 ORDER BY。在子查询里写 ORDER BY，除非搭配 LIMIT，否则很多时候是没有意义的。因为子查询结果如果没有 LIMIT，优化器可能直接忽略排序，你以为是取"最大的一条"，实际取出来的是全量。就算你配了 LIMIT，也要明白：MySQL 8.0.31 之后对派生表里"有 LIMIT 却没有 ORDER BY"的写法变得非常警惕，取出来的行可能是随机的。
所以我的建议很简单：子查询里用 LIMIT，务必同时写 ORDER BY，让它明确"我要的是排序后的前几条"；能不用 LIMIT 子查询就别用，很多需求改写成窗口函数（比如 ROW_NUMBER）更干净，MySQL 8.0 里窗口函数已经足够成熟。
6.4 静默不出结果的等值判断：子查询返回 NULL
还有一种 "错的没报错，但就是查不到数据" 的坑，比报错更麻烦。
SELECT ename FROM emp WHERE dept_id = (SELECT dept_id FROM dept WHERE dname = '不存在的部门');子查询返回的结果是 NULL，那么dept_id = NULL的结果是 UNKNOWN，WHERE 不保留 UNKNOWN，于是整个查询返回空集。没有报错、没有警告，就是结果为空。
排查这种问题时，先单独跑一下子查询，看返回值是不是 NULL，或者考虑用<=>（NULL 安全等于）来明确"我允许 NULL 参与比较"。但更合理的做法是：如果子查询可能查不到数据，先在外面做个判断，或者在应用层把子查询结果单独查一次。
我在带团队的时候反复交代一句话：子查询里查不到数据不是错误，但不提前想清楚 "查不到时外层要怎么办"，才是真正的错误。
6.5 一个综合案例的完整排查链路
说一个我自己实际处理过的慢查询，能把这几个坑串起来。
某次线上有个报表SQL，查"近30天有订单的客户"：
SELECT c.customer_id, c.customer_name FROM customer c WHERE c.customer_id IN ( SELECT customer_id FROM orders WHERE order_status = 'PAID' AND order_date >= NOW() - INTERVAL 30 DAY )表不大，customer 3 万行，orders 80 万行，但这个查询每次都要 4 秒多。EXPLAIN 一看，IN 子查询走了物化，orders 全表扫描后被物化成临时表，再跟 customer 连接。
我做的第一件事是把 order_status 和 order_date 的复合索引补上：
ALTER TABLE orders ADD INDEX idx_status_date (order_status, order_date);索引生效后，物化的数据量从 80 万行缩到近 30 天实际成交的几万行，查询降到 0.8 秒。
第二件事是加了一个 customer_id 为空的安全判断。订单表里历史数据有脏数据，customer_id 偶尔是 NULL。虽然这次查询里有 INNER JOIN 语义不会选出来，但后续有同事用同样的子查询改 NOT IN 需求时，直接中了 6.1 说的 NULL 坑。我最后把这段 SQL 统一改成 EXISTS 写法，两拨人的需求都能覆盖，而且语义更稳：
SELECT c.customer_id, c.customer_name FROM customer c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.order_status = 'PAID' AND o.order_date >= NOW() - INTERVAL 30 DAY )结论很清晰：子查询本身不慢，慢的是没有索引、没有想清楚 NULL 边界、没有选对关联写法。
最后再分享一个我的调试心得。遇到子查询相关的问题，先用五步法走一遍：第一步，把子查询单独抽出来跑，确认返回内容的形状；第二步，明确这个形状匹配的是哪种子查询类型；第三步，检查内层涉及字段的索引；第四步，用 EXPLAIN ANALYZE 验证实际执行路径；第五步，判断是否需要改写为 JOIN 或 EXISTS。这五步走完，九成的问题都能定位到根因，剩下的基本就是业务需求自己没定义清楚。子查询这东西，学的时候觉得不就是嵌套嘛，真正在生产环境把它用明白、用稳，靠的还是对执行模型和数据分布的持续理解。
配置全解析：从上架到用户支持)
