刚带完一个项目组里新人写的统计报表SQL把服务器拖到CPU飙到90%我看了一眼三条子查询嵌套加两个IN完全没有连接条件。这种场景我见过太多次了很多人MySQL基础语法都背下来了一碰上复合查询就露馅。这篇就把复合查询和内外连接一次说透从执行逻辑到实战写法都过一遍新手能直接照着写有经验的也能查漏补缺。1. 为什么单表查询不够用数据关系拆解与组合逻辑先想一个问题你为什么要用连接查询因为业务数据不是孤立的。比如学生选课系统学生信息存在student表课程信息存在course表成绩存在sc表这是典型的第三范式设计目的就是减少数据冗余。但查询的时候你不可能查三张表然后自己拿内存去拼数据库提供了一系列语法让你的查询直接一步到位。很多人在这个环节犯的错误是明明只需要查一张表偏偏去JOIN另一张表结果数据翻倍。比如查一个学生的基本信息你不需要关联任何表直接WHERE student_id 1就结束了。复合查询的价值在于你需要的结果横跨多张表且这些表之间存在关联字段。我之前画过一张关系图说明这个问题。学生表的sno是主键sc表的sno是外键course表的cno是主键sc表的cno也是外键。你要查出哪个学生选了哪门课成绩是什么就必须通过sc这张中间表把student和course串起来。这就是复合查询最基础的场景多表通过连接字段组合成一张逻辑上的大表然后在这张大表上做筛选、排序、分组。另一种复合查询来自单表内的复杂逻辑。比如你要找出每一科成绩都高于平均分的学生或者查工资比本部门平均工资高的员工。这类问题需要子查询和聚合函数配合你不可能用一条简单的SELECT完成必须层层嵌套。MySQL执行子查询的时候有些情况是逐个外层行去匹配内层结果集有些情况是先把子查询结果物化理解这个区别是后面优化查询的关键。再往深一层复合查询还承担着一个容易被忽略的功能形状转换。比如你有一张学生表每个学生一行你想知道每个分数段有多少人这是聚合但你想要的是每个分数段的平均年龄这就变成先聚合再关联的复合问题。没有复合查询你得写多个SQL然后在应用层做二次加工传输数据量大了应用服务器内存也被白白消耗。所以复合查询不只是语法技巧更是减少数据传输、加速业务逻辑的重要手段。我也遇到过很多面试者上来跟我背左连接右连接的语法但问他为什么会得到重复数据就答不上来。这就是因为没理解连接的本质。连接的底层是笛卡尔积的过滤形式如果不带连接条件两张表各100条数据结果就是10000条这个爆炸式的增长是很多线上事故的根源。一句话总结单表查询解决的是一张表里怎么筛怎么算的问题复合查询解决的是多张表怎么拼、怎么过滤、怎么在拼完之后继续处理的问题。理解这个定位学后面的语法才有方向感。2. 内连接与外连接的底层差异从结果集形态反推SQL写法连接查询是复合查询的地基。内连接INNER JOIN、左连接LEFT JOIN、右连接RIGHT JOIN在教科书里讲得头头是道但落笔写SQL的时候很多人还是会犹豫。我带你从结果集形态的角度重新理解这个问题。2.1 内连接的核心只有匹配上才保留内连接是最严格的连接方式。它返回的每一行都必须同时满足左右两张表的连接条件。比如查选修了课程的学生名单用内连接就只返回真正有选课记录的学生没选课的不会出现。SELECT student.sname, sc.score FROM student INNER JOIN sc ON student.sno sc.sno;这种写法的逻辑语义是两边都有的才是有效的。内连接的等价写法是用WHERE很多老工程师习惯这么写SELECT student.sname, sc.score FROM student, sc WHERE student.sno sc.sno;这两种写法执行计划在MySQL 8.0里没有本质区别但ON写法语义更清晰而且改成外连接的时候只需要改JOIN关键字不用挪条件位置。我建议新项目统一用INNER JOIN ON代码可读性高一个档次。2.2 外连接的核心保留主表的孤儿数据外连接才会让很多人困惑。LEFT JOIN左连接的意思很直白左边这张表的每一行必须出现在结果里右边表有匹配就带上没匹配就置NULL。用选课场景举例。你要出一份全体学生名单带选课情况没选课的学生也要列出来成绩位置写NULL。用INNER JOIN就漏人LEFT JOIN才对SELECT student.sname, sc.score FROM student LEFT JOIN sc ON student.sno sc.sno ORDER BY student.sno;反过来RIGHT JOIN就是右边表的行全部保留。现实中我几乎不用RIGHT JOIN因为把表的顺序换一下LEFT JOIN就能解决何必增加阅读负担。MySQL也允许RIGHT JOIN但团队规范里直接禁用统一用LEFT JOIN。外连接最容易翻车的地方是连接条件里的过滤和WHERE里的过滤混用。看这两个SQL的区别-- 场景1连接时只匹配及格成绩不及格的显示NULL SELECT student.sname, sc.score FROM student LEFT JOIN sc ON student.sno sc.sno AND sc.score 60; -- 场景2先连接再过滤掉不及格记录等价于内连接 SELECT student.sname, sc.score FROM student LEFT JOIN sc ON student.sno sc.sno WHERE sc.score 60;场景2会把没选课的学生也过滤掉因为NULL 60结果是NULL被WHERE过滤了。很多人栽在这个细节上以为写了LEFT JOIN就万事大吉结果在WHERE里一加右表字段的过滤条件左连接直接变内连接数据就少了。2.3 全外连接MySQL为什么不支持Oracle有FULL OUTER JOINMySQL至今不支持。原因是全外连接就是LEFT JOIN和RIGHT JOIN的并集MySQL觉得这种需求可以直接用UNION实现。真遇到我要两张表的所有数据匹配上就合并不然就各显示各的这种极端需求用UNION-- 模拟FULL OUTER JOIN SELECT student.sname, sc.score FROM student LEFT JOIN sc ON student.sno sc.sno UNION SELECT student.sname, sc.score FROM student RIGHT JOIN sc ON student.sno sc.sno;UNION会自动去重如果想去掉去重开销并且能接受重复行用UNION ALL。不过说句实在话我在真实业务里几乎没碰到过必须要全外连接才能解决的场景一般当你觉得需要FULL JOIN的时候先停下来想想是不是表结构设计出了问题。2.4 连接查询的笛卡尔积陷阱连接不带条件就是灾难。两张几百条数据的表不带条件连接一下轻轻松松生成几十万行中间结果。我记得有一次排查线上慢查询看到一个JOIN语句压根没写ON直接把两张几十万行的表做了笛卡尔积数据库差点OOM。排查时EXPLAIN一看type是ALLrows是几十万乘以几十万直接怀疑人生。所以写连接查询第一条铁律没有ON条件的JOIN等于自杀。第二条铁律连接条件要出现在ON里不要全扔到WHERE里。虽然执行器可能转化等价但ON和WHERE的语义边界模糊会让代码极难维护。3. 复合查询实战拆解一个覆盖子查询、聚合与外连接的完整案例理论说完了上实战。我带过很多实习生发现他们最大的问题是单条SQL语法都懂但面对一个复杂的查询需求无从下手。这里给你一个可复制的拆解方法论。3.1 案例需求描述给定一个学生-课程-成绩数据库三张表结构如下CREATE TABLE student ( sno INT PRIMARY KEY, sname VARCHAR(20) NOT NULL, age INT, dept VARCHAR(20) ); CREATE TABLE course ( cno INT PRIMARY KEY, cname VARCHAR(30) NOT NULL, credit INT ); CREATE TABLE sc ( sno INT, cno INT, score DECIMAL(5,2), PRIMARY KEY (sno, cno) );需求一查询每个学生的选课门数和平均成绩没选课的学生也要显示平均成绩保留两位小数。需求二查询每门课程的选课人数没被选过的课程保留显示选课人数为0。这两个需求里其实都用到了外连接聚合分组排序几乎一份SQL就把复合查询的大半考点覆盖了。第一个需求用LEFT JOIN从student出发因为要以学生为主体列出所有学生所以学生表必须是主表SELECT student.sno, student.sname, COUNT(sc.cno) AS course_count, ROUND(COALESCE(AVG(sc.score), 0), 2) AS avg_score FROM student LEFT JOIN sc ON student.sno sc.sno GROUP BY student.sno, student.sname ORDER BY avg_score DESC;注意这里几个细节。第一COUNT要用sc.cno而不是COUNT()如果学生没选课LEFT JOIN产生的右表字段是NULLCOUNT(sc.cno)只数非NULL值所以没选课的学生course_count是0而COUNT()会把那行也算进去返回1就错了。这是最经典的坑。第二AVG(sc.score)对没选课的学生结果也是NULL先用COALESCE转成0再ROUND保留两位小数。第三GROUP BY后面写了student.sno和student.sname两个字段因为sname虽然在功能上依赖sno但MySQL的ONLY_FULL_GROUP_BY模式下要求SELECT的非聚合字段必须出现在GROUP BY里直接两个都写上最省心。第二个需求换主表以课程为主体SELECT course.cno, course.cname, COUNT(sc.sno) AS student_count FROM course LEFT JOIN sc ON course.cno sc.cno GROUP BY course.cno, course.cname ORDER BY student_count DESC, course.cno;这不就是把上一套模板翻转一下吗理解了主表决定谁必须全部出现这类需求秒解。3.2 子查询的三种形态与典型场景连接解决横向拼接子查询解决纵向嵌套。子查询放的位置不同执行逻辑也有差异。标量子查询子查询只返回一个值直接放在SELECT后面充当一列。比如查询每个学生的成绩和全部学生的平均成绩差值SELECT sname, score, score - (SELECT AVG(score) FROM sc) AS diff_from_avg FROM student LEFT JOIN sc ON student.sno sc.sno;这种写法胜在直观但注意子查询是逐行执行的如果外层结果集很大性能会很差。好在这个场景里的子查询与外表无关MySQL 8.0会缓存为常量执行一次即可。IN/NOT IN子查询最常见也最容易踩坑。查询没有选任何课程的学生SELECT sno, sname FROM student WHERE sno NOT IN (SELECT sno FROM sc);逻辑上没毛病但有个隐患如果子查询返回的结果集中包含NULLNOT IN整个条件会变成不是任何值最终一行都不返回。这是NULL三值逻辑导致的经典问题。解决方案是改NOT EXISTS语义等价且不会踩NULL的坑性能通常也不会更差SELECT sno, sname FROM student s WHERE NOT EXISTS ( SELECT 1 FROM sc WHERE sc.sno s.sno );FROM子句派生表把子查询当临时表用。比如查询选课超过2门的学生SELECT t.sno, t.course_count FROM ( SELECT sno, COUNT(*) AS course_count FROM sc GROUP BY sno HAVING COUNT(*) 2 ) t ORDER BY t.course_count DESC;这种写法把你脑子里先汇总再根据汇总结果筛选的过程直接翻译给MySQL。逻辑层次清晰也是面试官最爱问的代码之一。3.3 多表连接的顺序与结果集形态判断当连接的表超过两张比如查每个学生的学号、姓名、所选课程名、成绩需要student、sc、course三表连动SELECT student.sno, student.sname, course.cname, sc.score FROM student JOIN sc ON student.sno sc.sno JOIN course ON sc.cno course.cno ORDER BY student.sno, course.cno;三个表连接时关键是要理解执行过程是逐步的先把student和sc按sno连接得到中间结果集再把中间结果集和course按cno连接。你脑子里可以先想清楚第一步的结果长什么样再想第二步这样写起来不容易错。如果想把没选课的学生也带上同时课程名没有就显示NULL第一个JOIN换成LEFT JOIN即可。多表连接时每个JOIN的语义要单独判断别指望一个LEFT JOIN保住后面所有表的数据。比如上面SQL里的第二个JOIN是内连接那么即使某个学生没选课结果里他是不会出现的如果他选了一门不存在的课程外键约束缺失导致的脏数据那第二段JOIN的右表匹配不上但在内连接下这个学生又会被丢弃掉。3.4 HAVING和WHERE到底怎么区分WHERE在分组前过滤HAVING在分组后过滤。查选了至少2门课且平均分大于85的学生SELECT sno FROM sc WHERE score 60 -- 先过滤掉不及格的记录 GROUP BY sno HAVING COUNT(*) 2 AND AVG(score) 85;这个顺序是一名合格SQL开发最基本的肌肉记忆。但我想给你一个更实用的判别方法WHERE里不能放聚合函数因为这时聚合还没发生HAVING里可以放聚合函数也可以放普通字段的条件。如果你在HAVING里写了跟聚合无关的普通字段条件比如HAVING sno 10纯粹是因为忘了放到WHERE里这不会报错但效率低也会让阅读代码的人困惑。当年我面试过一个人让他写查询找出平均分大于80的学生他写了WHERE AVG(score) 80直接报错然后一脸茫然不知道错在哪。这就是对过滤时机没有概念。只要脑子里有WHERE是先筛行再分组HAVING是先分组再筛组这条线这类错误永远不可能发生。4. 学生成绩系统的复合查询进阶从常规统计到排名分析基本语法掌握之后真正让复合查询拉开差距的是窗口函数和一些高级聚合技巧。MySQL 8.0开始支持窗口函数这也让很多原本要写半天子查询的逻辑变得一目了然。4.1 窗口函数解决分组排名问题需求查询每门课程的成绩排名。不用窗口函数的话你得先查出去重后的成绩集合再用相关子查询数一数有多少个成绩大于等于当前成绩非常痛苦。用窗口函数一行搞定SELECT cno, sno, score, RANK() OVER (PARTITION BY cno ORDER BY score DESC) AS rk FROM sc ORDER BY cno, rk;RANK()遇到并列成绩会跳号比如两个并列第一下一个直接是第三。想不跳号用DENSE_RANK()想按行号排用ROW_NUMBER()。这三个函数面试基本必问。窗口函数的核心逻辑是PARTITION BY决定分组ORDER BY决定组内排序函数在每一组内部独立计算。再比如查询每个学生所有课程中的最高分SELECT sno, cno, score FROM ( SELECT sno, cno, score, ROW_NUMBER() OVER (PARTITION BY sno ORDER BY score DESC) AS seq FROM sc ) t WHERE t.seq 1;这是典型的先打行号再取序号为1的套路也是取每组Top N的通用模板非常实用。4.2 自连接同一张表当成两半看有一类问题必须用自连接。比如查询比王小明年龄大的学生如果你不知道王小明的主键ID可以先查出他的年龄再比较。但更统一的写法是自连接SELECT a.sname AS student_name, a.age AS student_age FROM student a JOIN student b ON a.age b.age WHERE b.sname 王小明;把同一张表起两个别名一个当主表一个当参照表按条件连接。再比如查成绩比自己选的这门课平均分高的记录也可以用自连接配合聚合SELECT s1.sno, s1.cno, s1.score FROM sc s1 LEFT JOIN ( SELECT cno, AVG(score) AS avg_score FROM sc GROUP BY cno ) s2 ON s1.cno s2.cno WHERE s1.score s2.avg_score;这里子查询先按课程分组算平均值再把每条成绩记录和对应课程的平均值比较。不要觉得自连接简单很多复杂业务报表的底层都是它。4.3 聚合路径的拆解GROUP_CONCAT做行转列还有一个场景经常被忽略想把某个学生的所有课程成绩拼成一行展示比如张三语文90、数学85、英语92。MySQL提供了GROUP_CONCAT函数SELECT student.sname, GROUP_CONCAT(CONCAT(course.cname, :, sc.score) ORDER BY course.cno SEPARATOR 、) AS course_scores FROM student JOIN sc ON student.sno sc.sno JOIN course ON sc.cno course.cno GROUP BY student.sno, student.sname;这个函数在处理展示型需求时极其好用比你在应用层循环拼接省无数事情。GROUP_CONCAT默认最大长度是1024个字符超出会截断真遇到长内容可以会话级调大group_concat_max_len参数。4.4 交叉表的场景一个数据透视的替代方案MySQL没有专门的PIVOT语法但可以通过CASE WHEN加聚合实现行列转换。比如查每个学生各门课的分数语文、数学、英语各成一列SELECT student.sname, MAX(CASE WHEN course.cname 语文 THEN sc.score END) AS chinese_score, MAX(CASE WHEN course.cname 数学 THEN sc.score END) AS math_score, MAX(CASE WHEN course.cname 英语 THEN sc.score END) AS english_score FROM student LEFT JOIN sc ON student.sno sc.sno LEFT JOIN course ON sc.cno course.cno GROUP BY student.sno, student.sname;MAX配合CASE是一种定向取数因为每个学生每门课只有一条记录MAX不会真的对多行求最大值而是把对应那一行的值挑出来。这个写法在报表场景出现频率极高务必掌握。唯一要小心的是如果一名学生同一门课有多次考试记录CASE会匹配多行这时聚合函数MAX会真的选最大值实际业务里要按业务口径思考取哪个值。5. 优化与避坑EXPLAIN、JOIN选择中的真实经验SQL能跑出结果只是第一步跑得快才是硬道理。很多线上查询慢不是数据库配置问题而是SQL写法不友好。这一节我把自己这些年最常踩的坑和压箱底的经验写出来。5.1 用EXPLAIN判断连接顺序与索引利用任何一条连接查询我都建议先跑一下EXPLAINEXPLAIN SELECT student.sname, sc.score FROM student LEFT JOIN sc ON student.sno sc.sno WHERE student.dept 计算机;EXPLAIN输出里最关键的是type字段。从好到差依次是system const eq_ref ref range index ALL。ALL代表全表扫描基本就是性能炸弹。ref代表通过非唯一索引访问eq_ref是唯一索引访问const是主键直接匹配都算优秀。还有一个重要信息是rows估算需要扫描的行数。连接查询的优化器会根据统计信息决定哪个表做驱动表、按什么顺序连接。内连接时优化器通常选择小表驱动大表因为这决定了中间结果集的大小。但外连接就没那么灵活主表是逻辑上固定的驱动顺序基本定死。所以如果你发现LEFT JOIN很慢第一件事检查ON条件字段是否有索引。连接字段没索引是最常见的慢查询原因。sc表的sno和cno是外键字段但有些建表脚本不会自动给外键字段建索引。没有索引MySQL就得对右表做全表扫描来匹配每一个左表的行这种场景下性能直接崩。补充上索引ALTER TABLE sc ADD INDEX idx_sno (sno); ALTER TABLE sc ADD INDEX idx_cno (cno);加完索引再用EXPLAIN看type从ALL变refrows从几万降到几十效果立竿见影。5.2 子查询性能陷阱IN、EXISTS和JOIN怎么选很多教程说EXISTS比IN快这个说法在特定历史版本下成立但MySQL 5.6之后优化器针对半连接做了大量优化IN、EXISTS、JOIN在大多数简单场景下执行计划会被优化为同一种方式。真正拉开差距的是数据分布和是否使用索引。一个我实测过的案例查有选课记录的学生。-- 方式1: 用JOIN DISTINCT SELECT DISTINCT student.sno, student.sname FROM student JOIN sc ON student.sno sc.sno; -- 方式2: 用IN SELECT sno, sname FROM student WHERE sno IN (SELECT sno FROM sc); -- 方式3: 用EXISTS SELECT sno, sname FROM student s WHERE EXISTS (SELECT 1 FROM sc WHERE sc.sno s.sno);在sc表sno有索引的前提下方式2和方式3都会被优化器重写成半连接性能几乎无差异。方式1因为有DISTINCT需要在连接后做去重如果sc表数据量大反而可能更慢。所以我现在的经验是先写语义清晰的版本再用EXPLAIN验证不要凭感觉优化。子查询里还有一个高频坑MySQL 8.0之前子查询的派生表FROM子句里的子查询默认不可以使用索引会被物化成临时表。比如你在子查询里对结果集做了GROUP BY再跟主表连接外层连接条件无法命中派生表索引就会全表扫描。MySQL 8.0引入派生表合并和物化优化情况好一些但最好还是用EXPLAIN确认。5.3 排序与分组的最佳实践ORDER BY和GROUP BY也很容易让查询变慢。特别是分组前用了大范围扫描再排序sort buffer不够时就要走磁盘临时文件甚至产生隐式排序。一个实用的优化思路是让索引覆盖排序字段但实际中更常用的是减少参与排序的数据量。先过滤再排序再连接。比如-- 推荐先限制范围再排序 SELECT sc.sno, sc.score FROM sc JOIN student ON student.sno sc.sno WHERE student.dept 计算机 ORDER BY sc.score DESC LIMIT 100;LIMIT也很关键数据库拿到结果后可以只保留前100行不用把整个结果集排完。没有LIMIT的排序全量数据都要进sort buffer内存不够就爆临时表。5.4 NULL值过滤与外连接结果的核对方法最后说一个所有新手都会撞上的墙外连接后的NULL过滤。确认过滤条件放ON还是WHERE有个简单粗暴的检验方法——自己先心算一遍最小样例。比如student表只有张三、李四两条数据sc表只有张三的一条选课记录分别把条件放在ON里和WHERE里按行推演一遍结果马上就明白差异。MySQL的NULL和三值逻辑TRUE、FALSE、UNKNOWN是SQL最反直觉的地方没有捷径只有多推演多测试。再分享一个土办法每次写完涉及外连接的复杂SQL我都习惯用一个已知的小数据集验证结果数量。比如student表共5人LEFT JOIN后结果应该至少5行如果查出4行说明一定有过滤条件把某位没选课的学生干掉了。这个验证习惯帮我抓出过很多逻辑漏洞强烈建议你也养成。6. 几个容易让你查不到数据的隐藏杀手除了连接和子查询本身还有一批看起来很基础、但坑人无数的细节。写复合查询的时候它们会放大你的逻辑错误让你调试半天也摸不着头脑。6.1 字符集和排序规则不一致两表连接的字段如果是字符串类型但表的字符集不同比如一张表是utf8mb4_general_ci另一张是utf8mb4_0900_ai_ciMySQL无法直接使用索引只能逐行转换再比较导致性能骤降。更离谱的是同样的两个字在不同排序规则下比较结果可能不同。建表时统一字符集和排序规则能避免大量这类问题。6.2 连接字段的数据类型不一致比如学生的sno一张表是INT另一张表设计成了VARCHAR。MySQL连接时会对数据进行隐式类型转换。字符串转数字比较一旦某行存了非数字内容转换后可能是0匹配结果完全出乎意料。这类问题EXPLAIN都不一定立刻看出来但数据一多就出现莫名缺失。排查方法很粗暴对比两张表的字段定义确保连接字段类型一致。6.3 三值逻辑导致判断失效外连接产生NULL之后任何与NULL的比较都会返回NULL也就是FALSE。你写ON sc.score NULL结果是NULL等于什么都没有匹配上。要判断NULL必须用IS NULL。这条规则我强调过无数次但每次代码评审还是能在新人的SQL里看到等号比较NULL。6.4 连接条件里做函数运算在ON条件里写函数等于告诉MySQL索引失效。比如ON YEAR(student.birthday) YEAR(course.start_date)两者都无法走索引。正确的做法是在应用层算好年份范围或者设计冗余字段别在连接条件里动函数。这个原则和WHERE里对字段用函数导致索引失效是同一个道理只是ON里失效更隐蔽因为EXPLAIN看着还是有连接操作但扫描行数已经爆炸了。每次做完这些检查我都会在团队内部强调一句复合查询写不出来不要硬写先查表结构再画关系图最后动笔写SQL。顺序反了调试时间至少翻三倍。你现在对照自己的项目把上面这些坑过一遍应该能救回不少慢查询。