写SQL绕不开子查询这一点我在MySQL上踩过的坑可以写一篇文章。前几年写业务统计一看到“找出每个部门工资最高的员工”“查没有订单的客户”这类需求我第一反应就是套一个子查询。结果有时候跑得飞快有时候直接把数据库拖慢还有一次NOT IN莫名查不到数据排查到凌晨才发现是子查询结果里带了NULL。后来我把MySQL里的子查询彻底研究了一遍才发现这东西的规则远比想象中清晰无非是返回几行几列、写在哪个位置、和外层查不查得到关系。这篇就把这些规则一次讲透从分类、位置、相关子查询、NULL陷阱到UPDATE/DELETE里的写法再到慢SQL优化全程用MySQL真实语法演示。适合刚上手的新手也适合那些SQL能跑通但说不清理所以然的同学。1. 子查询的四种分类标量、列、行、表1.1 先从“返回结果”看子查询子查询的本质就是一条完整的SELECT语句被塞进另一条SQL里。它执行完会产生一个结果集这个结果集长什么样决定了外面的表达式能不能接住它。MySQL里可以按返回内容把子查询分成四类标量子查询返回一行一列本质上就是一个普通的单值可以直接用在等于、大于、小于这些比较运算符旁边。列子查询返回一列多行值可以理解成一串列表典型搭配是IN、NOT IN、ANY、ALL。行子查询返回一行多列MySQL支持用行构造器做整体比较比如(a, b) (SELECT ...)。表子查询返回多行多列这种子查询没法放在WHERE里直接比只能放在FROM后面当一张临时表用也就是派生表。用表格看会更清楚类型返回结果典型写法位置语法要点标量子查询单行单列SELECT、WHERE、HAVING必须保证最多返回一行否则报错列子查询单列多行WHERE中搭配IN、ANY、ALL不能直接和、、连用行子查询单行多列WHERE中搭配行构造器字段个数和顺序必须完全一致表子查询多行多列FROM子句必须取别名这个分类建议直接当作判断标准看到一条SQL里出现了括号先别看逻辑先看括号里的SELECT返回几行几列你就知道它想干什么。这也是后面所有写法和优化的地基绝大多数子查询报错都源自“内外形状不匹配”。1.2 标量子查询的写法细节标量子查询是最好理解的因为它的结果可以当作一个列值来用。比如查工资高于全公司平均工资的员工SELECT name, salary FROM emp WHERE salary (SELECT AVG(salary) FROM emp);这里内层SELECT AVG(salary)返回一行一列就是全公司的平均工资。外层再把每个人的工资和它比较超过的就留下。语法上没有任何歧义执行顺序也很直观先算平均值再用这个平均值过滤外层数据整个过程只执行一次子查询性能风险很低。有一点必须注意标量子查询如果返回超过一行会直接报错Subquery returns more than 1 row。这是新手最容易踩的坑。比如下面这条SQL如果部门10里有三个员工内层就会返回三行工资外层直接崩溃SELECT name FROM emp WHERE salary (SELECT salary FROM emp WHERE dept_id 10);这种场景应该改用IN比如SELECT name FROM emp WHERE salary IN (SELECT salary FROM emp WHERE dept_id 10);反过来如果标量子查询返回0行结果就是NULL外层条件不会报错但WHERE比较结果是UNKNOWN行会被过滤掉。这一点后面讲NULL陷阱的时候会重点展开现在只要记住标量子查询不是“必须有值”而是“最多一行”。1.3 列子查询和行子查询一列值和一个元组列子查询返回的是一列值可以理解成一张竖着的列表。最常见的搭配就是IN和NOT IN。比如查所有在深圳部门上班的员工SELECT name FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE city 深圳);这里内层返回所有深圳部门的id外层判断员工的dept_id是否在这个id列表里。列表里如果有100个idIN判断本质就是dept_id 1 OR dept_id 2 OR ...的快捷写法。ANY和ALL也属于列子查询的搭档但它们不是“相等判断”而是带着比较运算符走的后面第4节讲NULL陷阱时我会专门说它们的坑。行子查询用得相对少但遇到“同时比多列”的场景非常省事。MySQL允许用行构造器把多个字段拼成一行再比较SELECT id, name FROM emp WHERE (id, name) (SELECT id, name FROM emp WHERE id 100);内层返回的是id100这一行的两列外层把每一行的(id, name)二元组和它做整体比较。这里要求字段数量、顺序完全一致少一个多一个都会报Operand should contain 1 column(s)一类的错误。这种写法在业务里确实用得少但要读懂别人代码看到这种括号包裹多个字段的形式时至少要知道它属于行子查询。1.4 表子查询必须放在FROM后面并取别名表子查询返回的是多行多列本质上就是一张临时表。这种子查询只能出现在FROM子句里并且必须取别名。比如按部门统计完人数后再关联部门表SELECT d.name, t.cnt FROM dept d JOIN ( SELECT dept_id, COUNT(*) AS cnt FROM emp GROUP BY dept_id ) t ON d.id t.dept_id;内层SELECT dept_id, COUNT(*) ... GROUP BY dept_id返回一张两列的临时表我用t给它起了别名外层再和dept表做JOIN。如果不取别名MySQL会直接报错Every derived table must have its own alias。这个报错可以说是派生表位置出现频率最高的错误了几乎没有之一。2. SELECT、FROM、WHERE、HAVING四个位置子查询的写法禁忌完全不同2.1 SELECT子句后的子查询想清楚它是“逐行执行”还是“执行一次”SELECT列表里放子查询本质是把子查询当作一个新列查出来。最常见的是放标量子查询。比如查员工基本信息的同时带出全公司最高工资SELECT name, salary, (SELECT MAX(salary) FROM emp) AS max_salary FROM emp;这个子查询不涉及外层MySQL一般会把它当作常量处理执行一次就好性能没问题。但如果你写的是相关子查询比如带出本部门最高工资SELECT name, salary, (SELECT MAX(salary) FROM emp e2 WHERE e2.dept_id e1.dept_id) AS dept_max FROM emp e1;这时候MySQL的执行逻辑就变成了外层emp表有多少行内层子查询就可能执行多少次。这就是后面要说的相关子查询。别在SELECT列表里写这种相关子查询去处理大表否则SQL大概率会变成慢SQL。我见过不止一次因为这种写法把响应时间从几十毫秒拉到几十秒的案例外层行数一大问题立刻暴露。2.2 FROM子句后的子查询MySQL把它当一张临时表FROM后面的括号子查询叫派生表MySQL会先执行它把结果放到内存或临时表里再交给外层做后续操作。MySQL 5.7之后对派生表有“合并”和“物化”两种处理方式能合并的会直接把子查询的SQL和外层SQL合并执行可以减少临时表开销不能合并的就物化成临时表物化时如果发现子查询结果集比较大甚至会在磁盘上生成临时表这时候性能就会明显下降。派生表有两条硬规矩一是必须取别名二是一般情况下不能引用外层的列。MySQL 8.0.14开始支持了一种叫LATERAL的派生表可以在内层引用外层列实现一些高级用法但这属于进阶话题业务上90%的派生表都不需要它。还有一个容易忽视的细节派生表里如果加了ORDER BY在外层没有LIMIT的情况下这个排序很可能被优化器丢掉因为MySQL认为既然外层要全量处理这张表派生表排不排序对最终结果没有影响。所以在派生表里写ORDER BY ... LIMIT n才有实际意义。2.3 WHERE子句里的子查询最常使用也最容易出错WHERE子句是子查询的主战场。它接受三类子查询但不同运算符接受的类型不一样比较运算符、、、、、只接受标量子查询也就是一行一列。IN、NOT IN、ANY、SOME、ALL接受列子查询。EXISTS、NOT EXISTS接受任意子查询因为它只看“有没有行返回”。行构造器比较(a, b) (SELECT ...)接受行子查询。比如前面案例里WHERE salary (SELECT AVG(salary) FROM emp)子查询是标量所以可以用但如果某个子查询返回一列你就不能直接写WHERE salary (SELECT salary FROM emp ...)因为MySQL不知道拿这一列怎么和一个值比大小。这种场景要么改成标量要么配合ANY、ALL使用WHERE salary ANY (SELECT salary FROM emp WHERE dept_id 10);意思是“大于部门10里任何一个人的工资”。注意 ANY和 ALL的语义完全不同 ALL是“大于部门10里所有人的工资”也就比最高工资还高。这两个运算在业务里确实用得少但一旦遇到语义搞反就很麻烦。2.4 HAVING子句里的子查询分组之后的过滤器HAVING是在GROUP BY之后执行的过滤条件所以它里面的子查询经常搭配聚合函数。一个典型场景是找出平均工资高于全公司平均水平的部门SELECT dept_id, AVG(salary) AS avg_sal FROM emp GROUP BY dept_id HAVING AVG(salary) (SELECT AVG(salary) FROM emp);内层子查询先算出全公司平均工资外层再按部门分组算平均工资最后用HAVING过滤。整个执行顺序是先执行子查询再分组聚合再HAVING过滤最后SELECT输出。理解这个顺序对排查“为什么HAVING里不能写别名”这类问题很有帮助因为SELECT列表的别名是在HAVING过滤之后才生成出来的HAVING阶段根本看不到别名。另外虽然ORDER BY里也可以放子查询但业务里极少用而且可读性差一般不建议学。子查询能出现的常规位置就是SELECT、FROM、WHERE、HAVING以及后面要说的UPDATE、DELETE、INSERT把这几类吃透就足够应付绝大多数开发场景了。3. 相关子查询与非相关子查询性能差异的核心来源3.1 非相关子查询先跑内层再跑外层如果子查询不引用外层任何字段它就是一个完全独立的SQLMySQL通常先执行它得到一个结果集再用这个结果集去处理外层。这种叫非相关子查询。比如SELECT name FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE city 上海);内层不依赖外层MySQL先拿到所有上海的部门id再对外层员工表做过滤。执行计划里它通常只算一遍性能相对可控。非相关子查询在EXPLAIN里的select_type一般是SUBQUERY或者MATERIALIZED前者代表一次性执行后者代表把结果物化成临时表后再参与外层计算无论哪种都不会因为外层行数多而重复执行内层。3.2 相关子查询外层每行都要执行一遍如果子查询里引用了外层的列它就不是独立查询了。比如经典场景“找出工资高于本部门平均工资的员工”SELECT e1.name, e1.salary FROM emp e1 WHERE e1.salary ( SELECT AVG(salary) FROM emp e2 WHERE e2.dept_id e1.dept_id );这里内层的e2.dept_id e1.dept_id引用了外层的e1.dept_id所以对外层emp表的每一行MySQL都要带着这一行的部门id去执行一次内层查询。外层有10万行内层就要执行10万次。这就是相关子查询性能差的根源不用怀疑就是字面意义上的“逐行执行”。MySQL优化器会尝试缓存一些重复子查询或者把EXISTS改写为semi-join但相关子查询仍然是慢SQL的高发区域。在EXPLAIN里非相关子查询的select_type通常是SUBQUERY或MATERIALIZED而相关子查询通常是DEPENDENT SUBQUERY看到DEPENDENT这个词就要警觉尤其当它出现要扫描的外层表行数特别大时基本可以断定这条SQL需要改造了。3.3 用EXISTS写相关子查询的正确姿势EXISTS是相关子查询最经典的搭档。它不管内层SELECT的是什么只看有没有返回行所以内层习惯写成SELECT 1。比如查那些在深圳有部门的员工相关子查询配合EXISTS可以这样写SELECT e.name FROM emp e WHERE EXISTS ( SELECT 1 FROM dept d WHERE d.id e.dept_id AND d.city 深圳 );这种写法等价于对每个员工去dept表里查是否有他所在部门且城市是深圳的记录有就保留。在dept表的id列有索引的前提下随着外层行数增加单次查询成本很低整体可能比大列表的NOT IN要稳。需要强调的是相关子查询并不总是坏选择。当内层表有合适的索引、外层表数据量不大时它的表现完全能接受真正要避免的是“外层大表 × 内层无索引且每次全表扫”的组合。判断依据还是EXPLAIN不要因为别人说EXISTS快就无脑改。4. NOT IN为什么查不出来子查询里的NULL值陷阱4.1 NOT IN遇到NULL结果直接变成空集这是子查询领域最著名的坑没有之一。假设我要查没有下过订单的客户SELECT * FROM customer c WHERE c.id NOT IN (SELECT customer_id FROM orders);如果orders表里customer_id这一列存在任何一条NULL值这条SQL查出来的结果就是空集哪怕明明有很多客户没下过单一条都不会返回。原因要从SQL的三值逻辑说起。SQL里的比较结果除了TRUE、FALSE还有UNKNOWN。x NOT IN (a, b, NULL)等价于x a AND x b AND x NULL。而任何值和NULL比较都会得到UNKNOWNAND链里一旦出现UNKNOWN整个表达式就不是TRUEWHERE就会把行过滤掉。换句话说只要子查询列表里混进一个NULLNOT IN就彻底废了。遇到这种情况解决问题的姿势有两个-- 姿势一内层先过滤掉NULL SELECT * FROM customer c WHERE c.id NOT IN ( SELECT customer_id FROM orders WHERE customer_id IS NOT NULL ); -- 姿势二换成NOT EXISTS天生免疫NULL SELECT * FROM customer c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.id );我的建议是凡是写NOT IN最好先确认子查询列上有没有NOT NULL约束没有就主动加IS NOT NULL或者干脆用NOT EXISTS虽然写法看起来长一点但语义最稳不会因为底层数据变化突然翻车。4.2 IN、ANY、ALL在NULL面前的表现IN的情况比NOT IN好一点但同样要留心。x IN (1, 2, NULL)等价于x 1 OR x 2 OR x NULL。如果x匹配到了1或2结果是TRUE能正常返回如果x一个都不匹配比如x3三个比较分别是FALSE、FALSE、UNKNOWNOR的结果是UNKNOWN这行照样被过滤。所以不能说IN遇到NULL就出错只能说遇到NULL时它不一定返回你预期的TRUE。ANY和ALL的NULL问题更隐蔽很多人根本没有意识到。x ANY (子查询)是OR逻辑只要集合里有任何一个非NULL值让x大于它结果就是TRUENULL影响不大。但x ALL (子查询)是AND逻辑只要集合里有任何一个NULLx NULL就是UNKNOWN整个AND链直接变UNKNOWN结果就不会返回行。所以凡是用 ALL、 ALL这类写法内层最好显式过滤掉NULLWHERE salary ALL ( SELECT salary FROM emp e2 WHERE e2.dept_id e1.dept_id AND e2.salary IS NOT NULL );4.3 判断NULL永远用IS NULL不要用等号这一条虽然不在子查询语法里但它是所有NULL坑的底层原因。SQL标准里任何值用、!、和NULL比较结果都是UNKNOWN不是TRUE或FALSE。只有IS NULL和IS NOT NULL是专门用来判断NULL的。这也是为什么内层过滤条件写WHERE customer_id ! NULL无法生效的原因它不会报错但也不会过滤掉任何东西NULL判断必须写成IS NOT NULL。所以检查子查询返回的内容是否包含NULL最快的办法就是看对应的表结构里该列有没有NOT NULL约束如果有说明这一列不可能有NULLNOT IN可以放心用如果没有就按上面的姿势处理。多花十秒钟查一下建表语句能省下半夜排查数据问题的功夫。5. INSERT、UPDATE、DELETE里怎么用子查询改了数据才知道的坑5.1 INSERT INTO ... SELECT把一张表的查询结果灌进另一张表这种写法本质就是“用查询结果批量插入”。比如把研发部门的员工复制到备份表INSERT INTO emp_bak (id, name, salary, dept_id) SELECT id, name, salary, dept_id FROM emp WHERE dept_id (SELECT id FROM dept WHERE name 研发部);这里SELECT id FROM dept返回标量部门id外层SELECT只取对应的员工。INSERT和SELECT的列数量、类型必须对齐否则MySQL会报列数不匹配的错误。如果只是想快速复制整表还可以用CREATE TABLE new_table AS SELECT ...但注意这种方式不会自动复制主键、索引等结构只复制数据后续该加的索引还得手动补。5.2 UPDATE里用子查询注意SET和WHERE中的关联给指定部门的员工统一加薪10%UPDATE emp SET salary salary * 1.1 WHERE dept_id (SELECT id FROM dept WHERE name 研发部);这个子查询引用的是dept表没有和emp表产生“同表更新”冲突所以能正常执行。相关子查询也可以出现在SET里比如把每行工资更新成本部门平均工资UPDATE emp e SET e.salary ( SELECT AVG(salary) FROM emp e2 WHERE e2.dept_id e.dept_id );这个写法在MySQL里实际是允许的但逻辑上很危险因为它是“把每行工资改成部门平均值”执行期间表数据在变不同行的结果可能互相影响。真要更新同表数据建议先计算好目标值再用JOIN或临时表方式处理不要在这种场景里追求花活。5.3 同表更新的经典报错You cant specify target table for update in FROM clause直接更新emp表时如果子查询的FROM里又出现empMySQL会报错。比如UPDATE emp SET salary (SELECT MAX(salary) FROM emp) WHERE id 100;这个SQL会被MySQL拒绝报You cant specify target table emp for update in FROM clause。原因是子查询的FROM和UPDATE的目标表是同一张表MySQL不允许这种直接引用避免逻辑混乱。绕法也简单把子查询再包一层派生表让MySQL先物化成临时表再去更新UPDATE emp SET salary ( SELECT m.max_salary FROM (SELECT MAX(salary) AS max_salary FROM emp) m ) WHERE id 100;内层SELECT MAX(salary) FROM emp被包进(SELECT ...) m派生表后MySQL会先生成一张临时表然后外层再从中取值这样就不存在“直接引用目标表”的问题了。这个技巧在MySQL 5.7和8.0都适用用途虽然偏门但遇到这种报错时非常好使。5.4 DELETE里用子查询同表删除的限制同样存在DELETE和UPDATE的限制类似。比如按条件删除低薪员工如果直接写DELETE FROM emp WHERE id IN (SELECT id FROM emp WHERE salary 3000);同样会报You cant specify target table emp for update in FROM clause。绕法还是包一层派生表DELETE FROM emp WHERE id IN ( SELECT id FROM ( SELECT id FROM emp WHERE salary 3000 ) tmp );这里三层结构看起来繁琐但意义很明确最内层查出要删的id中间层把结果变成临时表外层再DELETE。业务上如果经常要“按同表条件删数据”不如先想想能不能用JOIN、临时表或用主键列表分两次执行可读性会更好也能减少一次性锁大量行的风险。6. 子查询变慢SQL用EXPLAIN定位再按这三招优化6.1 先学会看EXPLAIN里的select_type遇到子查询性能问题第一步永远是执行EXPLAIN。下面这张表列出和子查询直接相关的select_typeselect_type表示的含义常见风险SUBQUERY非相关子查询执行一次低MATERIALIZED子查询被物化成临时表且可能自动建索引中等物化有开销DEPENDENT SUBQUERY相关子查询外层每行都可能执行一次高UNCACHEABLE SUBQUERY子查询无法缓存每次都要重新算高DERIVEDFROM子句后的派生表中等看是否合并如果你的EXPLAIN里出现DEPENDENT SUBQUERY且外层表扫描行数很大那就要想办法优化。出现DERIVED且物化时不走索引也要检查派生表上是否有合适的索引可以利用。MySQL 8.0还提供了EXPLAIN ANALYZE可以直接输出每个步骤实际消耗的时间、行数比普通EXPLAIN更直观用法是把原来的EXPLAIN换成EXPLAIN ANALYZE执行。它会真正跑一遍查询线上大查询慎用但排查慢SQL很有价值。6.2 优化招一IN和EXISTS别再死记口诀网上流传很久的说法是“IN表大就慢EXISTS表大就快”这在MySQL 5.6之后已经不够准确。MySQL优化器会把部分IN子查询改写为semi-join也会把EXISTS改写成其他形式。所以判断标准只有一个拿出EXPLAIN看实际执行计划。如果两条SQL执行计划一样性能就不会有本质区别没必要为了所谓的“经验”强行改写法。不过有两个方向可以作为初筛逻辑如果内层子查询结果集很小用IN一般没问题MySQL甚至可能物化子查询后自动加索引如果外层表小、内层表大且内层关联列刚好有索引用EXISTS相关子查询通常每行查询成本极低表现可能更好。关键还是内层的关联列必须有索引无论IN还是EXISTS内层的WHERE关联字段如果没索引都要走全表扫描性能必然差。6.3 优化招二子查询改写为JOIN很多子查询本质上就是一次关联查询改写为JOIN后执行计划往往更清晰。比如“查在深圳有部门的员工”-- 子查询版 SELECT e.* FROM emp e WHERE e.dept_id IN (SELECT id FROM dept WHERE city 深圳); -- JOIN版 SELECT DISTINCT e.* FROM emp e JOIN dept d ON e.dept_id d.id WHERE d.city 深圳;JOIN版的好处是优化器可以用匹配算法直接关联两表而不是先物化一个临时结果集。代价是JOIN可能因一对多关系产生重复行所以要加DISTINCT或确认两表关系唯一。如果只是判断“存在性”EXISTS往往比DISTINCT去重更干净SELECT e.* FROM emp e WHERE EXISTS ( SELECT 1 FROM dept d WHERE d.id e.dept_id AND d.city 深圳 );三类写法最终效果可能完全一样但EXPLAIN显示的成本可能差异很大。我个人的习惯是存在性判断优先EXISTS取字段关联优先JOIN结果集小且典型的IN可以保留。不要为了统一风格硬套某种写法SQL优化本质是跟数据和索引结构打交道的活。6.4 优化招三MySQL 8.0用CTE让复杂子查询不再嵌套MySQL 8.0开始支持WITH公共表表达式也就是CTE。它可以把多次出现的子查询提取出来让SQL层级从“括号套括号”变成平铺的步骤。比如统计2023年消费总额高于平均值的客户WITH cust_total AS ( SELECT customer_id, SUM(amount) AS total_amount FROM orders WHERE order_date BETWEEN 2023-01-01 AND 2023-12-31 GROUP BY customer_id ) SELECT c.id, c.name, ct.total_amount FROM customer c JOIN cust_total ct ON c.id ct.customer_id WHERE ct.total_amount (SELECT AVG(total_amount) FROM cust_total);这里cust_total被使用了两次一次JOIN一次算平均。如果是传统写法要么把同一段GROUP BY子查询复制两遍要么再嵌套一层派生表可读性立刻下降。CTE的另一个价值是可以递归但递归一般用在对组织架构、父子层级这类数据的处理上业务场景相对少。如果你的生产环境还是MySQL 5.7那就继续用派生表注意给每个子查询加别名并且尽量在物化前把数据过滤干净避免临时表太大。7. 业务实战订单统计里的子查询全套用法7.1 场景一每个部门工资最高的员工假设表结构是emp(id, name, dept_id, salary)要查每个部门工资最高的员工。最直觉的写法是SELECT e1.name, e1.dept_id, e1.salary FROM emp e1 WHERE e1.salary ( SELECT MAX(e2.salary) FROM emp e2 WHERE e2.dept_id e1.dept_id );内层子查询对每个部门返回最高工资外层再按“工资等于部门最高工资”过滤。这样写结果可能包含多个并列最高的人如果业务要求每个部门只能出一条记录还要再用GROUP BY或窗口函数去重。这个场景同时用到了相关子查询和聚合函数是理解子查询执行逻辑很好的例子。7.2 场景二找出2023年消费总额超过全站客户平均消费额的客户这个需求要两层统计先算每个客户2023年的消费总额再算所有客户消费总额的平均值最后过滤。用派生表可以写成SELECT c.id, c.name, t.total_amount FROM customer c JOIN ( SELECT customer_id, SUM(amount) AS total_amount FROM orders WHERE order_date BETWEEN 2023-01-01 AND 2023-12-31 GROUP BY customer_id ) t ON c.id t.customer_id WHERE t.total_amount ( SELECT AVG(total_amount) FROM ( SELECT customer_id, SUM(amount) AS total_amount FROM orders WHERE order_date BETWEEN 2023-01-01 AND 2023-12-31 GROUP BY customer_id ) avg_t );如果觉得这段SQL太长把它拆成CTE就是前面第6节展示的写法。这里能明显看到子查询嵌套层次变多之后维护成本会上升所以能抽取的公共子查询建议尽早提取。尤其是在5.7环境里这种三层嵌套是绕不开的写法只能靠合理的缩进和注释让代码可读。7.3 场景三每个客户最近的一笔订单要查每个客户最近一笔订单常见子查询写法是SELECT o.id, o.customer_id, o.order_date, o.amount FROM orders o WHERE o.order_date ( SELECT MAX(o2.order_date) FROM orders o2 WHERE o2.customer_id o.customer_id );这是标准的相关子查询用法对外层每一行订单内层先找出同一个客户的最晚下单日期如果这一行正好是这个日期就保留。该写法在orders表的customer_id、order_date联合索引存在时会比较高效否则全表逐行扫内层数据量一大就容易被拖垮。如果只要每个客户一条结果还可以用窗口函数ROW_NUMBER()实现MySQL 8.0支持。子查询和窗口函数两者都能解窗口函数通常更简洁但子查询在MySQL 5.7也能跑兼容性更好。开发前先确认一下线上版本再决定用什么写法别等上线了才发现语法不兼容。7.4 场景四EXISTS替代IN的边界再举一个经常混用的场景。要查“有订单的客户”两种写法SELECT * FROM customer c WHERE c.id IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL); SELECT * FROM customer c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.id );如果orders表很大IN会把customer_id的列表物化出来再判断列表可能非常长EXISTS则对每个客户去orders表按索引查找通常更省内存。但如果orders表的customer_id上加了联合索引且MySQL优化器能把IN改写为semi-join两者差距可能不大。结论还是那句拿EXPLAIN说话参数不同执行计划会变不要背死口诀。最后分享一个自己用了很多年的习惯拿到一个查询需求先在纸上画数据流——先算哪张表得到什么中间结果再和哪张表关联。子查询写复杂之后最怕的就是连自己都搞不清内层返回的是单值还是列表。我通常会把子查询先写成独立SQL跑通之后再加进外层这样既能验证内层结果的正确性也能顺便确认字段数量和类型。希望这篇能帮你把MySQL里的子查询彻底捋顺少踩NULL的坑多写出看得懂、跑得快的SQL。 MySQL子查询全解析：分类、NULL陷阱与性能优化实战
写SQL绕不开子查询这一点我在MySQL上踩过的坑可以写一篇文章。前几年写业务统计一看到“找出每个部门工资最高的员工”“查没有订单的客户”这类需求我第一反应就是套一个子查询。结果有时候跑得飞快有时候直接把数据库拖慢还有一次NOT IN莫名查不到数据排查到凌晨才发现是子查询结果里带了NULL。后来我把MySQL里的子查询彻底研究了一遍才发现这东西的规则远比想象中清晰无非是返回几行几列、写在哪个位置、和外层查不查得到关系。这篇就把这些规则一次讲透从分类、位置、相关子查询、NULL陷阱到UPDATE/DELETE里的写法再到慢SQL优化全程用MySQL真实语法演示。适合刚上手的新手也适合那些SQL能跑通但说不清理所以然的同学。1. 子查询的四种分类标量、列、行、表1.1 先从“返回结果”看子查询子查询的本质就是一条完整的SELECT语句被塞进另一条SQL里。它执行完会产生一个结果集这个结果集长什么样决定了外面的表达式能不能接住它。MySQL里可以按返回内容把子查询分成四类标量子查询返回一行一列本质上就是一个普通的单值可以直接用在等于、大于、小于这些比较运算符旁边。列子查询返回一列多行值可以理解成一串列表典型搭配是IN、NOT IN、ANY、ALL。行子查询返回一行多列MySQL支持用行构造器做整体比较比如(a, b) (SELECT ...)。表子查询返回多行多列这种子查询没法放在WHERE里直接比只能放在FROM后面当一张临时表用也就是派生表。用表格看会更清楚类型返回结果典型写法位置语法要点标量子查询单行单列SELECT、WHERE、HAVING必须保证最多返回一行否则报错列子查询单列多行WHERE中搭配IN、ANY、ALL不能直接和、、连用行子查询单行多列WHERE中搭配行构造器字段个数和顺序必须完全一致表子查询多行多列FROM子句必须取别名这个分类建议直接当作判断标准看到一条SQL里出现了括号先别看逻辑先看括号里的SELECT返回几行几列你就知道它想干什么。这也是后面所有写法和优化的地基绝大多数子查询报错都源自“内外形状不匹配”。1.2 标量子查询的写法细节标量子查询是最好理解的因为它的结果可以当作一个列值来用。比如查工资高于全公司平均工资的员工SELECT name, salary FROM emp WHERE salary (SELECT AVG(salary) FROM emp);这里内层SELECT AVG(salary)返回一行一列就是全公司的平均工资。外层再把每个人的工资和它比较超过的就留下。语法上没有任何歧义执行顺序也很直观先算平均值再用这个平均值过滤外层数据整个过程只执行一次子查询性能风险很低。有一点必须注意标量子查询如果返回超过一行会直接报错Subquery returns more than 1 row。这是新手最容易踩的坑。比如下面这条SQL如果部门10里有三个员工内层就会返回三行工资外层直接崩溃SELECT name FROM emp WHERE salary (SELECT salary FROM emp WHERE dept_id 10);这种场景应该改用IN比如SELECT name FROM emp WHERE salary IN (SELECT salary FROM emp WHERE dept_id 10);反过来如果标量子查询返回0行结果就是NULL外层条件不会报错但WHERE比较结果是UNKNOWN行会被过滤掉。这一点后面讲NULL陷阱的时候会重点展开现在只要记住标量子查询不是“必须有值”而是“最多一行”。1.3 列子查询和行子查询一列值和一个元组列子查询返回的是一列值可以理解成一张竖着的列表。最常见的搭配就是IN和NOT IN。比如查所有在深圳部门上班的员工SELECT name FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE city 深圳);这里内层返回所有深圳部门的id外层判断员工的dept_id是否在这个id列表里。列表里如果有100个idIN判断本质就是dept_id 1 OR dept_id 2 OR ...的快捷写法。ANY和ALL也属于列子查询的搭档但它们不是“相等判断”而是带着比较运算符走的后面第4节讲NULL陷阱时我会专门说它们的坑。行子查询用得相对少但遇到“同时比多列”的场景非常省事。MySQL允许用行构造器把多个字段拼成一行再比较SELECT id, name FROM emp WHERE (id, name) (SELECT id, name FROM emp WHERE id 100);内层返回的是id100这一行的两列外层把每一行的(id, name)二元组和它做整体比较。这里要求字段数量、顺序完全一致少一个多一个都会报Operand should contain 1 column(s)一类的错误。这种写法在业务里确实用得少但要读懂别人代码看到这种括号包裹多个字段的形式时至少要知道它属于行子查询。1.4 表子查询必须放在FROM后面并取别名表子查询返回的是多行多列本质上就是一张临时表。这种子查询只能出现在FROM子句里并且必须取别名。比如按部门统计完人数后再关联部门表SELECT d.name, t.cnt FROM dept d JOIN ( SELECT dept_id, COUNT(*) AS cnt FROM emp GROUP BY dept_id ) t ON d.id t.dept_id;内层SELECT dept_id, COUNT(*) ... GROUP BY dept_id返回一张两列的临时表我用t给它起了别名外层再和dept表做JOIN。如果不取别名MySQL会直接报错Every derived table must have its own alias。这个报错可以说是派生表位置出现频率最高的错误了几乎没有之一。2. SELECT、FROM、WHERE、HAVING四个位置子查询的写法禁忌完全不同2.1 SELECT子句后的子查询想清楚它是“逐行执行”还是“执行一次”SELECT列表里放子查询本质是把子查询当作一个新列查出来。最常见的是放标量子查询。比如查员工基本信息的同时带出全公司最高工资SELECT name, salary, (SELECT MAX(salary) FROM emp) AS max_salary FROM emp;这个子查询不涉及外层MySQL一般会把它当作常量处理执行一次就好性能没问题。但如果你写的是相关子查询比如带出本部门最高工资SELECT name, salary, (SELECT MAX(salary) FROM emp e2 WHERE e2.dept_id e1.dept_id) AS dept_max FROM emp e1;这时候MySQL的执行逻辑就变成了外层emp表有多少行内层子查询就可能执行多少次。这就是后面要说的相关子查询。别在SELECT列表里写这种相关子查询去处理大表否则SQL大概率会变成慢SQL。我见过不止一次因为这种写法把响应时间从几十毫秒拉到几十秒的案例外层行数一大问题立刻暴露。2.2 FROM子句后的子查询MySQL把它当一张临时表FROM后面的括号子查询叫派生表MySQL会先执行它把结果放到内存或临时表里再交给外层做后续操作。MySQL 5.7之后对派生表有“合并”和“物化”两种处理方式能合并的会直接把子查询的SQL和外层SQL合并执行可以减少临时表开销不能合并的就物化成临时表物化时如果发现子查询结果集比较大甚至会在磁盘上生成临时表这时候性能就会明显下降。派生表有两条硬规矩一是必须取别名二是一般情况下不能引用外层的列。MySQL 8.0.14开始支持了一种叫LATERAL的派生表可以在内层引用外层列实现一些高级用法但这属于进阶话题业务上90%的派生表都不需要它。还有一个容易忽视的细节派生表里如果加了ORDER BY在外层没有LIMIT的情况下这个排序很可能被优化器丢掉因为MySQL认为既然外层要全量处理这张表派生表排不排序对最终结果没有影响。所以在派生表里写ORDER BY ... LIMIT n才有实际意义。2.3 WHERE子句里的子查询最常使用也最容易出错WHERE子句是子查询的主战场。它接受三类子查询但不同运算符接受的类型不一样比较运算符、、、、、只接受标量子查询也就是一行一列。IN、NOT IN、ANY、SOME、ALL接受列子查询。EXISTS、NOT EXISTS接受任意子查询因为它只看“有没有行返回”。行构造器比较(a, b) (SELECT ...)接受行子查询。比如前面案例里WHERE salary (SELECT AVG(salary) FROM emp)子查询是标量所以可以用但如果某个子查询返回一列你就不能直接写WHERE salary (SELECT salary FROM emp ...)因为MySQL不知道拿这一列怎么和一个值比大小。这种场景要么改成标量要么配合ANY、ALL使用WHERE salary ANY (SELECT salary FROM emp WHERE dept_id 10);意思是“大于部门10里任何一个人的工资”。注意 ANY和 ALL的语义完全不同 ALL是“大于部门10里所有人的工资”也就比最高工资还高。这两个运算在业务里确实用得少但一旦遇到语义搞反就很麻烦。2.4 HAVING子句里的子查询分组之后的过滤器HAVING是在GROUP BY之后执行的过滤条件所以它里面的子查询经常搭配聚合函数。一个典型场景是找出平均工资高于全公司平均水平的部门SELECT dept_id, AVG(salary) AS avg_sal FROM emp GROUP BY dept_id HAVING AVG(salary) (SELECT AVG(salary) FROM emp);内层子查询先算出全公司平均工资外层再按部门分组算平均工资最后用HAVING过滤。整个执行顺序是先执行子查询再分组聚合再HAVING过滤最后SELECT输出。理解这个顺序对排查“为什么HAVING里不能写别名”这类问题很有帮助因为SELECT列表的别名是在HAVING过滤之后才生成出来的HAVING阶段根本看不到别名。另外虽然ORDER BY里也可以放子查询但业务里极少用而且可读性差一般不建议学。子查询能出现的常规位置就是SELECT、FROM、WHERE、HAVING以及后面要说的UPDATE、DELETE、INSERT把这几类吃透就足够应付绝大多数开发场景了。3. 相关子查询与非相关子查询性能差异的核心来源3.1 非相关子查询先跑内层再跑外层如果子查询不引用外层任何字段它就是一个完全独立的SQLMySQL通常先执行它得到一个结果集再用这个结果集去处理外层。这种叫非相关子查询。比如SELECT name FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE city 上海);内层不依赖外层MySQL先拿到所有上海的部门id再对外层员工表做过滤。执行计划里它通常只算一遍性能相对可控。非相关子查询在EXPLAIN里的select_type一般是SUBQUERY或者MATERIALIZED前者代表一次性执行后者代表把结果物化成临时表后再参与外层计算无论哪种都不会因为外层行数多而重复执行内层。3.2 相关子查询外层每行都要执行一遍如果子查询里引用了外层的列它就不是独立查询了。比如经典场景“找出工资高于本部门平均工资的员工”SELECT e1.name, e1.salary FROM emp e1 WHERE e1.salary ( SELECT AVG(salary) FROM emp e2 WHERE e2.dept_id e1.dept_id );这里内层的e2.dept_id e1.dept_id引用了外层的e1.dept_id所以对外层emp表的每一行MySQL都要带着这一行的部门id去执行一次内层查询。外层有10万行内层就要执行10万次。这就是相关子查询性能差的根源不用怀疑就是字面意义上的“逐行执行”。MySQL优化器会尝试缓存一些重复子查询或者把EXISTS改写为semi-join但相关子查询仍然是慢SQL的高发区域。在EXPLAIN里非相关子查询的select_type通常是SUBQUERY或MATERIALIZED而相关子查询通常是DEPENDENT SUBQUERY看到DEPENDENT这个词就要警觉尤其当它出现要扫描的外层表行数特别大时基本可以断定这条SQL需要改造了。3.3 用EXISTS写相关子查询的正确姿势EXISTS是相关子查询最经典的搭档。它不管内层SELECT的是什么只看有没有返回行所以内层习惯写成SELECT 1。比如查那些在深圳有部门的员工相关子查询配合EXISTS可以这样写SELECT e.name FROM emp e WHERE EXISTS ( SELECT 1 FROM dept d WHERE d.id e.dept_id AND d.city 深圳 );这种写法等价于对每个员工去dept表里查是否有他所在部门且城市是深圳的记录有就保留。在dept表的id列有索引的前提下随着外层行数增加单次查询成本很低整体可能比大列表的NOT IN要稳。需要强调的是相关子查询并不总是坏选择。当内层表有合适的索引、外层表数据量不大时它的表现完全能接受真正要避免的是“外层大表 × 内层无索引且每次全表扫”的组合。判断依据还是EXPLAIN不要因为别人说EXISTS快就无脑改。4. NOT IN为什么查不出来子查询里的NULL值陷阱4.1 NOT IN遇到NULL结果直接变成空集这是子查询领域最著名的坑没有之一。假设我要查没有下过订单的客户SELECT * FROM customer c WHERE c.id NOT IN (SELECT customer_id FROM orders);如果orders表里customer_id这一列存在任何一条NULL值这条SQL查出来的结果就是空集哪怕明明有很多客户没下过单一条都不会返回。原因要从SQL的三值逻辑说起。SQL里的比较结果除了TRUE、FALSE还有UNKNOWN。x NOT IN (a, b, NULL)等价于x a AND x b AND x NULL。而任何值和NULL比较都会得到UNKNOWNAND链里一旦出现UNKNOWN整个表达式就不是TRUEWHERE就会把行过滤掉。换句话说只要子查询列表里混进一个NULLNOT IN就彻底废了。遇到这种情况解决问题的姿势有两个-- 姿势一内层先过滤掉NULL SELECT * FROM customer c WHERE c.id NOT IN ( SELECT customer_id FROM orders WHERE customer_id IS NOT NULL ); -- 姿势二换成NOT EXISTS天生免疫NULL SELECT * FROM customer c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.id );我的建议是凡是写NOT IN最好先确认子查询列上有没有NOT NULL约束没有就主动加IS NOT NULL或者干脆用NOT EXISTS虽然写法看起来长一点但语义最稳不会因为底层数据变化突然翻车。4.2 IN、ANY、ALL在NULL面前的表现IN的情况比NOT IN好一点但同样要留心。x IN (1, 2, NULL)等价于x 1 OR x 2 OR x NULL。如果x匹配到了1或2结果是TRUE能正常返回如果x一个都不匹配比如x3三个比较分别是FALSE、FALSE、UNKNOWNOR的结果是UNKNOWN这行照样被过滤。所以不能说IN遇到NULL就出错只能说遇到NULL时它不一定返回你预期的TRUE。ANY和ALL的NULL问题更隐蔽很多人根本没有意识到。x ANY (子查询)是OR逻辑只要集合里有任何一个非NULL值让x大于它结果就是TRUENULL影响不大。但x ALL (子查询)是AND逻辑只要集合里有任何一个NULLx NULL就是UNKNOWN整个AND链直接变UNKNOWN结果就不会返回行。所以凡是用 ALL、 ALL这类写法内层最好显式过滤掉NULLWHERE salary ALL ( SELECT salary FROM emp e2 WHERE e2.dept_id e1.dept_id AND e2.salary IS NOT NULL );4.3 判断NULL永远用IS NULL不要用等号这一条虽然不在子查询语法里但它是所有NULL坑的底层原因。SQL标准里任何值用、!、和NULL比较结果都是UNKNOWN不是TRUE或FALSE。只有IS NULL和IS NOT NULL是专门用来判断NULL的。这也是为什么内层过滤条件写WHERE customer_id ! NULL无法生效的原因它不会报错但也不会过滤掉任何东西NULL判断必须写成IS NOT NULL。所以检查子查询返回的内容是否包含NULL最快的办法就是看对应的表结构里该列有没有NOT NULL约束如果有说明这一列不可能有NULLNOT IN可以放心用如果没有就按上面的姿势处理。多花十秒钟查一下建表语句能省下半夜排查数据问题的功夫。5. INSERT、UPDATE、DELETE里怎么用子查询改了数据才知道的坑5.1 INSERT INTO ... SELECT把一张表的查询结果灌进另一张表这种写法本质就是“用查询结果批量插入”。比如把研发部门的员工复制到备份表INSERT INTO emp_bak (id, name, salary, dept_id) SELECT id, name, salary, dept_id FROM emp WHERE dept_id (SELECT id FROM dept WHERE name 研发部);这里SELECT id FROM dept返回标量部门id外层SELECT只取对应的员工。INSERT和SELECT的列数量、类型必须对齐否则MySQL会报列数不匹配的错误。如果只是想快速复制整表还可以用CREATE TABLE new_table AS SELECT ...但注意这种方式不会自动复制主键、索引等结构只复制数据后续该加的索引还得手动补。5.2 UPDATE里用子查询注意SET和WHERE中的关联给指定部门的员工统一加薪10%UPDATE emp SET salary salary * 1.1 WHERE dept_id (SELECT id FROM dept WHERE name 研发部);这个子查询引用的是dept表没有和emp表产生“同表更新”冲突所以能正常执行。相关子查询也可以出现在SET里比如把每行工资更新成本部门平均工资UPDATE emp e SET e.salary ( SELECT AVG(salary) FROM emp e2 WHERE e2.dept_id e.dept_id );这个写法在MySQL里实际是允许的但逻辑上很危险因为它是“把每行工资改成部门平均值”执行期间表数据在变不同行的结果可能互相影响。真要更新同表数据建议先计算好目标值再用JOIN或临时表方式处理不要在这种场景里追求花活。5.3 同表更新的经典报错You cant specify target table for update in FROM clause直接更新emp表时如果子查询的FROM里又出现empMySQL会报错。比如UPDATE emp SET salary (SELECT MAX(salary) FROM emp) WHERE id 100;这个SQL会被MySQL拒绝报You cant specify target table emp for update in FROM clause。原因是子查询的FROM和UPDATE的目标表是同一张表MySQL不允许这种直接引用避免逻辑混乱。绕法也简单把子查询再包一层派生表让MySQL先物化成临时表再去更新UPDATE emp SET salary ( SELECT m.max_salary FROM (SELECT MAX(salary) AS max_salary FROM emp) m ) WHERE id 100;内层SELECT MAX(salary) FROM emp被包进(SELECT ...) m派生表后MySQL会先生成一张临时表然后外层再从中取值这样就不存在“直接引用目标表”的问题了。这个技巧在MySQL 5.7和8.0都适用用途虽然偏门但遇到这种报错时非常好使。5.4 DELETE里用子查询同表删除的限制同样存在DELETE和UPDATE的限制类似。比如按条件删除低薪员工如果直接写DELETE FROM emp WHERE id IN (SELECT id FROM emp WHERE salary 3000);同样会报You cant specify target table emp for update in FROM clause。绕法还是包一层派生表DELETE FROM emp WHERE id IN ( SELECT id FROM ( SELECT id FROM emp WHERE salary 3000 ) tmp );这里三层结构看起来繁琐但意义很明确最内层查出要删的id中间层把结果变成临时表外层再DELETE。业务上如果经常要“按同表条件删数据”不如先想想能不能用JOIN、临时表或用主键列表分两次执行可读性会更好也能减少一次性锁大量行的风险。6. 子查询变慢SQL用EXPLAIN定位再按这三招优化6.1 先学会看EXPLAIN里的select_type遇到子查询性能问题第一步永远是执行EXPLAIN。下面这张表列出和子查询直接相关的select_typeselect_type表示的含义常见风险SUBQUERY非相关子查询执行一次低MATERIALIZED子查询被物化成临时表且可能自动建索引中等物化有开销DEPENDENT SUBQUERY相关子查询外层每行都可能执行一次高UNCACHEABLE SUBQUERY子查询无法缓存每次都要重新算高DERIVEDFROM子句后的派生表中等看是否合并如果你的EXPLAIN里出现DEPENDENT SUBQUERY且外层表扫描行数很大那就要想办法优化。出现DERIVED且物化时不走索引也要检查派生表上是否有合适的索引可以利用。MySQL 8.0还提供了EXPLAIN ANALYZE可以直接输出每个步骤实际消耗的时间、行数比普通EXPLAIN更直观用法是把原来的EXPLAIN换成EXPLAIN ANALYZE执行。它会真正跑一遍查询线上大查询慎用但排查慢SQL很有价值。6.2 优化招一IN和EXISTS别再死记口诀网上流传很久的说法是“IN表大就慢EXISTS表大就快”这在MySQL 5.6之后已经不够准确。MySQL优化器会把部分IN子查询改写为semi-join也会把EXISTS改写成其他形式。所以判断标准只有一个拿出EXPLAIN看实际执行计划。如果两条SQL执行计划一样性能就不会有本质区别没必要为了所谓的“经验”强行改写法。不过有两个方向可以作为初筛逻辑如果内层子查询结果集很小用IN一般没问题MySQL甚至可能物化子查询后自动加索引如果外层表小、内层表大且内层关联列刚好有索引用EXISTS相关子查询通常每行查询成本极低表现可能更好。关键还是内层的关联列必须有索引无论IN还是EXISTS内层的WHERE关联字段如果没索引都要走全表扫描性能必然差。6.3 优化招二子查询改写为JOIN很多子查询本质上就是一次关联查询改写为JOIN后执行计划往往更清晰。比如“查在深圳有部门的员工”-- 子查询版 SELECT e.* FROM emp e WHERE e.dept_id IN (SELECT id FROM dept WHERE city 深圳); -- JOIN版 SELECT DISTINCT e.* FROM emp e JOIN dept d ON e.dept_id d.id WHERE d.city 深圳;JOIN版的好处是优化器可以用匹配算法直接关联两表而不是先物化一个临时结果集。代价是JOIN可能因一对多关系产生重复行所以要加DISTINCT或确认两表关系唯一。如果只是判断“存在性”EXISTS往往比DISTINCT去重更干净SELECT e.* FROM emp e WHERE EXISTS ( SELECT 1 FROM dept d WHERE d.id e.dept_id AND d.city 深圳 );三类写法最终效果可能完全一样但EXPLAIN显示的成本可能差异很大。我个人的习惯是存在性判断优先EXISTS取字段关联优先JOIN结果集小且典型的IN可以保留。不要为了统一风格硬套某种写法SQL优化本质是跟数据和索引结构打交道的活。6.4 优化招三MySQL 8.0用CTE让复杂子查询不再嵌套MySQL 8.0开始支持WITH公共表表达式也就是CTE。它可以把多次出现的子查询提取出来让SQL层级从“括号套括号”变成平铺的步骤。比如统计2023年消费总额高于平均值的客户WITH cust_total AS ( SELECT customer_id, SUM(amount) AS total_amount FROM orders WHERE order_date BETWEEN 2023-01-01 AND 2023-12-31 GROUP BY customer_id ) SELECT c.id, c.name, ct.total_amount FROM customer c JOIN cust_total ct ON c.id ct.customer_id WHERE ct.total_amount (SELECT AVG(total_amount) FROM cust_total);这里cust_total被使用了两次一次JOIN一次算平均。如果是传统写法要么把同一段GROUP BY子查询复制两遍要么再嵌套一层派生表可读性立刻下降。CTE的另一个价值是可以递归但递归一般用在对组织架构、父子层级这类数据的处理上业务场景相对少。如果你的生产环境还是MySQL 5.7那就继续用派生表注意给每个子查询加别名并且尽量在物化前把数据过滤干净避免临时表太大。7. 业务实战订单统计里的子查询全套用法7.1 场景一每个部门工资最高的员工假设表结构是emp(id, name, dept_id, salary)要查每个部门工资最高的员工。最直觉的写法是SELECT e1.name, e1.dept_id, e1.salary FROM emp e1 WHERE e1.salary ( SELECT MAX(e2.salary) FROM emp e2 WHERE e2.dept_id e1.dept_id );内层子查询对每个部门返回最高工资外层再按“工资等于部门最高工资”过滤。这样写结果可能包含多个并列最高的人如果业务要求每个部门只能出一条记录还要再用GROUP BY或窗口函数去重。这个场景同时用到了相关子查询和聚合函数是理解子查询执行逻辑很好的例子。7.2 场景二找出2023年消费总额超过全站客户平均消费额的客户这个需求要两层统计先算每个客户2023年的消费总额再算所有客户消费总额的平均值最后过滤。用派生表可以写成SELECT c.id, c.name, t.total_amount FROM customer c JOIN ( SELECT customer_id, SUM(amount) AS total_amount FROM orders WHERE order_date BETWEEN 2023-01-01 AND 2023-12-31 GROUP BY customer_id ) t ON c.id t.customer_id WHERE t.total_amount ( SELECT AVG(total_amount) FROM ( SELECT customer_id, SUM(amount) AS total_amount FROM orders WHERE order_date BETWEEN 2023-01-01 AND 2023-12-31 GROUP BY customer_id ) avg_t );如果觉得这段SQL太长把它拆成CTE就是前面第6节展示的写法。这里能明显看到子查询嵌套层次变多之后维护成本会上升所以能抽取的公共子查询建议尽早提取。尤其是在5.7环境里这种三层嵌套是绕不开的写法只能靠合理的缩进和注释让代码可读。7.3 场景三每个客户最近的一笔订单要查每个客户最近一笔订单常见子查询写法是SELECT o.id, o.customer_id, o.order_date, o.amount FROM orders o WHERE o.order_date ( SELECT MAX(o2.order_date) FROM orders o2 WHERE o2.customer_id o.customer_id );这是标准的相关子查询用法对外层每一行订单内层先找出同一个客户的最晚下单日期如果这一行正好是这个日期就保留。该写法在orders表的customer_id、order_date联合索引存在时会比较高效否则全表逐行扫内层数据量一大就容易被拖垮。如果只要每个客户一条结果还可以用窗口函数ROW_NUMBER()实现MySQL 8.0支持。子查询和窗口函数两者都能解窗口函数通常更简洁但子查询在MySQL 5.7也能跑兼容性更好。开发前先确认一下线上版本再决定用什么写法别等上线了才发现语法不兼容。7.4 场景四EXISTS替代IN的边界再举一个经常混用的场景。要查“有订单的客户”两种写法SELECT * FROM customer c WHERE c.id IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL); SELECT * FROM customer c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.id );如果orders表很大IN会把customer_id的列表物化出来再判断列表可能非常长EXISTS则对每个客户去orders表按索引查找通常更省内存。但如果orders表的customer_id上加了联合索引且MySQL优化器能把IN改写为semi-join两者差距可能不大。结论还是那句拿EXPLAIN说话参数不同执行计划会变不要背死口诀。最后分享一个自己用了很多年的习惯拿到一个查询需求先在纸上画数据流——先算哪张表得到什么中间结果再和哪张表关联。子查询写复杂之后最怕的就是连自己都搞不清内层返回的是单值还是列表。我通常会把子查询先写成独立SQL跑通之后再加进外层这样既能验证内层结果的正确性也能顺便确认字段数量和类型。希望这篇能帮你把MySQL里的子查询彻底捋顺少踩NULL的坑多写出看得懂、跑得快的SQL。