前几天有个朋友发来一条SQL，三层子查询嵌套，跑了小半天都不出结果，问我怎么优化。我一看，他是用相关子查询在百万行表里做逐行扫描，关联字段上还没有索引，不慢才怪。MySQL里的子查询（subquery）就是这么个东西：用好了是利器，用歪了是灾难，而且不管是面试还是日常开发，几乎绕不开它。
这篇文章我不想堆空洞理论，就从实际开发者的角度，把MySQL子查询从头到尾盘一遍：先讲清楚子查询到底是什么、哪些场景必须用它，再把标量子查询、IN、EXISTS、派生表逐个拆开，接着拿一个学生课程成绩库做完整实战，最后重点聊聊性能优化和几个容易踩的坑。适合刚接触SQL的新人，也适合写了几年SQL但一遇到子查询优化就头疼的老同学。
1. 先搞清楚子查询是什么：一个查询套在另一个查询里
1.1 子查询的定义与三种基础形态
子查询，说白了就是一个SELECT语句嵌套在另一个SQL语句里，被嵌套的那条SELECT通常用括号包起来。它内部的执行结果会作为外部查询的输入，可以是一个值、一列数据、一行记录，也可以是一整张临时表。
根据返回值，我习惯把子查询分成三种形态来看：
- 标量子查询：返回一行一列，比如
(SELECT MAX(score) FROM score)，就是一个数字或一个字符串，可以像普通值一样放在比较表达式中。 - 列子查询/行子查询：返回一列多行或者一行多列，最典型的就是
WHERE student_id IN (SELECT student_id FROM ...)，把子查询的结果当作一个集合。 - 表子查询（派生表）：返回一个多行多列的结果集，出现在FROM子句后面，相当于临时造了一张表，例如
FROM (SELECT ...) AS t。
子查询可以出现的位置也很多：WHERE子句、HAVING子句、FROM子句、SELECT的字段列表，甚至在UPDATE和DELETE语句里也照样能用。很多人以为子查询只是“查询时用的”，其实更新和删除操作中它更常用，后面我会专门演示。
不少初学者会把子查询和JOIN搞混。简单理解，JOIN是把两张表横向拼接，子查询是先算出一部分结果再交给外层逻辑，更像“分步计算”。这两种方式在很多场景下能互相替代，但语义和性能可能完全不同，这个是第4章的重点。
1.2 为什么需要子查询：有些过滤条件必须先“算”出来
很多场景用JOIN也能做，但子查询有它不可替代的位置。最典型的是“WHERE条件里拿聚合结果做比较”。
举个例子，你想查“成绩高于全校平均分的学生”。SQL的语法规定WHERE子句里不能直接写聚合函数，你不能写成：
SELECT * FROM score WHERE score > AVG(score);这句话在MySQL里直接报错。正确的思路是先用子查询把平均分算出来，再放到WHERE里去比较：
SELECT * FROM score WHERE score > (SELECT AVG(score) FROM score);在这里子查询就相当于先算出“全校平均分”这个常量，然后外部查询拿这个常量去逐行过滤。这种“先算一步，再比一步”的逻辑，用JOIN也能写，但通常要绕一大圈，远不如子查询直观。
还有一种场景，是希望筛选“主表中满足某个集合条件的记录”。比如“找出所有选过课的学生”“找出所有没有选课的学生”，这类需求如果用JOIN，会因为一对多关系让主表记录重复，还得加DISTINCT；而用子查询，语义上更贴合人的思考习惯。尤其是“没有选课”这种否定条件，NOT EXISTS和NOT IN的子查询写法几乎成了标准答案。
再说一个实战中很常见的场景：UPDATE和DELETE操作里需要引用另一张表的数据。比如“把所有低于课程平均分的成绩标记为待补考”，你必须先算出每门课的平均分，再去更新成绩表。这种场景子查询直接嵌入UPDATE语句里，写起来非常顺手。
所以我的判断是：子查询不是“能用但尽量少用”的东西，而是一个独立的思考层级。它在聚合比较、集合判断、派生表加工这三个方向上尤其有优势。
2. 核心语法拆解：从标量子查询到相关子查询
2.1 标量子查询：返回一行一列，当普通值用
标量子查询是入门最简单、也最容易踩坑的一种。它返回且只能返回一行一列，比如：
SELECT student_id, name, (SELECT MAX(score) FROM score) AS max_score FROM student;这条SQL会在每个学生后面都带上一列全校最高分，虽然这个例子业务意义不大，但它很好展示了标量子查询的用法：子查询的结果被当成一个字段值带在结果集里。
更实际一点的用法是在WHERE里做比较。比如查“成绩高于全校平均分的学生记录”：
SELECT * FROM score WHERE score > (SELECT AVG(score) FROM score);这里有个非常经典的报错：如果子查询返回了多行，MySQL会直接抛错，错误码是1242，提示信息是“Subquery returns more than 1 row”。很多人第一次遇到都懵了，明明逻辑没问题，为什么报错？因为标量子查询的使用场景是“代替一个值”，一个值只能有一个，多行就违反了基本语义。
还有一种情况是子查询结果为空。这个时候标量子查询不会报错，而是返回NULL。于是会出现一个隐蔽问题：外部用不等于比较时，NULL参与比较会导致结果过滤不干净，这个在第5章里会详细说。
2.2 IN与行子查询：把子查询结果当作集合
当子查询返回一列多行时，最常见的使用方式就是配合IN操作符。比如：
SELECT name FROM student WHERE student_id IN ( SELECT student_id FROM score WHERE course_id = 1 );这条SQL先找出选了课程1的学生ID集合，再从student表里把这些人查出来。它的执行逻辑很符合人的直觉：“先找到条件集合，再根据集合过滤”。
MySQL还支持一种行子查询的写法，把多个字段当作一个“组合值”来比较。比如我想查“每门课程的最高分记录”，可以用：
SELECT * FROM score WHERE (course_id, score) IN ( SELECT course_id, MAX(score) FROM score GROUP BY course_id );这里(course_id, score)是一个行构造器，子查询返回每一门课的课程号和最高分，外部用两列组合去匹配。这个方法可以解决“每组最大值”这类经典问题，代码比手动写相关子查询简洁很多，也不容易漏掉并列最高分的记录。
用IN的时候要注意，如果子查询结果集中包含NULL值，IN本身还是能正常工作的——只要匹配到任意一个非NULL的值就会返回TRUE。但如果你用的是NOT IN，一旦子查询结果里有NULL，结果集大概率会变成“空集”。这个坑放到第5章集中讲。
2.3 EXISTS与相关子查询：内外联动的关键
EXISTS是子查询体系里最难理解、也最重要的部分。它通常配合“相关子查询”一起出现，所谓相关，是指内层子查询引用了外层查询的字段，内外两层产生联动。
比如查询“至少选过一门课的学生”：
SELECT * FROM student s WHERE EXISTS ( SELECT 1 FROM score sc WHERE sc.student_id = s.student_id );这里的sc.student_id = s.student_id就是关联条件。每取一个学生，MySQL就会去score表里查一下这个学生有没有成绩记录。只要存在至少一条记录，EXISTS就返回真，这个学生就会被选中。
EXISTS只关心“内层有没有记录返回”，并不是真的要把记录取出来，所以内层写SELECT 1、SELECT *，甚至SELECT NULL都一样。它不参与NULL值的比较逻辑，所以NOT EXISTS在处理“不存在”类需求时，比NOT IN可靠得多。
不过相关子查询是有性能代价的。如果外层表有10万行，内层查询理论上就可能执行10万次，这在MySQL里叫“逐行执行”。一旦内层查询少了索引，性能会迅速恶化。很多“子查询慢”的传闻，其实都来自相关子查询被滥用。这章先记住它的特点和风险，第4章我专门讲优化办法。
2.4 FROM子句里的派生表：查询套查询的“临时表”
子查询放在FROM子句后面，就是派生表。它相当于在SQL执行过程中临时生成一张表，供外层继续查询。派生表必须起别名，否则MySQL会直接报错。
SELECT course_id, avg_score FROM ( SELECT course_id, AVG(score) AS avg_score FROM score GROUP BY course_id ) AS t WHERE avg_score > 80;这个例子里，内层先算出每门课的平均分，外层再筛出平均分大于80的课程。虽然这个特定需求用HAVING也能写，但一旦你要对聚合结果做二次计算，比如“过滤出平均分高于所有课程平均分平均值”的课程，派生表几乎就是唯一直观的解法。
MySQL 8.0对派生表做了不少优化，比如derived_merge，即优化器尝试把派生表和外部查询合并起来执行，避免真正物化成一张临时表。但能不能合并成功，要看外层查询的用法，如果派生表里用了聚合函数、DISTINCT、GROUP BY等操作，还是可能会物化成临时表。所以写派生表的时候，不要理所当然认为它是“零成本的”，也要看执行计划。
3. 实战：学生课程成绩库，把子查询用到极致
3.1 建表与造数：一个可直接复现的成绩库
光讲语法记不住，我自己学SQL的时候最有效的办法就是建一套最简单的学生课程成绩库，反复折腾。下面这个结构很基础，但足够覆盖80%的子查询场景。
CREATE TABLE student ( student_id INT PRIMARY KEY, name VARCHAR(20) NOT NULL, class_name VARCHAR(20) ); CREATE TABLE course ( course_id INT PRIMARY KEY, course_name VARCHAR(40) NOT NULL, credit DECIMAL(2,1) ); CREATE TABLE score ( id INT PRIMARY KEY AUTO_INCREMENT, student_id INT NOT NULL, course_id INT NOT NULL, score DECIMAL(5,1), remark VARCHAR(20), KEY idx_student (student_id), KEY idx_course (course_id), KEY idx_score (score) );注意我给score表加了三个索引。这不是为了示范而示范，而是因为后续几乎所有子查询案例都会在student_id、course_id、score这三个字段上做过滤和关联，没有索引的话，再好的查询写法也白搭。
插入几条简单数据：
INSERT INTO student (student_id, name, class_name) VALUES (1, '张三', '一班'), (2, '李四', '一班'), (3, '王五', '二班'), (4, '赵六', '二班'); INSERT INTO course (course_id, course_name, credit) VALUES (1, '数据库', 3.0), (2, '操作系统', 2.5), (3, '计算机网络', 2.0); INSERT INTO score (student_id, course_id, score) VALUES (1, 1, 92.0), (1, 2, 85.0), (2, 1, 86.0), (2, 3, 78.0), (3, 2, 95.0), (3, 3, 88.0);这里有意让赵六没有任何成绩，后面查“没有选课的学生”时正好派上用场。
3.2 查询每门课的最高分记录：IN和=的差别
先看一个非常经典的需求：找出每门课程的最高分记录，并把学生姓名等详情一起带出来。
最直观的关联子查询写法是这样的：
SELECT * FROM score s WHERE score = ( SELECT MAX(s2.score) FROM score s2 WHERE s2.course_id = s.course_id );如果每门课只有一个最高分，这种写法是对的。但实际数据里，很可能两个学生正好考了一样的最高分，这时候内层子查询就会返回两行，外层用等于号比较直接报1242错误。
更稳妥的写法是用行构造器配合IN：
SELECT s.*, st.name FROM score s JOIN student st ON s.student_id = st.student_id WHERE (s.course_id, s.score) IN ( SELECT course_id, MAX(score) FROM score GROUP BY course_id );这条SQL先把每个课程的最高分查出来，再回到score表里用多字段匹配，能够同时取回并列最高分的所有人。我在实际项目里就遇到过“线上一门课出现并列最高分，旧SQL漏数”的事故，所以涉及这类统计需求时，默认用IN比用=安全。
3.3 查询没有选课的学生：NOT EXISTS 慎用 NOT IN
这个需求直接对应“赵六”。不少人的第一反应是：
SELECT * FROM student WHERE student_id NOT IN ( SELECT student_id FROM score );看起来没问题，student_id是的子查询字段，而且score表里没有NULL，所以在这个演示库里能跑出正确结果。但一旦score表的student_id字段允许为空，或者某行数据意外写入了NULL，这条SQL的结果就会变成空集。
原因我放到5.2详细讲，现在先给出更可靠的写法：
SELECT * FROM student s WHERE NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.student_id = s.student_id );用NOT EXISTS，逻辑就变成本质上的“不存在任何一条成绩记录，才选中这个学生”。它不关心子查询里有没有NULL，也不用担心优化器的特殊行为，结果始终符合预期。
写“不存在”类需求时，我个人现在默认用NOT EXISTS，除非能确认子查询字段绝对没有NULL，才偶尔用NOT IN。
3.4 UPDATE里的子查询：同一张表的更新陷阱
子查询不只在SELECT里常见，UPDATE里也经常用。比如我想把“成绩低于全校平均分”的记录标记为待补考：
UPDATE score SET remark = '待补考' WHERE score < (SELECT AVG(score) FROM score);这条SQL在MySQL里会直接报错，错误码是1093，提示不能在同一张表上一边更新一边查。MySQL不允许UPDATE目标表和子查询引用的表是同一张表。
解决办法是包一层派生表，让MySQL把它当成一张“临时结果表”而不是原始目标表：
UPDATE score SET remark = '待补考' WHERE score < ( SELECT avg_score FROM ( SELECT AVG(score) AS avg_score FROM score ) AS t );如果是更复杂的“低于本课程平均分”，最好直接用UPDATE JOIN的方式：
UPDATE score s JOIN ( SELECT course_id, AVG(score) AS avg_score FROM score GROUP BY course_id ) c ON s.course_id = c.course_id SET s.remark = '待补考' WHERE s.score < c.avg_score;这种写法先构造各课程平均分的派生表，再和成绩表做关联更新，既绕过了同一张表的限制，又保证了逻辑清晰。记住一个判断原则：更新时如果子查询要引用目标表的数据，优先考虑包一层派生表，或者直接用JOIN更新。
3.5 DELETE里的子查询：安全删除的正确姿势
删除操作里的子查询和更新类似。比如删除没有选课的学生：
DELETE FROM student WHERE NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.student_id = student.student_id );删除前务必先用SELECT验证一遍结果。我处理线上数据时的习惯是先把DELETE临时改成SELECT：
SELECT * FROM student WHERE NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.student_id = student.student_id );确认结果集没问题，再改成DELETE执行，而且通常放到事务里：
START TRANSACTION; DELETE FROM student WHERE NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.student_id = student.student_id ); -- 确认影响行数无误后提交 COMMIT;大表删除时要注意锁表问题，DML会长期持有锁，尽量避开业务高峰期分批删除。
4. 性能对比与优化：子查询真的慢吗
4.1 子查询性能的争议来源：优化器的执行策略
很多老DBA一听到子查询就皱眉，说“子查询慢，能用JOIN就别用子查询”。这个说法在MySQL 5.5时代基本成立，因为早期优化器对子查询的处理方式非常粗糙，尤其是相关子查询，经常会逐行执行，外层有多少行，内层就执行多少次。
MySQL 5.6之后引入了大量子查询优化策略，包括半连接转换、物化、Exists策略等。到了MySQL 8.0，优化器已经相当成熟，IN子查询在很多情况下会被自动改写成半连接执行，性能并不比JOIN差，甚至在某些场景更快。
所以现在再拿“子查询等于慢”当结论，已经过时了。正确做法是看执行计划。子查询本身没有原罪，真正的问题是用户在写相关子查询时没有建立合适的索引，或者三层嵌套把自己的逻辑搞得复杂到优化器也无从下手。
4.2 用EXPLAIN判断子查询的执行方式
EXPLAIN是分析SQL性能的第一工具。拿刚才“查询每门课最高分”的语句举例：
EXPLAIN SELECT s.*, st.name FROM score s JOIN student st ON s.student_id = st.student_id WHERE (s.course_id, s.score) IN ( SELECT course_id, MAX(score) FROM score GROUP BY course_id );你会看到执行计划里有很多select_type值，常见的有：
| select_type | 含义 |
|---|---|
| SIMPLE | 普通查询，没有子查询和UNION |
| PRIMARY | 最外层的查询 |
| SUBQUERY | 非相关子查询，先算一次然后外层使用 |
| DEPENDENT SUBQUERY | 相关子查询，外层每行都执行一次 |
| DERIVED | FROM中的派生表 |
| MATERIALIZED | 子查询结果被物化成临时表 |
看到DEPENDENT SUBQUERY就要格外敏感，如果外层记录多、内层关联字段没有索引，基本可以断定这条SQL会慢。解决办法通常是在内层查询的关联字段上加索引。
比如相关子查询的内层是WHERE sc.student_id = s.student_id，那score.student_id就必须有索引。演示库里我已经建了，所以即便出现相关子查询，性能也不会差太多。实际项目里很多人建表时漏了这类索引，才是“子查询慢”的真正原因。
4.3 子查询、JOIN、EXISTS怎么选：一张表讲清楚
到底哪种写法更好？我的判断标准是先看语义，再看执行计划。不搞“一刀切”。
| 场景 | 推荐写法 | 说明 |
|---|---|---|
| WHERE里和聚合值比较 | 标量子查询 | 无法用JOIN表达，必须先把聚合算出来 |
| 过滤主表记录，不关心重复 | EXISTS或IN | EXISTS避免了NULL问题，语义更稳 |
| 需要同时取关联表的字段 | JOIN | 子查询无法直接输出另一张表的字段 |
| 查询“不存在”类记录 | NOT EXISTS | NOT IN遇NULL会翻车，谨慎使用 |
| 对聚合结果做二次过滤 | 派生表 | 把中间结果包装成临时表 |
| 每组分组的Top N | 窗口函数或相关子查询 | MySQL 8.0优先用窗口函数 |
我自己写SQL的顺序是：先保证逻辑正确，用最容易理解的子查询把结果跑出来；然后打开EXPLAIN看执行计划；如果发现相关子查询在逐行扫描，再考虑改写。比如把相关子查询改成JOIN，或者把IN改成EXISTS，然后对比两条SQL的执行计划，用数据说话，而不是“听别人说”。
5. 常见问题与排查技巧实录
5.1 Subquery returns more than 1 row：最常见的1242报错
这个报错高频到几乎所有写子查询的人都会遇到。错误信息：
ERROR 1242 (21000): Subquery returns more than 1 row原因很简单：标量子查询或等于号比较时，内层查出了多行数据。解决办法分情况：
- 如果业务上只需要任意一个值，内层加
LIMIT 1。 - 如果是“每组最大值”这类需求，用行构造器IN。
- 如果原本就用错了逻辑，应该改为EXISTS或IN。
排查时可以先把子查询单独拎出来跑一遍，看返回多少行，往往一眼就能发现问题。
5.2 NOT IN遇上NULL：为什么查不到任何数据
这是SQL里最著名的隐蔽坑之一。假设score表的student_id列允许为NULL，并且确实有NULL值：
SELECT * FROM student WHERE student_id NOT IN (1, 2, NULL);这条SQL的结果是空集。原因是SQL的三值逻辑：student_id无论等于哪个值，与NULL比较时结果都是NULL，而NULL在WHERE里会被当作不成立，所以一行都查不出来。
解决办法就是用NOT EXISTS替代。这也是MySQL面试题里高频出现的一个点，面试官常拿它考候选人到底是否理解NULL语义。
5.3 子查询里的ORDER BY和LIMIT：容易被忽视的坑
子查询里用ORDER BY排序，再交给外层使用，结果顺序不一定保得住。经典问题“取每个班级分数最高的学生之一”：
SELECT * FROM student s WHERE student_id IN ( SELECT student_id FROM score WHERE score GROUP BY class_name ORDER BY score DESC LIMIT 1 );这个写法本身问题很多，因为子查询里ORDER BY和LIMIT与外层IN组合时，语义可能有变化，而且MySQL优化器可能改写掉内层顺序。更可靠的做法是外层排序：
SELECT * FROM score WHERE student_id IN (...) ORDER BY score DESC;派生表里的ORDER BY也不保证外层最终结果的顺序，外层需要排序就必须在外层写ORDER BY。别把“内层排好序，外层直接用”当作默认行为。
5.4 优化器改写导致的“惊喜”：结果和预期不一致
MySQL优化器会在不影响语义的情况下重写SQL，比如把IN子查询改写成半连接。半连接在去重逻辑上和普通JOIN不同，有时候你以为会去重，实际却没去；有时候你以为不去重，结果反而去重了。
遇到“结果和预期不一致”时，除了检查业务逻辑，还要看两条线索：一是EXPLAIN的执行计划，看select_type发生了什么改变；二是打开optimizer trace，看优化器到底做了哪些改写：
SET optimizer_trace = 'enabled=on'; SELECT ...; SELECT * FROM information_schema.OPTIMIZER_TRACE;这个工具能直接输出优化器的决策过程，非常直观。排查“被改写”类问题时，比瞎猜高效得多。MySQL 8.0还支持NO_SEMIJOIN这类优化器hint，可以强制关闭某种策略，但生产环境不建议滥用，更多是用来验证问题。
写到这里，子查询的日常用法、典型实战和关键坑基本都过了一遍。我个人每次写SQL的习惯是：先把业务逻辑用最直白的方式写出来，哪怕是三层嵌套，跑通拿到正确结果，再打开EXPLAIN看执行计划，最后才考虑要不要改成JOIN、EXISTS或者用窗口函数。子查询本身不是洪水猛兽，不了解原理就乱优化才是真正的灾难。
最后再分享一个小技巧：如果你要反复调试一条带子查询的SQL，记得先给相关子查询里的关联列建索引，比如今天这个例子里的score表，student_id、course_id、score三个字段建好索引，90%的子查询慢问题都能缓解。遇到奇怪结果，优先怀疑NULL，其次怀疑优化器改写，按这两条思路排查，大多数坑都能快速定位。
