1. 子查询为什么让人又爱又怕：先看一个真实翻车现场

先说结论：子查询是所有SQL学习者绕不过去的一道坎，也是我在带团队和做面试评审时最常拿来试探候选人基本功的话题。它确实不难，但几乎每个人都踩过它的坑——要么写出逻辑正确但慢到离谱的查询，要么遇到NULL值导致结果悄悄少了几行还不自知。

我印象很深的一次，是在某电商订单系统里排查一个统计报表。运营同事反馈"近30天买过A类商品但没买过B类商品的用户数"这个数字不对，少了一大截。我拉出SQL一看，开头就是典型的NOT IN子查询写法：

SELECT COUNT(DISTINCT user_id) FROM orders WHERE product_category = 'A' AND user_id NOT IN ( SELECT user_id FROM orders WHERE product_category = 'B' );

逻辑看起来天衣无缝，但结果就是少了。问题出在orders表里的user_id有NULL。NOT IN遇上NULL，整个条件会变成"非真即假"，所有行都被过滤掉。这类问题如果不把子查询的语义彻底吃透，排查一整天都很正常。

所以这篇文章我打算把子查询讲透：它是什么、该放在SQL的哪些位置、三种子查询的执行逻辑差异在哪、最容易翻车的坑有哪些、什么时候该把它改写掉。目标读者是已经有基础SQL功底、想进阶到中级水平的人；如果你刚学完SELECT、JOIN、GROUP BY，这篇能帮你把"子查询"这根筋彻底打通。子查询本质上就是把一个查询的结果当作另一个查询的输入，理解这一点，比背一万个语法公式都管用。

2. 子查询的三个藏身之处：WHERE、FROM、SELECT各管什么事

2.1 WHERE中的子查询：把一段查询当成筛子

子查询最常见的去处就是WHERE子句。它的核心逻辑是：内层查询先执行，产出一个值或一批值，外层查询拿这些值和表中的列做比较，从而筛选出目标行。

按内层查询产出值的数量，可以分成两类：

• 单行子查询：返回一行一列，通常配合=、>、<、>=、<=、<>这些比较运算符使用。
• 多行子查询：返回一列多行，通常配合IN、ANY、ALL使用。

举个例子，查"订单金额高于全站平均订单金额"的所有订单：

SELECT order_id, user_id, total_amount FROM orders WHERE total_amount > ( SELECT AVG(total_amount) FROM orders );

先算平均金额，再拿每笔订单的外层行去跟这个平均值比较。内层子查询和外层查询之间没有数据依赖，它先执行一次，执行完就完事了。这种子查询叫非相关子查询。

多行版本配合IN的用法更常见，比如"查所有下过单的用户资料"：

SELECT user_id, nickname FROM users WHERE user_id IN ( SELECT DISTINCT user_id FROM orders );

这里有个容易混淆的点：很多人以为IN后面必须接子查询才是高级用法，其实IN后面直接接一个值列表，比如IN (1, 2, 3)，本质上就是一个没有SELECT的"行集合"。理解了这一点，IN子查询就不难了——它只是把这个值列表的生成方式从"手写"变成了"查出来"。

2.2 FROM中的派生表：把查询结果当成临时表来用

子查询出现在FROM后面时，它不叫子查询了，正式名称叫派生表。它的作用是：先把内层查询的结果当成一张"临时表"，然后外层查询从这张临时表里继续做筛选、聚合、关联。

为什么需要派生表？因为SQL对WHERE子句有一个限制——不能在WHERE里直接使用聚合函数的结果做过滤。比如你想查"订单数超过5单的用户"，你没法直接写WHERE COUNT(*) > 5，因为聚合发生在分组之后，而WHERE在分组之前执行。标准做法是HAVING，但在某些更复杂的场景里，HAVING也不够用，这时派生表就上场了。

拿"查每个品类下单数排名前2的品类"来演示（这里先不引入窗口函数）：

SELECT category, order_cnt FROM ( SELECT product_category AS category, COUNT(*) AS order_cnt FROM orders GROUP BY product_category ) AS t WHERE order_cnt > 100 ORDER BY order_cnt DESC;

注意两个细节。第一，派生表必须起别名，上面案例里的AS t就是干这个的，不写会直接报错。第二，派生表里列的别名会被外层查询引用，所以在内层有命名冲突时，要提前把列改好名。

派生表的执行逻辑有个关键点：多数数据库优化器会先把整张派生表物化（即实际执行内层查询并缓存结果），再往外层供数。物化意味着内层查询的结果是快照，外层无论你怎么查，它都不会重新执行内层。这在数据量大时是双刃剑——好处是外层可以反复引用而不重复计算，坏处是物化本身有内存开销，如果派生表的结果集很大，反而会拖慢查询。

2.3 SELECT中的标量子查询：逐行附加上下文信息

第三种位置是SELECT子句后面，它必须返回一行一列的单一值，否则会直接报错。它的作用相当于给每一行查询结果动态附加一个"计算出来的列"。

比如我要查订单列表，同时显示每个订单对应的用户昵称。如果用户和订单在不同的表里，最常规的做法是JOIN。但有时候关联条件很别扭，或者你只是想查一下某个辅助信息，不想引入完整的多表连接，就可以用标量子查询：

SELECT order_id, user_id, total_amount, (SELECT nickname FROM users u WHERE u.user_id = o.user_id) AS user_nickname FROM orders o;

外层每处理一行订单，就执行一次内层查询，拿这个订单的user_id去传参，查出对应的昵称。这就是典型的相关子查询——内层表和外层表之间有列引用关系，内层必须依赖外层的当前行才能执行。

它的执行次数是"外层行数 + 1"。外层有1000条订单，这个标量子查询就可能执行1000次。听起来很可怕，但在实际优化器的执行计划里，某些情况会被改写成LEFT JOIN的方式来做，性能不至于崩。真正危险的是在小表驱动大表的场景中，这个"逐行执行"的逻辑会无限放大成本。所以我的原则是：SELECT里的标量子查询只用来取极少数辅助字段，一旦超过两三个，老老实实改成JOIN，可读性和性能都好很多。

3. 相关子查询与非相关子查询：执行顺序完全相反

3.1 非相关子查询：先算一次，用完就扔

非相关子查询是最好理解的一种。它的特点是：内层查询不依赖外层任何列，SQL引擎可以先把内层完整执行一遍，得到一张结果集，再把这个结果集当作常量供外层使用。执行顺序是固定且唯一的——内层先跑，外层后跑。

它的好处是稳定、易优化。数据库优化器对这种子查询几乎都能做提前计算（也叫"常量折叠"），甚至可以把结果缓存起来反复用。写法和执行逻辑也很好理解，上面的平均订单金额案例就是典型。

但要注意，非相关子查询如果返回的结果集特别大，比如IN里带了一个几十万行的子查询，那么这个"先用一次"的常量集合就会非常大，导致外层匹配时成本飙升。此时优化器可能会自动把它物化成临时表并建索引，也可能选择直通地逐行扫描，取决于表结构和统计信息。这就是为什么我后面会专门讲"子查询性能"——不是所有子查询都该用，也不是所有子查询都该改。

3.2 相关子查询：外层驱动内层的"嵌套循环"

相关子查询的执行模型更接近编程语言里的嵌套循环：外层取一行，传给内层，内层执行完，返回一个值或一个布尔结果，外层根据结果决定这一行保不保留。接着外层取下一行，再传给内层……

拿"查每个用户最近一笔订单时间超过30天的用户"来举例（简化版，只取时间维度）：

SELECT user_id, order_id, order_date FROM orders o1 WHERE order_date = ( SELECT MAX(order_date) FROM orders o2 WHERE o2.user_id = o1.user_id );

对orders o1的每一行，SQL都要运行一次内层的MAX(order_date)查找。内层查询中用到了外层别名o1.user_id，这个引用就是"相关"二字的来源。如果orders表有10万行，那内层可能被触发10万次——虽然实际优化器会想办法缓存部分结果，但这个模型的天花板确实很低。

理解这个执行模型有什么用？非常大。它能帮你判断一条SQL为什么慢：看到执行计划里出现DEPENDENT SUBQUERY，就意味着走了相关子查询路径，你就该重点检查外层驱动表的数据量。如果外层很小、内层有索引，那这种写法还挺高效；一旦外层大内层又没有索引，基本必死无疑。

3.3 EXISTS的语义和IN不一样：它是"有木有"而不是"在不在"

EXISTS后面接的子查询，返回的不是数据，而是一个布尔值：只要子查询返回至少一行，结果就是TRUE；一行都不返回就是FALSE。它不关心子查询具体查出什么列，你写SELECT 1也好，写SELECT *也好，逻辑完全一样。

它和IN最大的区别在于处理NULL的语义。前面说过，IN的子查询结果集里如果包含NULL，可能会引发三值逻辑问题。但EXISTS完全不碰这个麻烦——它只判断子查询是否有行产出，NULL对它来说就是一个普通值，完全不影响判断。

-- 找下单过的用户 SELECT user_id, nickname FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.user_id );

这个写法等价于"关联存在性判断"。很多有经验的开发者会无条件推荐用EXISTS替代IN，这个观点有点极端——在数据量小、且结果集不涉及NULL时，IN和EXISTS的性能差距不大，现代优化器甚至能把IN改写成半连接来执行。但当你吃不准子查询结果集里有没有NULL，尤其后续还要加NOT反向判断的时候，EXISTS确实更安全。

4. 子查询最危险的三个坑：NULL、空集合、返回多行

4.1 NULL参与比较：三值逻辑让条件悄悄失效

SQL里有一个核心概念叫三值逻辑：任何比较运算的结果不只是TRUE和FALSE，还有第三种状态UNKNOWN。NULL参与任何运算，得出的结果几乎都是UNKNOWN。而WHERE子句只保留结果为TRUE的行，FALSE和UNKNOWN都会被过滤掉。

这个逻辑听起来简单，但实际影响面极大。最常见的就是前面说的NOT IN场景。你可能会觉得"我的用户ID是主键，不可能有NULL"，但子查询的结果集往往不是主键列，而是业务列，甚至可能是多表关联后的结果，NULL难免混进来。一旦子查询结果集里包含NULL，NOT IN的语义就会退化成"永远为UNKNOWN"，整条SQL返回空结果。

拿最开始的案例来复盘：

SELECT user_id FROM users WHERE user_id NOT IN ( SELECT user_id FROM orders WHERE product_category = 'B' );

如果orders表中存在product_category = 'B'且user_id为NULL的记录，那么这个子查询会返回一个包含NULL的集合。MySQL对NOT IN的等价改写是user_id != 值1 AND user_id != 值2 ... AND user_id != NULL，最后那个!= NULL的结果是UNKNOWN，整个AND链的最终结果必然是UNKNOWN，于是所有行都被过滤。

解决办法有三个：一是给子查询结果去掉NULL，比如WHERE user_id IS NOT NULL；二是改用NOT EXISTS；三是用LEFT JOIN加IS NULL判断。个人最推荐第三种，因为它语义最直观，而且性能通常也更好：

SELECT u.user_id FROM users u LEFT JOIN ( SELECT DISTINCT user_id FROM orders WHERE product_category = 'B' ) b ON b.user_id = u.user_id WHERE b.user_id IS NULL;

4.2 空集合的问题：IN查询永远不等于"空"

另一个和NULL容易混淆的坑是子查询结果为空。很多人以为"子查询查不到数据时，IN会怎样"，直觉上会认为"应该跟查任何数据都匹配不上"——对，这才是正确直觉。IN一个空集合等价于一个空列表，外层条件永远为FALSE，结果集为空，这本身不会"翻车"。

翻车的是反过来写的情况。比如WHERE user_id = (SELECT ...)，当单行子查询返回空结果时，SQL会把它当作NULL，然后user_id = NULL的结果就是UNKNOWN，一样不会报错但也不会返回任何行。这时候最坑的地方是：不报错。你写了一整条逻辑严密的SQL，结果返回空表，日志里啥也看不出来，只有你拿着业务数据对照才知道是子查询没查到数据。

所以我的建议是：凡是涉及标量子查询的场景，都主动用COALESCE或IFNULL给它一个默认值兜底，别把"查不到"和"值本身是NULL"混在一起。比如：

SELECT order_id, COALESCE( (SELECT MAX(paid_amount) FROM payments p WHERE p.order_id = o.order_id), 0 ) AS last_paid_amount FROM orders o;

这样即使没有支付记录，结果显示的也是0，而不是NULL，后续业务流程里就不用再做额外的空值判断。

4.3 标量子查询返回多行：最常见的运行时错误

标量子查询的硬性要求是"最多返回一行一列"。如果内层查询返回了两行以上，所有主流数据库都会直接抛错。MySQL的报错信息是Subquery returns more than 1 row，错误码1242。

这种情况经常发生在你从"单行子查询"改写成"聚合子查询"时。比如想取每个订单的最大支付金额，写成：

SELECT order_id, (SELECT paid_amount FROM payments WHERE order_id = o.order_id) AS paid FROM orders o;

如果一个订单有多条支付记录，这个子查询就会炸。正确的做法是加MAX()：

SELECT order_id, (SELECT MAX(paid_amount) FROM payments WHERE order_id = o.order_id) AS paid FROM orders o;

顺带提一句，这类"每个订单的最大支付金额"用窗口函数写会更优雅，后面第6章会展开。这里想强调的是：标量子查询返回多行是语法级错误，不是逻辑级错误，你没法靠优化器兜底，只能在写SQL时就盯紧关联列的约束条件，确认内层最多只有一行匹配。

5. 子查询性能焦虑怎么破：什么时候该改写成JOIN

5.1 执行计划里的三个坏味道

子查询不是不能用，而是必须用执行计划来判断它到底怎么跑。我的习惯是，凡是子查询写完后，先跑一条EXPLAIN看看计划。如果出现以下几种情况，就该警惕了：

• DEPENDENT SUBQUERY：说明内层查询对外层行有依赖，大概率在走嵌套循环式的逐行执行，外层行数越多越危险。
• MATERIALIZATION（物化）：优化器选择把子查询结果物化成临时表。如果物化结果又大又没有索引，后续访问就变成大临时表的全表扫描。
• UNCACHEABLE SUBQUERY：子查询无法被缓存，每次都要重新执行。出现这种一般是因为子查询里包含非确定性的函数，或者外层每行传入的值会导致内层完全不同的结果。

我个人会在执行计划里优先看Key列有没有用到索引，以及rows估算的扫描行数。如果子查询相关的步骤里rows出现几十万行甚至上百万行，且没有走索引，那不管它逻辑多完美，上线前都得优化。

5.2 IN、EXISTS、JOIN不是等价替换，先搞懂半连接再说

很多文章会把IN、EXISTS、JOIN说成可以互相替换的等价写法，这个说法不精确。它们在"逻辑结果"上很多时候确实等价，但在执行路径上可能完全不同。

MySQL等现代数据库对IN和EXISTS都提供了半连接优化：半连接的意思是，子查询返回的匹配行只用来判断外层行是否保留，不会像普通JOIN那样把重复的匹配行都取出来。所以SELECT ... FROM users WHERE user_id IN (SELECT user_id FROM orders)，即使同一个用户下了100个订单，最终也只会保留一条用户记录，不会膨胀。

理解了半连接，你再看EXISTS和JOIN的性能差异就能跳出口水仗：如果优化器把IN改写成了半连接，那它和EXISTS的差距几乎没有。真正有差距的场景是：

• 子查询结果集特别大时，EXISTS因为只关心"存在性"，可能提前终止扫描（发现第一条匹配就返回TRUE），而IN要先把整个结果集算完才能比较。这一点在理论上对EXISTS有利。
• JOIN如果写不好，比如忘了DISTINCT或关联字段没索引，可能出现结果膨胀的风险，而子查询的这些副作用相对可控。

所以我的选择逻辑很简单：可读性优先。先写清晰准确的子查询版本，如果性能不达标，再翻执行计划、做改写。不要一上来就画蛇添足。

5.3 一个完整优化案例：4500ms到38ms是怎么做到的

这里分享一个真实改写过案例，表和数据都是常见的订单场景。当时某后台管理页面要查"最近7天有下单但从未申请过售后的用户"，原始SQL长这样：

SELECT u.user_id, u.nickname FROM users u WHERE u.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) AND u.user_id IN ( SELECT DISTINCT o.user_id FROM orders o WHERE o.paid_at IS NOT NULL ) AND u.user_id NOT IN ( SELECT DISTINCT r.user_id FROM refunds r WHERE r.created_at >= '2023-01-01' );

users表不到5万行，orders表接近200万行，refunds表也有80万行。这个SQL在MySQL 5.7上跑了4.5秒，页面直接超时。执行计划显示两个子查询路径都走了DEPENDENT SUBQUERY，每个外层用户都要去200万行订单表和80万行退款表里扫一遍，逻辑上等价于5万次大查询，不慢才怪。

改写思路核心是三条：

1. 用JOIN替代第一个IN子查询，让订单表的关联走索引。
2. 用NOT EXISTS替代第二个NOT IN，彻底规避NULL问题。
3. 给refunds的user_id建索引，提前缩小退款表参与比对的范围。

改写后的SQL：

SELECT u.user_id, u.nickname FROM users u INNER JOIN ( SELECT DISTINCT o.user_id FROM orders o WHERE o.paid_at IS NOT NULL AND o.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) ) oa ON oa.user_id = u.user_id WHERE u.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOT EXISTS ( SELECT 1 FROM refunds r WHERE r.user_id = u.user_id AND r.created_at >= '2023-01-01' );

改写后执行时间直接降到38毫秒。这里的关键不是说NOT IN就是垃圾，而是在大数据集+无索引+关联列可空的情况下，JOIN和NOT EXISTS能让优化器拿到更清晰的统计信息和索引路径。改写前建议先确认refunds.user_id的NULL比率，如果是0%，NOT IN也能用；但只要有一丁点NULL可能，NOT EXISTS就是更稳的选择。

这个案例的教训可以说很经典：子查询的性能问题往往不是子查询本身造成的，而是查询里藏着的全表扫描和数据膨胀被层层放大了。换句话说，你排查的目标不是"去掉子查询"，而是"让每一步操作都走索引"。

6. 子查询的进阶变形：嵌套、CTE、窗口函数的取舍

6.1 多层嵌套子查询：先跑通，再拆解

子查询里面还可以再嵌套子查询，技术上没有任何障碍，但可读性会急速下降。拿"查每个商品品类中销售数量最高的商品名称"举例，用纯嵌套写法会长成这个鬼样子：

SELECT category, product_name FROM ( SELECT pc.category_name AS category, p.product_name, ROW_NUMBER() OVER (PARTITION BY pc.category_id ORDER BY SUM(oi.quantity) DESC) AS rn FROM product_categories pc JOIN products p ON p.category_id = pc.category_id JOIN order_items oi ON oi.product_id = p.product_id GROUP BY pc.category_id, pc.category_name, p.product_name ) t WHERE rn = 1;

这个场景纯靠子查询叠子查询会非常难写，我见过的很多新人会写出四层嵌套的版本，中间还混着一堆GROUP BY和HAVING。最终跑出来可能对，但你和同事根本没法维护。

我的建议是：复杂逻辑写完后，立刻做"可读性重构"，不要等它烂在代码里。重构不只是给代码加注释，更多是调整结构，把内层的聚合逻辑拿出来命名清楚。下面这个WITH语法就是干这个的。

6.2 CTE：把子查询变成"带名字的临时视图"

CTE（Common Table Expression，公共表表达式）是标准SQL语法，MySQL 8.0、PostgreSQL、SQL Server等主流数据库都支持。它的本质就是给子查询起个名字，然后可以在后续查询里反复引用。

还是上面那个"每个品类销量王"的需求，用CTE写，结构就清晰很多：

WITH sales_stats AS ( SELECT pc.category_id, pc.category_name, p.product_name, SUM(oi.quantity) AS total_qty FROM product_categories pc JOIN products p ON p.category_id = pc.category_id JOIN order_items oi ON oi.product_id = p.product_id GROUP BY pc.category_id, pc.category_name, p.product_name ), ranked AS ( SELECT category_id, category_name, product_name, ROW_NUMBER() OVER ( PARTITION BY category_id ORDER BY total_qty DESC ) AS rn FROM sales_stats ) SELECT category_name, product_name FROM ranked WHERE rn = 1;

CTE最大的好处不是性能，而是把复杂的子查询逻辑分层拆开，每一层都像流水线上的一道工序：先算销量统计，再算排名，最后取第一名。排查问题时，哪一层数据不对，单独把那一层拿出来跑一遍即可。

注意一点：CTE如果不加特殊提示，并不保证一定会被物化。同一个CTE被引用两次时，不同数据库的优化器可能选择不同的执行策略——有些会物化，有些会重复执行。所以"CTE一定能减少重复计算"这种说法，只在特定数据库的特定执行计划下成立，实际还是要看EXPLAIN。

6.3 哪些地方能用窗口函数"绕开"子查询

子查询的很多经典场景，本质上是"同一张表内部的自关联"或者"分组后取组内极值"。这类需求在窗口函数出现之前基本只能靠相关子查询硬扛，现在有了更干净的写法。

拿"查每个订单的金额和该用户历史最高订单金额的差值"举例，相关子查询版本：

SELECT order_id, user_id, total_amount, total_amount - ( SELECT MAX(total_amount) FROM orders o2 WHERE o2.user_id = o1.user_id ) AS diff_from_max FROM orders o1;

窗口函数版本：

SELECT order_id, user_id, total_amount, total_amount - MAX(total_amount) OVER (PARTITION BY user_id) AS diff_from_max FROM orders;

窗口函数版本不需要自关联，扫描次数少，可读性也更好。凡是看到MAX、MIN、SUM、AVG、COUNT配合PARTITION BY能表达的逻辑，就不要硬套子查询。

但窗口函数也解决不了所有问题——它没法直接表达"存在性判断"。比如"查有订单但无退款"这种过滤型需求，窗口函数只能先算出标记列再在外面套一层WHERE，本质上还是要借助子查询、CTE或JOIN。所以我的判断原则是：需要保留原表所有行、只追加计算列时，优先窗口函数；需要过滤行、做存在性判断时，优先子查询或半连接。

最后补两句实操上的体会

子查询学到最后，拼的其实不是语法，而是对执行计划和数据特征的敏感度。我至今写任何一条带子查询的SQL，都会本能地过一遍"四问"：内层返回几行？外层有多少行？关联列有没有索引？能不能命中半连接优化？这四个问题想清楚，90%的子查询性能问题都能在写代码阶段被提前消灭。

另一个小技巧是，调试复杂子查询时，别急着把整个嵌套结构跑起来。把内层子查询单独跑一遍，把结果落成一张临时表，从它开始往外层一层层加条件。这样每次变化都能立刻定位是哪一层逻辑出了问题，比对着四层嵌套的SQL干瞪眼效率高得多。子查询这东西，写多了你会慢慢形成肌肉记忆，但始终保持"先清晰、再优化"的克制，才是避免未来在某张大表上翻车的根本办法。