1. 子查询是什么，为什么后端和数据分析都得掌握
我第一次真正被 SQL 子查询“教育”了一回，是在一个统计报表的改造现场。一条主查询里嵌套了三个子查询，老板在旁边催着我定位慢在哪，而我盯着屏幕只能把每个括号一层层拆开人工推演，拆到一半脑子就烧了。后来我才意识到，真正让我痛苦的其实不是子查询语法，而是我不知道它是怎么执行的、什么时候该用、什么时候不该用。
所谓子查询，简单说就是一条完整的 SELECT 语句，被括号包住之后，嵌在另一条 SELECT、INSERT、UPDATE、DELETE 语句的里面。外层 SQL 拿内层返回的结果继续做筛选、计算、排序或者关联。听起来很简单，但在真实业务里，它几乎天天出现：查“比平均分高的学生”、查“没下过单的用户”、查“每个品类卖得最好的商品”，这些需求用子查询都可以写成一眼能看懂的 SQL。
举一个最基础、也最经典的例子：
SELECT id, name, city FROM users WHERE id IN ( SELECT user_id FROM orders WHERE total_amount > 1000 );这里括号内的部分就是一个子查询，它先把满足条件的user_id查出来，外层再根据这个结果集合去users表里找人。如果你是后端开发、数据分析师或者经常要和 MySQL 打交道的人，这个东西属于基本功，不掌握的话，写复杂统计 SQL 会很吃力。
1.1 一句话定义，外加一段能直接看懂的示例
再往深一步说，子查询的完整定义是：嵌套在外部查询内部的一条查询语句，它可以在外层查询执行之前、执行过程中，甚至逐行执行时被反复计算，并把返回值交给外层使用。
为了方便后面讲，我先把本文会用到的几张简化表定义出来。注意，字段和索引都是从真实项目里抽取出来的简化版本，重在讲逻辑。
CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), city VARCHAR(50), status INT ); CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, total_amount DECIMAL(10,2), status VARCHAR(20), created_at DATETIME, KEY idx_user_id (user_id) ); CREATE TABLE products ( id INT PRIMARY KEY, product_name VARCHAR(100), category VARCHAR(50), price DECIMAL(10,2) );有了这些表，后面的所有案例都能直接跑起来。你可以理解为：users是用户主表，orders是订单流水表，products是商品表。实际项目里这些表可能会拆得更细，但子查询的写法和执行逻辑不会变。
1.2 真实业务里哪些场景必须用子查询
很多人问我，子查询到底能不能不用？答案是：能，很多子查询确实可以改写成 JOIN。但有些场景，子查询反而是最贴近业务表达、最不容易写错的方案。
我总结了几类“更适合用子查询”的情况：
- 需要用聚合函数的结果去做比较，比如“找出价格高于全站平均价的商品”。
- 需要先算出一个临时集合，再把这个集合当成一张表继续参与查询，这时候可以用 FROM 子查询。
- 需要判断“是否至少存在一条符合条件的记录”，EXISTS 子查询非常合适。
- 需要在 SELECT 列表里直接输出某个关联统计值，比如每个用户的下单次数。
子查询不是万能的，但它是 SQL 表达能力的重要拼图。你不需要刻意制造嵌套，但遇到上面的场景时，应该条件反射般想到“这里可以用子查询”。
2. 子查询的分类，先把骨架搭起来再谈细节
子查询如果只靠死记硬背，很容易混乱。但如果你先按“返回什么结果”和“跟外层有没有关系”两个维度分类，整个骨架就清楚了。
2.1 按返回结果分：标量、行、表
第一类，标量子查询。它返回的结果只有一个值，也就是一行一列。比如下面这条语句：
SELECT product_name, price FROM products WHERE price > ( SELECT AVG(price) FROM products );AVG(price)返回的是一个数值，这个子查询就是标量子查询。写了标量子查询之后，你不能再指望它返回多行，否则外层使用=比较时会直接报错。
第二类，行子查询。它返回一行，但可以有多个列。比如：
SELECT * FROM employees WHERE (salary, department_id) = ( SELECT salary, department_id FROM employees WHERE id = 1001 );这在 MySQL 里完全合法，括号里用一个“行构造器”和子查询返回的一整行做比较。实际项目里用到的频率不算高，但遇到这种写法时你要能看懂。
第三类，表子查询，也叫派生表。它返回一个多行多列的结果集，通常放在 FROM 后面，被当成一张临时表使用。比如：
SELECT u.name, t.order_cnt FROM users u JOIN ( SELECT user_id, COUNT(*) AS order_cnt FROM orders WHERE status = 'paid' GROUP BY user_id ) t ON u.id = t.user_id WHERE t.order_cnt > 3;这里的括号部分就是表子查询，外层把它当作一张临时表t去 JOIN。注意，MySQL 要求这种派生表必须有别名，所以你不能省略t。
2.2 按内外层依赖分：非相关子查询和相关子查询
这是整个子查询体系里最重要、也最影响性能的一个分类。
非相关子查询，也叫独立子查询。它内部不引用外层查询的任何字段，可以先单独执行一次，得到结果之后外层再继续处理。刚才那个WHERE price > (SELECT AVG(price) FROM products)就是典型例子，内层先算平均价，外层拿这个固定值去做比较。
相关子查询则完全相反。它的内层语句里引用了外层表的字段，所以外层每处理一行，内层都可能要重新执行一次。看这个例子：
SELECT u.name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_cnt FROM users u;内层的o.user_id = u.id用到了外层users表的u.id，这两个查询是绑在一起的。理论上，外层有多少个用户，内层就可能跑多少次。这个概念我在后面讲执行代价和优化时还会重点提，大家可以先记住：相关子查询的灵活度高，但性能风险也高。
2.3 按出现位置分：SELECT、FROM、WHERE、HAVING、ORDER BY
子查询可以出现在 SQL 的很多位置，而不同位置的写法约束和优化逻辑并不完全一样。
- 在 SELECT 列表里：一般返回标量，作为额外输出的一列。
- 在 FROM 子句里：作为派生表，必须有别名。
- 在 WHERE 子句里：可以用在
IN、EXISTS、比较运算符、ANY、ALL等场景。 - 在 HAVING 子句里：用于整体分组后的过滤条件，比如过滤出“平均分高于整体平均分”的分组。
- 在 ORDER BY 子句里：可以用子查询的结果作为排序依据。
把这些位置都试一遍之后，你会发现子查询其实是一套很灵活的“嵌套逻辑”。写的时候最关键的是先想清楚：我想要的是一个值、一列、还是一张表？想清楚这个，位置才不会放错。
3. 高频实战场景拆解
理论讲再多，不落地都是空的。我直接把日常开发里出现频率最高的七类子查询场景一个个拆开讲，每段都配有能直接抄走的 SQL。
3.1 WHERE 后接标量子查询：找出超过平均线的数据
最常用、也最基础的一个场景，是先算出一个聚合值，再用它去过滤外层数据。假设我想找出所有价格高于全站平均价格的商品：
SELECT product_name, price FROM products WHERE price > ( SELECT AVG(price) FROM products );这条语句的执行顺序非常直观：先执行括号里的SELECT AVG(price) FROM products，得到比如 299，然后再执行外层的WHERE price > 299。这种写法比先查平均价、再写第二条 SQL 要简单，而且能保证两条语句之间平均价不会因为中间数据变动而产生偏差。
需要注意的是，标量子查询必须保证只返回一行一列。如果子查询返回多行，外层用>、=这类运算符就会直接报错。所以写标量子查询时，要确认聚合函数或者主键筛选能让它退化成单值。
3.2 FROM 子查询：把聚合结果当成一张临时表
有些需求是：先做一次聚合，得到一个新的统计表，然后再和其他表关联。这种情况最适合 FROM 子查询。比如我要找出“有效订单超过 3 个的用户及下单数”：
SELECT u.name, t.order_cnt FROM users u INNER JOIN ( SELECT user_id, COUNT(*) AS order_cnt FROM orders WHERE status = 'paid' GROUP BY user_id ) t ON u.id = t.user_id WHERE t.order_cnt >= 3;这里括号里的子查询先按user_id分组，算出每个用户的订单数，再把结果当成临时表t，和users表 JOIN。外层可以直接使用t.order_cnt这个别名，逻辑非常清晰。
这个场景最容易踩的坑是忘记给派生表加别名。MySQL 对派生表强制要求别名，不写就会直接报语法错误。另外一个容易被忽略的点是，子查询内部的 ORDER BY 如果外层没有配合 LIMIT 使用，很多情况下会被优化器忽略掉，这一点后面专题讲。
3.3 IN / NOT IN 集合判断：最常见的过滤写法
这里先展示最常见的 IN 子查询。比如查“所有上海用户下的订单”：
SELECT * FROM orders WHERE user_id IN ( SELECT id FROM users WHERE city = '上海' );这条 SQL 的意思是：先把子查询里的用户 id 集合查出来，再拿orders.user_id去判断是否在这个集合里。
IN 子查询写起来很舒服，但有一点要特别注意：子查询结果集中不能有 NULL，否则在 NOT IN 的场景下会出大问题。我专门在后面的“避坑指南”里讲，这里先不展开。通常遇到“排除某些用户”，也就是 NOT IN 的时候，我更推荐改成 NOT EXISTS，安全系数更高。
3.4 EXISTS / NOT EXISTS：存在性判断的最优解之一
EXISTS 是专门用来做“存在性判断”的。比如我要找“下过单且订单金额超过 1000 的用户”：
SELECT id, name FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total_amount > 1000 );这段 SQL 的逻辑是：每取一个用户，就去 orders 表里查有没有满足条件的记录，只要查到一条，就立刻返回真，不再继续往下扫。所以 EXISTS 更关心“有没有”，不关心查到的是什么，这也是为什么子查询里很多人喜欢写SELECT 1而不是SELECT *。
在实际执行时，EXISTS 子查询往往是相关子查询，因为内层用了外层表的u.id。但 MySQL 优化器有可能会把一些 EXISTS 改写成 semi-join 来提升效率，所以不是说相关子查询就一定慢，而是要看执行计划。
3.5 ANY / ALL：跟一套值做多行比较
ANY 和 ALL 很容易混淆，我教给大家一个记忆方法：ANY相当于“至少其中一个”，ALL相当于“所有里面都满足”。
假设我要找出“价格比任何一款手机都便宜的平板电脑”：
SELECT * FROM products p WHERE p.category = '平板' AND p.price < ANY ( SELECT price FROM products WHERE category = '手机' );< ANY表示只要比子查询返回的任意一个手机价格低，就算满足。如果手机价格是 3999、4999、5999，那么平板价格只要小于任意一个，比如低于 5999 就会被选中。
如果我反过来，要找“比所有手机都贵的平板”，就要用> ALL：
SELECT * FROM products p WHERE p.category = '平板' AND p.price > ALL ( SELECT price FROM products WHERE category = '手机' );如果手机价格最高是 5999，那么这个平板必须比 5999 高。ANY和ALL在业务里不算高频，但一旦遇到“跟一组动态值比较”的条件，它们能省很多事情。
3.6 SELECT 列表中的子查询：边上查边补列
有时候我希望在主查询的结果里直接带出一个统计值，比如“查询用户信息的同时列出每位用户的订单数”：
SELECT u.id, u.name, ( SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id ) AS order_cnt FROM users u;这个写法的好处是非常直白，不需要提前聚合。但代价也很明显：它通常是一个相关子查询，外层每走一个用户，内层都要执行一次 COUNT。如果 users 表只有几十行，没问题；如果 users 表有几十万行，最好提前用 GROUP BY 聚合后再 LEFT JOIN，而不是直接在 SELECT 列表里放子查询。
3.7 多级嵌套：子查询里套子查询
子查询可以嵌套好几层，比如先查出满足条件的一组用户，再在这组用户里继续筛，最后查订单：
SELECT * FROM orders WHERE user_id IN ( SELECT user_id FROM users WHERE id IN ( SELECT user_id FROM user_vip_level WHERE level >= 5 ) );这里最内层先查高等级用户，中间层根据结果回来筛用户表，最外层再查订单。多级嵌套本身不复杂，但可读性和性能都会随着层级变深而下降。真实项目里如果遇到三层以上的嵌套，我通常会先停下来想想：是不是拆成多个步骤或者用 JOIN 改写会更合适。
4. 执行顺序和执行计划：别让直觉蒙骗你
很多人学子查询时被告知“先执行内层，再执行外层”，这句话在简单场景下成立，但在 MySQL 的真实执行过程中并不总是如此。这不是语法问题，而是优化器可能会把子查询改写成其他执行方式。
4.1 先内后外？多数情况成立，但别忽略优化器
对于非相关子查询，比如WHERE price > (SELECT AVG(price) FROM products)，优化器通常会先把内层算出来，然后执行外层。但对于IN子查询，MySQL 可能会使用 semi-join 优化，把它执行成类似 JOIN 的流程，而不是傻乎乎地先物化一个完整的临时集合再外层扫描。
再比如 EXISTS 相关子查询，MySQL 也可能尝试半连接优化。所以在不查看执行计划的情况下，你靠“先内后外”的直觉去推测性能，很容易翻车。我见过有人看见相关子查询就一口咬定“一定慢”，结果 EXPLAIN 出来之后发现优化器改写得非常好，根本不存在逐行全表扫的问题。
所以记住一句经验：先按逻辑理解子查询，再用 EXPLAIN 验证真实的执行路径，两头都不能丢。
4.2 用 EXPLAIN 看穿子查询
MySQL 的EXPLAIN是拆解子查询执行过程的利器。你只需要在原来的 SQL 前面加上 EXPLAIN：
EXPLAIN SELECT id, name FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total_amount > 1000 );你会在结果里看到这些关键字段：
- select_type：代表这个查询是普通查询、子查询、还是依赖外层的相关子查询。比较常见的值有 SIMPLE、PRIMARY、SUBQUERY、DEPENDENT SUBQUERY、DERIVED、MATERIALIZED。
- table：这一步在访问哪张表。
- type：访问类型，从好到差大致是 system、const、eq_ref、ref、range、index、ALL。
- possible_keys 和 key：优化器可能使用和实际使用的索引。
- rows：预估扫描的行数。
- Extra：额外信息，比如 Using where、Using index、Using filesort 等。
如果你在select_type里看到DEPENDENT SUBQUERY，就意味着这是相关子查询，内层要依赖外层传入的值去执行。这种情况下，rows 预估值会非常直观地告诉你，简单的一条 SQL 背后可能扫描了多少行数据。
4.3 相关子查询为什么比非相关子查询更危险
同样逻辑下，非相关子查询可能只执行一次，相关子查询却可能执行 N 次。外层有 1 万行，内层每次扫描 1 万行，理论代价就是 1 亿行级别的扫描，这个量级对很多线上业务来说直接就是灾难。
当然，不是所有相关子查询都一定慢。如果内层的关联字段有索引，比如orders.user_id有索引，每次相关子查询通过索引去查找，效率会高很多。但我还是建议大家形成条件反射：看到相关子查询，先想想外层数据量有多大，再想想能不能改写。
5. 性能优化与改写技巧
子查询能不能写出好性能，关键不在于“尽量不用子查询”，而在于“知道什么时候用、怎么用，以及怎么帮优化器拿到合适的执行计划”。
5.1 索引放在哪些字段上
子查询的索引选择和普通查询没有本质区别，核心原则是：让 WHERE 条件和关联条件能通过索引快速圈定范围。
以IN (SELECT user_id FROM users WHERE city = '上海')为例，内层users.city如果有索引，内层可以快速筛选；外层orders.user_id如果有索引，外层也能快速匹配。对于相关子查询，比如WHERE o.user_id = u.id，orders.user_id上的索引尤其重要。
我给同事做 SQL 优化时，最常做的一件事就是检查 EXPLAIN 里的 key 字段。如果 key 是 NULL，同时 type 是 ALL，说明这个子查询在走全表扫描，这时候不管怎么写 SQL，性能上限都很低。
5.2 相关子查询改写为 JOIN
“把相关子查询改成 JOIN”是我用得最多的一招。比如这个 EXISTS 写法：
SELECT DISTINCT u.* FROM users u JOIN orders o ON o.user_id = u.id AND o.total_amount > 1000;逻辑上它和前面“下过单且订单金额超过 1000 的用户”是一致的。JOIN 写法把相关计算变成了普通的连接操作，优化器处理 JOIN 的经验更丰富，执行计划通常更稳定。
但这里有个前置条件：如果orders和users之间是一对多关系，JOIN 会导致外层记录重复，所以需要使用DISTINCT或者GROUP BY去重。DISTINCT 本身也有成本，所以不能无脑说 JOIN 一定比 EXISTS 好。我的习惯是：
- 如果只是判断“存在”，我会先试 EXISTS，再看 EXPLAIN。
- 如果优化器把 EXISTS 处理成普通的外层扫描加相关子查询，我才会考虑改成 JOIN。
- 如果还需要统计数量、汇总字段，直接 JOIN 加 GROUP BY 通常更自然。
5.3 派生表合并与物化
在 MySQL 5.7 和 8.0 中，派生表有两种处理方式：合并和物化。合并是指 MySQL 把 FROM 子查询展开，直接和外层查询合并成一条执行计划；物化是指 MySQL 先把子查询执行结果落到一个内部临时表，再去参与外部计算。
什么时候物化？通常当子查询内部有聚合、DISTINCT、LIMIT 等不容易合并的操作时，MySQL 会选择物化。物化本身并不一定慢，但如果物化出来的临时表没有有效索引，外部访问它时仍然可能扫描大量数据。
给一个实用的建议：如果你需要“先排序后分页”的效果，比如从子查询里取最近 10 条记录再和其他表关联，必须保证子查询内部有 ORDER BY 和 LIMIT。否则 MySQL 优化器很可能因为合并而忽略排序，最后结果完全不符合预期。
5.4 优化器不买账时怎么办
有时候你精心写完的子查询，EXPLAIN 一看还是全表扫描。这时候我一般按这个顺序排查：
- 先看 SELECT 列和 WHERE 条件是否对上了索引，优先给关联字段补索引。
- 再看子查询结果集的大小。如果内层返回的数据量很小，我可能会直接把子查询拆出来，在应用层先查一次小结果集，再拼进第二条 SQL。这个方法不够“优雅”，但在紧急优化场景里非常可靠。
- 最后才考虑各种优化器开关和 hint。线上环境不建议为了单条 SQL 乱调全局参数。
记住：SQL 优化的第一原则是“减少数据扫描范围”，别只盯着语法层面改来改去。
6. 我踩过的坑，整理成一份避坑指南
前面讲了很多正确做法，这一节全是实打实的翻车记录。以下每一个问题，我都在真实项目里见过，而且不止一次。
6.1 标量子查询返回多行
有次同事写了一条类似下面的 SQL：
SELECT * FROM users WHERE id = ( SELECT user_id FROM orders WHERE total_amount > 100 );表面看起来没什么问题，但total_amount > 100的订单不止一条，子查询返回了多行，外层用=去比较就直接报错：Subquery returns more than 1 row。
解决方式很简单：如果业务上确实想匹配多个用户，就把=改成IN；如果只想要其中一条，子查询里要加上明确的排序和LIMIT 1。这个错误是子查询新手几乎必踩的一关。
6.2 NOT IN 遇到 NULL，悄悄吞掉所有结果
这个坑非常隐蔽。假设我要查“从没下过单的用户”：
SELECT * FROM users WHERE id NOT IN ( SELECT user_id FROM orders );如果orders.user_id这一列里有 NULL，结果会变成 0 行。原因是 SQL 的三值逻辑：id NOT IN (1, 2, NULL)在逻辑上等价于id != 1 AND id != 2 AND id != NULL，而id != NULL的结果是 NULL，最终整个条件不为真，所有行都被过滤掉。
这种场景我强烈建议写成 NOT EXISTS：
SELECT * FROM users u WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id );如果必须用 NOT IN，就先把 NULL 排除掉，比如WHERE user_id IS NOT NULL。但习惯了用 NOT EXISTS 之后，你会省掉很多莫名其妙的排查时间。
6.3 ORDER BY 在子查询里没生效
有人在 FROM 子查询里写了 ORDER BY，以为外层能按这个顺序输出，结果发现乱序。比如：
SELECT * FROM ( SELECT id, product_name, price FROM products WHERE category = '平板' ORDER BY price DESC ) t LIMIT 10;如果子查询里没有 LIMIT，MySQL 优化器很可能在合并派生表时直接忽略内层 ORDER BY。只有在子查询里同时使用ORDER BY ... LIMIT n，排序才更有可能保留下来。
所以，当你想“先排序后取前几条再关联”时，一定要把 LIMIT 写进子查询里，否则就是白排。
6.4 别让相关子查询在 SELECT 列表里跑全表
我见过最典型的一条慢 SQL，就是把 3.6 节那种写法直接用在百万级用户表上：
SELECT u.id, u.name, ( SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id ) AS order_cnt FROM users u;外层扫一百万个用户，内层每次再去扫订单表，哪怕每条记录都有索引，总成本也非常可观。正确处理思路是先用 GROUP BY 把订单聚合好，再通过 JOIN 带出结果：
SELECT u.id, u.name, COALESCE(t.order_cnt, 0) AS order_cnt FROM users u LEFT JOIN ( SELECT user_id, COUNT(*) AS order_cnt FROM orders GROUP BY user_id ) t ON u.id = t.user_id;两条 SQL 的业务结果一致，但执行效率可以差出好几个数量级。相关子查询写在 SELECT 列表里，一定要先量一量外层数据量。
6.5 子查询不是越嵌套越高级，能减则减
最后分享一个经验：子查询虽然强大，但真的不是嵌套得越多越厉害。我见过一段五层嵌套的统计查询，写法看起来确实“很炫”，但实际执行时优化器把它拆成了一堆相关子查询，线上跑一次要好几秒。
后来我把它拆成两步，先物化中间结果到临时表，再用 JOIN 串起来，执行时间从秒级降到了毫秒级。子查询的价值应该体现在“让逻辑更清晰”，而不是“让结构更复杂”。当你发现一段子查询已经需要拿纸笔逐层分析时，就该考虑改写了。
每次写完子查询，我都会顺手加一发 EXPLAIN，确认一下优化器是不是真的按照我预想的方式在执行。这个习惯帮我避掉了很多性能坑，也希望你能带进自己的日常开发里。
