如果你写过一段时间SQL，肯定绕不开子查询这个坎。面试十次有八次会问，业务代码里隔三差五就要写。我前几年带团队的时候，常常看到新同事连JOIN都还没理顺，遇到复杂查询的第一反应就是“先套一层子查询再说”，能跑就行。结果等数据量上来，SQL直接卡死，然后就开始怀疑是不是服务器不行。今天这篇，我就把子查询从头到尾拆一遍，从执行原理、分类、运算符到底层优化，再到我这些年实际踩过的坑，全部摊开来讲。

先说下本文的测试环境：MySQL 8.0.32，所有SQL都是实际跑过的。如果你用的是5.5、5.6这种老版本，部分优化行为会不一样，后面涉及执行计划对比的时候我会特意标注。还有，要是你MySQL还没装利索，先去把环境捣鼓好，再看这篇文章，不然光看不动手，记不住。

1. 子查询到底是个什么东西

1.1 一句话理解子查询的本质

子查询说白了就是嵌套在另一个查询里的完整SELECT语句。它可以把一个“先算出中间结果，再基于中间结果继续筛选”的逻辑，直接写进一条SQL里。不用创建临时表，不用写两条SQL再在代码里拼结果，一条语句搞定。

举个人话例子：你要找“比全公司平均工资高的人”。正常拆解是两步——先算平均工资，再拿每人工龄工资去比。子查询就是把这个两步压缩成一步：WHERE salary > (SELECT AVG(salary) FROM employees)。那个括号里的SELECT就是子查询。

你可能会问：那用JOIN不行吗？很多场景确实行，但有些场景JOIN写起来非常别扭，比如“查每个部门里工资最高的人”“查人数不超过3人的部门有哪些”，子查询的表达力要强得多。这也是为什么明明有这么多种SQL写法，子查询依然没法被替代。

但这里有个关键点你得想清楚：子查询不是“性能差的写法”的代名词，也不是“高手都不屑用”的玩具。它只是SQL的一种组成方式，性能好坏取决于你怎么写、优化器怎么执行。这句话我先放这，后面你会反复体会到。

1.2 两种执行方式：先算里层还是边查边算

这是理解子查询性能的核心，分两类：

非相关子查询（不相关子查询）：子查询不依赖外层查询的任何字段，可以独立执行。MySQL会先执行内层子查询，拿到结果集，再用这个结果集去执行外层查询。比如上面那个“比全公司平均工资高”的SQL，AVG这个子查询跟外部每一行都没关系，整个查询只需要执行一次内层子查询。

相关子查询：子查询里引用了外层查询的字段，比如常见的“查每个部门工资最高的人”，内层WHERE条件里用了外层的e.department_id。这种写法需要外层每取出一行，就拿这一行的值去跑一次内层子查询。如果你的表有1万行，内层子查询理论上会被执行1万次。

这就是很多人说“子查询慢”的根源——他们多半是把相关子查询写在了大表上，又没有合适的索引。但换个角度来说，相关子查询语义清晰，如果驱动表数据量小、内层条件有索引，性能完全可以接受。

用行话说就是：非相关子查询的执行计划里通常出现SUBQUERY或MATERIALIZED，相关子查询则会出现DEPENDENT SUBQUERY。这四个关键字你在EXPLAIN里看到就明白怎么回事了，后面第4节我会专门讲怎么看。

2. 子查询的分类，一张表理清楚

子查询可以从三个维度去拆分：按返回的结果形状、按出现的位置、按配合的运算符。这三个维度互相交叉，理解透了，你看到任何一条带子查询的SQL，都能迅速判断它属于哪种、该怎么优化。

2.1 按返回结果分四类

这是最常见的分类口径，决定了你可以拿什么运算符去接子查询的结果。

标量子查询：结果只有一行一列，一个值。典型姿势是放在SELECT后面当列用，或者放WHERE后面和单值比较。比如查出每个人的姓名、工资、以及工资和部门平均工资的差值：

SELECT name, salary, (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id) AS dept_avg FROM employees e1

注意，标量子查询必须保证最多只返回一行一列，否则直接报错。这句你在第5节能看到一个经典翻车现场。

列子查询：返回一列多行，也就是一个“值的集合”。最常见的配合是IN、NOT IN、ANY、ALL。比如查出所有在“研发部”和“市场部”的员工：

SELECT * FROM employees WHERE department_id IN ( SELECT department_id FROM departments WHERE dept_name IN ('研发部', '市场部') )

行子查询：返回一行多列。这个用得相对少，但写起来很紧凑。比如查“和员工编号10086同一部门、同一职级的人”：

SELECT * FROM employees WHERE (department_id, job_level) = ( SELECT department_id, job_level FROM employees WHERE employee_id = 10086 )

表子查询：返回多行多列，本质上是一个派生表。必须放在FROM子句后面，而且MySQL强制要求给这个“临时表”起别名。比如算每个部门的工资总额，再筛出总额大于平均值的部门：

SELECT dept_id, total_salary FROM ( SELECT department_id AS dept_id, SUM(salary) AS total_salary FROM employees GROUP BY department_id ) AS dept_summary WHERE total_salary > ( SELECT AVG(total_salary) FROM ( SELECT SUM(salary) AS total_salary FROM employees GROUP BY department_id ) AS t )

这个嵌套层级有点深，但逻辑是一层层剥开的：先算每个部门总额，再算“部门总额的平均值”，最后筛。

2.2 按出现位置分四类

子查询不是只能放WHERE后面，它一共有四个常见落脚点。

WHERE子句中：这是最经典的位置，用来过滤数据。可以是值比较、IN列表、EXISTS判断。绝大多数业务场景都在这。

FROM子句中：子查询结果当作一张临时表，配合JOIN或直接查。MySQL会在内部把这种子查询物化成一张临时表，8.0版本还能自动对物化表加索引，性能比老版本好了不少。但注意：这种物化表默认没有主键约束，你要去重得自己处理。

SELECT子句中：说白了就是“查出来的每一行，临时去查一个值”。典型的就是算占比、算差值。这一类要特别注意，它往往是最容易写成相关子查询的地方，行行执行，大表上务必小心。

HAVING子句中：对分组后的结果做过滤。比如找出“平均工资高于全公司平均工资的部门”：

SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id HAVING AVG(salary) > (SELECT AVG(salary) FROM employees)

还有个特殊位置是EXISTS后面，严格说起来EXISTS本身就是子查询的用法之一，但它用法特殊，我单独讲。

2.3 运算符对照表：每个符号的语义都要门儿清

子查询配合的运算符直接决定它“能不能这么写”，也决定了优化器会不会搞出慢查询：

这里说两个容易被误解的点。

第一，ANY和SOME完全等价，只是SQL标准里保留了SOME这个写法，MySQL都支持，你随便用，但团队规范最好统一，别一会儿ANY一会儿SOME。

第二，ALL和ANY的语义要结合比较符理解。salary > ALL(子查询集合)，意思是“大于集合里的最大值”。salary > ANY(集合)，意思是“大于集合里的最小值”。很多人死记硬背容易混，你就想：ALL要跟每一个比都成立，ANY只要有一个成立就行。大于ALL等于大于最大值，大于ANY等于大于最小值。小于ALL等于小于最小值，小于ANY等于小于最大值。

我把这部分讲透，是因为我见过不少团队，IN用得很熟练，一到ANY、ALL就发怵，甚至干脆全用OR拼条件，又长又慢。其实ANY和ALL在“找比所有人都/某个人大”这类场景里非常顺手。

3. 一套业务案例，把子查询全串起来

光讲分类没意思，我构造一个小而全的业务模型，把上面所有类型的子查询都跑一遍。这个模型不复杂，但我故意设计了几种典型场景，每个场景都给你一条能直接跑的SQL，以及优化思路。

3.1 建表和数据准备

我们模拟一个公司人力资源系统，三张表：员工表、部门表、订单表（对，场景里顺便加点销售语义）。

CREATE TABLE departments ( dept_id INT PRIMARY KEY, dept_name VARCHAR(50) ); CREATE TABLE employees ( emp_id INT PRIMARY KEY, name VARCHAR(50), department_id INT, salary DECIMAL(10,2), job_level VARCHAR(20) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, emp_id INT, amount DECIMAL(10,2), order_date DATE );

造数据我就不贴完整插入了，太占地方，你随便插入个几百行测试就行。核心是这几张表要有主键，外键关系清晰。

3.2 场景一：找工资高于部门平均工资的员工

这是“相关子查询”的入门题，几乎面试必问。

SELECT e1.emp_id, e1.name, e1.salary, e1.department_id FROM employees e1 WHERE e1.salary > ( SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id );

运行流程：外层拿第一行员工，用它的department_id去内层算出这个部门的平均工资，再比较这一行的工资是否大于它。然后外层第二行，重复……每一行员工都会触发一次内层查询。

所以这个SQL的性能瓶颈一目了然：如果employees有1万行，内层平均工资查询可能要跑1万次。但如果你不假思索地把这个写法用在100万行的员工表上，那就要出事。

优化思路有两个。第一，如果部门数量不多（比如100个部门），可以改成先算出部门平均工资，再JOIN：

SELECT e.emp_id, e.name, e.salary, e.department_id FROM employees e JOIN ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) d ON e.department_id = d.department_id WHERE e.salary > d.avg_salary;

这样内层GROUP BY只执行一次，MySQL只要扫描一次employees做分组，再和临时表关联即可。实测在10万行数据下，这个改写能快一个数量级。原因就是原来1万次内层执行的扫描成本，被压缩成了1次分组+关联。

3.3 场景二：找出每个部门工资最高的人是谁

这个需求有几种写法，我拿最典型的子查询方案来说。

SELECT e1.name, e1.department_id, e1.salary FROM employees e1 WHERE e1.salary = ( SELECT MAX(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id );

这也是一种相关子查询。跟上一个问题一样，如果部门多、员工多，内层MAX查询被反复执行。性能优化一般优先考虑窗口函数（MySQL 8.0支持）：

SELECT name, department_id, salary FROM ( SELECT name, department_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn FROM employees ) t WHERE rn = 1;

子查询在FROM里做了一次全表扫描+窗口计算，之后外层只负责过滤。这个写法在8.0里基本是“每个部门TOP N”系列需求的标准答案。

3.4 场景三：查出没有任何订单的员工

这个需求就是典型的“哪些人在另一张表里不存在”，第一反应可能会用NOT IN。

SELECT emp_id, name FROM employees WHERE emp_id NOT IN ( SELECT emp_id FROM orders );

但等等，这个写法有个大坑：如果orders.emp_id里有NULL，NOT IN直接返回空结果。这不是玄学，是SQL的三值逻辑导致的。因为NOT IN意思是“不等于列表里任何一个值”，可列表里一旦有NULL，比较结果就会变成UNKNOWN，最终一行都查不出来。

解决方案之一是改用NOT EXISTS：

SELECT e.emp_id, e.name FROM employees e WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.emp_id = e.emp_id );

这个写法是逐行去判断“这一行员工是否在订单表里存在匹配记录”，NULL不会破坏语义，因为EXISTS只关心“有没有行”，不关心列值是多少。另外，NOT EXISTS通常还会利用到orders表上的(emp_id)索引，实际执行反而更快。

我把这种坑放在场景里说，是为了让你形成条件反射：看到NOT IN，第一件事先确认子查询结果有没有NULL可能性。这是一个能救你于生产事故的习惯。

3.5 场景四：算每个部门的销售额占比

这个需求很典型，尤其在报表里。子查询可以很好地表达“先算总数，再算各占比”。

SELECT d.dept_name, SUM(o.amount) AS dept_sales, SUM(o.amount) / (SELECT SUM(amount) FROM orders) AS sales_ratio FROM orders o JOIN employees e ON o.emp_id = e.emp_id JOIN departments d ON e.department_id = d.dept_id GROUP BY d.dept_name;

这里SELECT后面的(SELECT SUM(amount) FROM orders)是一个非相关标量子查询，跟外部无关，整个查询执行的时候只算一次总数，成本不高。如果还想更严谨地算占比，可以挪到FROM里做派生表，留给你自己练习。

4. 性能怎么查：执行计划与优化策略

4.1 EXPLAIN里那几个关键字，一眼看穿子查询怎么跑

写优化SQL，第一工具就是EXPLAIN。你不用记所有输出项，只需要抓住和子查询相关的几个关键字：

SUBQUERY：非相关子查询，会先独立执行，结果就绪之后外层再跑。这种通常问题不大，内层查询的结果集大小才是关键。

DEPENDENT SUBQUERY：相关子查询，外层每一行都要触发一次内层查询。看到这个，你就要警觉：外层驱动行数多不多？内层有没有走索引？两个条件都不满足，基本可以断定这SQL会慢。

MATERIALIZED：MySQL把子查询物化成一张临时表来处理。8.0一般会自动物化并加索引，这是IN类子查询最常见的优化路径。

看个例子。前面场景一的那个相关子查询：

EXPLAIN SELECT e1.emp_id, e1.name, e1.salary FROM employees e1 WHERE e1.salary > ( SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id );

如果employees上department_id有索引，你会看到内层的DEPENDENT SUBQUERY走的是索引扫描；如果没有索引，那就是全表扫描，成本翻倍。这就是为什么我在讲场景时反复强调索引的价值。

4.2 IN为什么会被优化器“加工”

MySQL 5.6之后对IN子查询做了一大改进——半连接优化。IN的语义是“等于集合中任意一个值”，优化器会把这种查询自动改写成类似JOIN的形式，去重后返回结果，而不是傻乎乎地把整个集合列出来再逐行比对。

所以，非相关的IN子查询在MySQL 8.0里通常性能不错。但有两个前提：子查询结果集别太大，以及子查询里的关联字段有索引。如果子查询返回几十万行，即使逻辑成立，内存里物化这张大表也不是闹着玩的，该缓存淘汰缓存，该全表扫全表扫。

这时候你可以手动把IN改成JOIN + DISTINCT，或者用临时表先把子查询结果保存下来再关联。我一般先EXPLAIN，看到ROWS特别大，就果断改写。

4.3 EXISTS和IN到底怎么选

这是经典争论。我的结论是：没有绝对谁快，要看“外查询驱动行数”和“内查询结果集大小”。

简单说：外层表数据量小、内层判断快，EXISTS很合适；子查询结果集小、外层数据量大，IN更稳。但8.0优化器还会在内部做改写，所以你真正要做的还是EXPLAIN一把，用数据说话。

4.4 子查询改写JOIN的三个原则

我在实际优化中，把“子查询改写JOIN”提炼成了三条原则。

原则一：非相关子查询且结果集可去重，优先JOIN。比如查“在研发部或市场部的员工”，用IN没问题，但JOIN后语义等价，通常执行计划也更直观。

原则二：相关子查询出现在SELECT列表中，改成JOIN + 聚合。比如前面那种查员工及其部门平均工资的，直接JOIN一张聚合好的部门均资表，比逐行算要快得多。

原则三：删除/更新场景别硬改子查询，先想清楚逻辑。UPDATE和DELETE的子查询改写约束比SELECT多，尤其是“不能同时查询和修改同一张表”，我下面专门讲。

这三条不是万能钥匙，但覆盖了大部分慢SQL的优化方向。

5. 避坑手册：我踩过的子查询的坑

这一节全是我的实战血泪史。网上的教程把子查询语法讲得清清楚楚，但这些坑没人提醒你就是会踩。

5.1 NOT IN遇上NULL直接翻车

前面场景三里已经提到。我再强调一遍：子查询结果集里只要有NULL，整个NOT IN查询返回空集。这不是MySQL的bug，是SQL标准的三值逻辑。NULL代表未知，在NOT IN集合比较里，任何值跟NULL比较都返回UNKNOWN，WHERE只保留TRUE的行，UNKNOWN直接被过滤掉。

解决办法：要么在子查询里加WHERE xxx IS NOT NULL，要么干脆换成NOT EXISTS。团队规范我可以给你一个默认项：判断“不在某集合里”，一律写NOT EXISTS，省心。

5.2 派生表必须起别名

FROM子句里的子查询，MySQL强制要求有别名，不然直接语法错误。报错信息是Every derived table must have its own alias。

这个坑新手几乎必踩，但好解决。我记忆方法很简单：把FROM子查询想象成一张真实的临时表，你得给它起个表名，后面跟JOIN、WHERE才有的指代。

5.3 UPDATE/DELETE不能动同一张表

这是大名鼎鼎的Error Code: 1093：You can't specify target table 't' for update in FROM clause。

意思是，你不能在UPDATE一张表的同时，又在这条语句里用子查询去查同一张表。比如我想把所有工资低于部门平均工资的员工的工资，直接置为部门平均工资，你可能会写：

UPDATE employees SET salary = ( SELECT AVG(salary) FROM employees ) WHERE salary < ( SELECT AVG(salary) FROM employees );

MySQL直接拒绝执行。绕行方案是包一层临时表。MySQL 8.0.29之前的经典写法：

UPDATE employees SET salary = ( SELECT avg_salary FROM ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) AS dept_avg WHERE dept_avg.department_id = employees.department_id ) WHERE salary < ( SELECT avg_salary FROM ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) AS dept_avg2 WHERE dept_avg2.department_id = employees.department_id );

注意，MySQL 8.0.29以后对于“子查询里再次包裹子查询”进行了更严格的重写检查，有时你发现老办法也报1093，那就把数据先落到临时表，再JOIN更新。虽然多一步，但逻辑清晰、也不容易触发限制。

5.4 标量子查询返回多行直接报错

标量子查询只能返回一行一列。如果内层结果有多行，MySQL直接报Subquery returns more than 1 row。典型例子是：内层子查询忘加LIMIT，或者筛选条件根本不够唯一。

这个错误信息极度常见。解决办法是：要么在子查询里做聚合（MAX、MIN之类），要么加LIMIT 1。但加LIMIT 1前先想清楚，你是不是真的只想要“任意一行”，还是应该找“最大/最小”的那行。业务语义不搞清楚就LIMIT，是隐藏bug的来源。

5.5 子查询里的ORDER BY和LIMIT

老版本MySQL对“IN子查询里的ORDER BY”是不予理睬的，优化器可能会直接忽略，导致你以为“先排序再取前几名，最后再IN”的逻辑根本没生效。8.0优化器改进了不少，但我建议你写关键逻辑时仍不要依赖这种内部行为，特别是在子查询里要取“每组前几条”时，老老实实用窗口函数或JOIN。

比如“查每个部门工资最高的三个人”这种需求，你千万不要写成：

SELECT * FROM employees WHERE (department_id, salary) IN ( SELECT department_id, salary FROM employees ORDER BY salary DESC LIMIT 3 );

这个LIMIT是对整个子查询生效的，不是对每个部门生效。语义完全错位。正确做法是窗口函数ROW_NUMBER()，或者相关子查询+COUNT比较。这一踩，能让你少走很多弯路。

5.6 常见问题速查表

说到这，我再分享一个我经常用的小工具：EXPLAIN ANALYZE。MySQL 8.0.18以后可以用它直接打印实际执行时间，比EXPLAIN估算值直观得多。我以前优化慢SQL，习惯先EXPLAIN看方向，再EXPLAIN ANALYZE看真实耗时，两相对照，问题基本八九不离十。

最后再唠叨一句可能招人嫌的实话。网上吵架主题永远是“子查询和JOIN谁更快”，我这么多年带项目下来，最大的体会是：SQL性能问题很少是写法单独决定的，表结构、索引、数据分布、优化器版本全都在起作用。子查询本身不是洪水猛兽，写之前先把业务逻辑想清楚，写完用执行计划验证，数据量大了再考虑改写，这个顺序比背任何“XX一定比XX快”的口诀都管用。