最近在整理一套MySQL笔记时有人问我一个问题统计每个分类下销量最高的商品用子查询好还是JOIN好这个问题看似简单但牵扯出的东西一点也不浅。实际上刚上手的人往往连子查询和普通嵌套SELECT有什么关系都没搞清就开始到处套用结果遇到NULL值、遇到性能毛刺就彻底懵了。这篇就专门讲清楚MySQL里的子查询sub query。我把子查询分成四类分别讲语法、执行逻辑、常见报错和优化方式再把UPDATE/DELETE中的子查询、EXISTS家族、以及和JOIN的选型对比一并拆开。内容基于MySQL 8.0适合已经会写简单SQL、但想在复杂查询上打通任督二脉的人。1. 子查询到底是什么——从一个真实报错说起先说个我在答疑时高频遇到的场景。有人写了一条统计SQL想找出所有下单超过3次的用户SELECT user_id, COUNT(*) AS order_cnt FROM orders GROUP BY user_id HAVING COUNT(*) 3;然后他嫌GROUP BYHVAING不够直观想用子查询来写SELECT * FROM users WHERE user_id IN ( SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(*) 3 );这段能跑通但是很多人并不清楚它在MySQL内部到底发生了什么更不知道这条SQL和上面那条有什么区别。1.1 大白话理解子查询子查询说穿了就是一条SQL里面再嵌套另一条完整的SQL。外层查询叫主查询内层括号里的那段叫子查询。MySQL先看内层括号算出一个中间结果再拿这个结果去跟外层表做匹配。你可以把它理解成一个分步计算的过程。就好比你做饭先问冰箱里有什么食材子查询再决定做什么菜主查询。MySQL不像你它不会猜它只会老实巴交地把子查询结果先算出来然后往上套。这里最关键的一点子查询必须放在括号里而且不同位置、不同返回形态的子查询用起来有完全不同的规则。我把常见写法分为四种后面逐一拆。1.2 四种形态标量子查询、列子查询、行子查询、表子查询准备两张测试表下面所有示例都用这两张表CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), class_id INT, score DECIMAL(5,2) ); CREATE TABLE class ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) );这四种形态的对比可以用一张表先建立整体认识类型子查询返回结果搭配的操作符典型场景标量子查询单个值一行一列, , , , , 拿某个值去比较列子查询一列多行IN, ANY, SOME, ALL拿多个值去匹配行子查询一行多列, , IN整体比较一行字段表子查询多行多列FROM、JOIN把子查询当临时表用一个常用但容易混淆的底层逻辑是标量子查询返回的是一行一列所以它本质上就是一个值可以直接放在SELECT后面当做一个虚拟列也可以放在WHERE中参与比较运算。而列子查询和行子查询已经类似小数据集了只能配合专用操作符使用。2. 四类子查询的写法与适用边界这一节把每种形态的语法和注意点全部过一遍。每种我都配上真实可运行的SQL顺手把常见的坑标出来。2.1 标量子查询只有一格结果最常见的写法。子查询结果必须是一行一列也就是只有一个返回值。举例查询张三所在班级的名称。步骤是先用子查询查出张三的class_id再把这个class_id拿到class表里查名字SELECT name AS class_name FROM class WHERE id ( SELECT class_id FROM student WHERE name 张三 );这条SQL的执行路径是先跑子查询SELECT class_id FROM student WHERE name 张三假设返回的是2然后外层再执行SELECT name FROM class WHERE id 2。假如子查询返回了多行比如有两个学生都叫张三那整个SQL直接报错Subquery returns more than 1 row。这是标量子查询最容易踩的雷——业务数据设计上名字唯一和数据库的约束唯一是两码事。没建唯一索引之前你永远不能假设它只返回一行。标量子查询还能放在SELECT后面当输出列。比如统计每个学生的分数和他的班级平均分差了多少SELECT name, score, (SELECT AVG(score) FROM student s2 WHERE s2.class_id s1.class_id) AS class_avg FROM student s1;这种写法很实用但要注意性能每一行外层结果MySQL都可能要执行一次子查询。如果子查询没走索引等于双层循环数据量一大就崩。解决办法是给class_id建索引。2.2 列子查询给IN喂一串值子查询返回一列多行时最常见的搭配就是IN。拿上面的例子变一下查询所有三班学生的姓名SELECT name FROM student WHERE class_id IN ( SELECT id FROM class WHERE name 三班 );这一类的执行逻辑是先把class表里所有name等于三班的id集合查出来比如3, 5, 8然后用WHERE class_id IN (3,5,8)去student表过滤。除了IN还有ANY、SOME、ALL。这三个放在一起说-- 查询分数大于二班任意一个学生的学生 SELECT name, score FROM student WHERE score ANY ( SELECT score FROM student WHERE class_id 2 ); -- 查询分数大于二班所有学生的学生 SELECT name, score FROM student WHERE score ALL ( SELECT score FROM student WHERE class_id 2 ); ANY的意思是大过最小的那个就满足 ALL的意思是大过最大的那个才算满足。SOME是ANY的别名完全等价。不过讲实话日常开发中ANY和ALL用得不算多大部分场景用 MAX(...)或 MIN(...)子查询更直观可读性也更好。2.3 行子查询一行多列整体对比MySQL允许子查询返回一行但有多列然后跟外层的多列做整体比较。这种写法平时不常见但有一种场景特别好用——处理多条字段联合匹配。比如要查和班里某个学生同班且同名的人。正常情况下你要写两个AND条件而行子查询可以这样SELECT id, name, class_id FROM student WHERE (class_id, name) ( SELECT class_id, name FROM student WHERE id 8 );这条SQL的意思是先查出id等于8的学生的class_id和name再去找所有class_id和name都和他相同的学生。MySQL 8.0完全支持这种写法。不过说句实在的行子查询的适用范围相对窄很多情况下用两个标量子查询或者直接JOIN也能搞定。它的价值在于让SQL语义更紧凑适合那种一组字段必须同时相等的比较逻辑。2.4 表子查询当临时表用子查询返回多行多列时可以直接放到FROM后面把这个结果当成一张临时的派生表来JOIN。这是目前最推荐使用的一种高级写法因为它的可读性不错MySQL优化器对它的处理也相对成熟。举个例子查每个班级的总分只保留总分超过600的班级并展示班级名和总分SELECT c.name, t.total_score FROM class c JOIN ( SELECT class_id, SUM(score) AS total_score FROM student GROUP BY class_id ) t ON c.id t.class_id WHERE t.total_score 600;这里括号里的子查询结果就是一张派生的临时表别名为t。外层再和class做JOIN、做WHERE过滤。注意几个细节派生表必须起别名。在MySQL 8.0中FROM后面的子查询如果没有别名直接报错。很多从别的数据库转过来的人第一次写就会撞上。派生表的列名以子查询里写的别名或列名为准。在MySQL 8.0.14之前派生表是不支持用外面列的即不能写相关派生表现在虽然支持LATERAL派生表但日常用到的概率不大先了解即可。用表子查询最大的好处是把复杂的聚合步骤拆成逻辑上的中间层SQL结构清晰排错也方便。很多统计后再筛选的需求用这种写法比HAVING嵌套更直观。3. EXISTS家族被低估的过滤利器聊子查询如果只聊IN和FROM就漏掉了最锋利的一把刀EXISTS。EXISTS不算什么复杂语法但在判断存在性的场景里它往往比IN更高效、更安全。3.1 EXISTS和NOT EXISTS的语义EXISTS只关心子查询有没有返回行——返回了就是TRUE没返回就是FALSE不关心子查询查出什么列。所以子查询的SELECT部分可以随便写甚至写SELECT 1也行。典型场景查出所有下过单的用户。用EXISTS写是这样SELECT id, name FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id );这里有个很重要的执行逻辑EXISTS子查询通常被MySQL优化成一种半连接semi-join。它不需要把orders表的所有user_id先查出来放到内存里而是拿外层users表的每一行去内层找找到一条就停可以用索引快速判断。所以当orders表很大、而users表中命中的人比较少时EXISTS往往比IN表现更好。对应的反面NOT EXISTS找没下过单的用户SELECT id, name FROM users u WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id );3.2 EXISTS派生出来的相关子查询上面这两个都是相关子查询内层子查询引用了外层表的列u.id。这意味着内层不能独立执行必须一行一行拿外层的数据进来关联。在这里不得不提醒一下相关子查询如果没索引就是典型的N1查询问题。外层有多少行内层就执行多少次。在只扫必要的行、且内层能快速命中索引的前提下EXISTS表现很好但一旦内外层数据都大且无索引性能就会雪崩。怎么确认索引是否生效直接看执行计划。用EXPLAIN查一下看到select_type是DEPENDENT SUBQUERY时就要重点检查内层关联字段的索引了。这个我在后面章节展开。3.3 EXISTS vs IN怎么选才靠谱关于EXISTS和IN的对比网上说法很乱。我基于MySQL 8.0实测给你个结论在大多数场景下MySQL 8.0的优化器会把IN和EXISTS都改写成半连接来执行两者性能差异很小。选型应该优先考虑语义清晰程度和NULL值的处理方式。选型建议数据量小、子查询结果简单稳定的用IN可读性好。子查询表特别大而且外层表过滤后行数少的用EXISTS。涉及NOT IN时优先考虑NOT EXISTS因为NOT IN遇到NULL值会出问题详见第5章。如果只需要判断存在性不需要具体数据EXISTS语义最贴合。4. UPDATE/DELETE 中的子查询怎么用才不踩雷翻热搜词的时候看到一大串mysql中更新子查询mysql update语法之类的问题说明目前很多人会在更新和删除语句里用子查询。这块确实坑最多必须单独说。4.1 UPDATE配合子查询的正确姿势需求把每个班级的平均分写到class表的avg_score字段里。UPDATE class c SET avg_score ( SELECT AVG(score) FROM student s WHERE s.class_id c.id );逻辑上没有毛病子查询返回的就是一个标量值可以直接赋值。但在MySQL里跑这条SQL的时候大概率会报一个很经典的错误ERROR 1093 (HY000): You cant specify target table class for update in FROM clause这个错误的意思是你不能在更新一张表的同时从同一张表里去查数据。上面的SQL里UPDATE目标是class而子查询里直接或间接又引用了class的数据MySQL在语法层面就禁止了这种自引用。解决办法很经典把子查询再包一层派生表让MySQL认为数据是从临时结果来的和原表无关了。UPDATE class c SET avg_score ( SELECT t.avg FROM ( SELECT class_id, AVG(score) AS avg FROM student GROUP BY class_id ) t WHERE t.class_id c.id );需要注意上面的子查询实际没有直接查询class表只是引用了c.id的外层关联MySQL通常能放过。但如果你是想在同一张表上做取最高分那一行然后更新的操作比如把每个学生自己的成绩更新为全班最高分那就必须先包派生表打破自引用限制。4.2 DELETE中的子查询和它的自引用陷阱DELETE与UPDATE的规则大同小异。目标表同样不能在子查询中直接出现。经典需求删除成绩低于班级平均分的学生记录。DELETE FROM student WHERE score ( SELECT AVG(score) FROM student );这立刻撞上错误1093。解决套路和UPDATE一样包一层派生表。DELETE FROM student WHERE score ( SELECT avg_score FROM ( SELECT AVG(score) AS avg_score FROM student ) t );4.3 从热搜词看大家常踩的两个雷热搜词里还有两个高频问法mysql中更新子查询和mysql字段为关键字。这两个其实是一家人——很多人写UPDATE子查询时用了update、order、group这类字段名导致语法解析失败。举个例子UPDATE student SET order 1 WHERE id 5;order是MySQL的保留字不能直接做列名。要么给字段加反引号UPDATE student SET order 1 WHERE id 5;要么在建表时就避开这些保留字这个才是最根本的解法。字段命名这事情看起来小但一旦混进了保留字后面每个SQL都得戴着手铐跳舞太难受了。5. 子查询的隐形陷阱与常见误区实战中真正让人头疼的不是子查询不会写而是明明返回值、逻辑都对结果却莫名其妙或者性能崩了还不知道原因。下面五个坑每一个我都亲眼见过。5.1 NULL值导致的全军覆没这是子查询里最经典的坑专门坑NOT IN。先看一条SQLSELECT name FROM student WHERE class_id NOT IN ( SELECT class_id FROM student WHERE score 90 );如果子查询的结果里包含任何一个NULL这条SQL的结果就会一行都查不出来。原因在SQL的三值逻辑class_id NOT IN (1, 2, NULL)会被解释成class_id 1 AND class_id 2 AND class_id NULL而class_id NULL的结果是UNKNOWN不是TRUE所以所有行都被过滤掉了。解决方案一是用NOT EXISTS替代二是想办法把NULL过滤掉WHERE class_id NOT IN ( SELECT class_id FROM student WHERE score 90 AND class_id IS NOT NULL );个人建议凡是判断不属于某一集合的场景一律改成NOT EXISTS这是最省心的习惯。同样的坑在ANY/ALL中也可能出现但因为ANY/ALL用的少没NOT IN这么普遍。5.2 ORDER BY和LIMIT在子查询里的失效问题很多人喜欢在子查询里做排序或限量比如SELECT * FROM student WHERE class_id IN ( SELECT class_id FROM student GROUP BY class_id ORDER BY AVG(score) DESC LIMIT 3 );这条能跑但请注意它的逻辑和顺序。MySQL优化器对子查询有改写机制不保证子查询里的排序一定生效尤其当排序结果不影响最终语义时优化器可能直接忽略ORDER BY。如果你依赖子查询的排序结果来决定取哪几条再用IN去匹配很可能会得到预期以外的数据。更稳妥的做法把排序限制包成派生表再JOINSELECT s.* FROM student s JOIN ( SELECT class_id FROM student GROUP BY class_id ORDER BY AVG(score) DESC LIMIT 3 ) t ON s.class_id t.class_id;这样子查询作为一个显式的派生表排序结果是被明确保留的不会因为优化器的改写而丢失。5.3 IN列表过大的性能问题IN后面的列表不是无限放大的。虽然MySQL 8.0对IN列表的行数没有硬性上限但当列表涨到几千甚至上万时查询优化器生成的执行计划会非常复杂CPU和内存消耗都很大最终查询时间会明显变慢。核心指标参考IN列表元素数量查询性能表现 100正常无需关注100 ~ 1000开始出现执行计划膨胀建议检查 1000性能明显下降考虑改用JOIN或临时表解决思路很简单如果数据来自另外一张表直接用JOIN代替IN子查询如果数据是外部系统传进来的ID列表考虑拆批查询或者先写入临时表再JOIN。5.4 嵌套层数过深MySQL理论上支持非常深的子查询嵌套但这个理论上没有实际意义。Q嵌套三层的SQL你真的能一眼看出它在算什么吗我的经验是超过两层的子查询可读性急剧下降而且排查问题的成本翻倍。如果你发现自己在写第三层嵌套性价比最高的做法是停下来把中间结果拆成临时表或者拆成多个视图。SQL是给人读的不是纯粹写给MySQL的。子查询再强也只是手段不是目的。5.5 相关子查询的性能隐患前面提到了相关子查询这里再深化一下。相关子查询的select_type显示为DEPENDENT SUBQUERY意味着MySQL对外层每一行都要执行一次内层查询。假设外层1万行内层执行1万次。如果内层的关联字段没有索引每次都是一次全表扫描这性能基本没法看。优化方向有两个给内层子查询的关联字段加索引。这是成本最低的优化。把相关子查询改写成JOIN或派生表。后者在MySQL 8.0里往往能获得更好的执行计划。至于为什么有人会觉得子查询就是慢多半不是子查询本身慢而是相关子查询导致的N1全表扫描在慢。6. 子查询 vs JOIN到底该选谁最后绕回开头那个问题。子查询和JOIN在某种程度上是同一个需求的两套写法选哪个不取决于哪个高级取决于场景和数据特征。这一节专门把两者的思维方式讲透。6.1 两者是同一个问题的不同视角子查询的思考方式是分步计算先算出内层结果再拿这个结果往下走。JOIN的思考方式是横向连接先把两张或多张表按关联条件拼成一张大宽表再做过滤和聚合。举例查每个学生的成绩和班级平均分。用JOIN可以这样SELECT s.name, s.score, t.avg_score FROM student s JOIN ( SELECT class_id, AVG(score) AS avg_score FROM student GROUP BY class_id ) t ON s.class_id t.class_id;这就是用表子查询做派生表再JOIN。你会发现到了一定复杂度之后子查询和JOIN往往是配合使用而不是二选一。真正该纠结的只是在具体一个过滤步骤里用哪种表达方式。6.2 子查询更占优势的三类场景第一类是标量比较场景。比如查分数高于平均分的学生WHERE score (SELECT AVG(score) FROM student)这种写法直观到什么程度JOIN反而要先聚合再关联绕了一大圈。第二类是存在性判断。比如查所有下过单的用户EXISTS子查询语义最直接而且优化器很容易把它优化成半连接。第三类是隔离性需求关联的中间结果需要重复使用。用一个派生表把结果算好外层多次引用比每次都写一段嵌套SQL清爽太多。6.3 JOIN更占优势的三类场景第一类是返回关联表的具体字段。比如要展示订单和商品名称、用户姓名肯定直接用JOIN把多张表拼起来没必要用子查询。第二类是一张表的字段不够需要另一张表的数据参与过滤和输出。JOIN的横向扩展优势在这里很明显。第三类是数据量非常大的场景。MySQL优化器对JOIN的优化相对成熟合理索引下JOIN往往比复杂的嵌套子查询更可控。6.4 一个实用的选型判断清单把本节内容收敛成一套可以直接套用的判断方法可以按顺序问自己四个问题子查询返回的是一个值吗——是用标量子查询最直观。我需要子查询结果里的字段参与外层输出吗——需要优先JOIN。只是在做存在性判断吗——是优先EXISTS避开NOT IN的NULL坑。数据量会不会很大——会优先JOIN或者物化成临时表别让优化器去猜。我在实际项目里还有一个习惯先用子查询把逻辑快速写通再Explain查看执行计划发现性能瓶颈后再改写成JOIN。这种做法特别适合不熟悉表数据分布的阶段——先求正确再求性能。回到开头那个每个分类下销量最高的商品问题。用子查询配合关联派生表是最清晰的写法SELECT * FROM products p JOIN ( SELECT category_id, MAX(sales_cnt) AS max_sales FROM products GROUP BY category_id ) t ON p.category_id t.category_id AND p.sales_cnt t.max_sales;子查询负责找到每个分类的最大值JOIN负责拿原表去匹配两边都不复杂合起来刚好是你要的结果。有时候最好的SQL不是某个单一技巧的炫技而是把多种思路恰当地组合起来。