尧图网页设计 
1. 从单表到多表为什么复合查询才是SQL的试金石先问个问题你在写业务代码的时候有没有遇到过这种情况——数据就在那几张表里摆着单表查询写得飞起可一旦需要把员工表和部门表串起来、找出每个部门工资最高的那个人或者查谁拿的工资比本部门平均值高当场就卡壳了我见过不少候选人CRUD写了好几年一到复合查询就露馅IN和EXISTS分不清自连接更是想都不敢想。说句实在话SQL的入门是单表但真正的分水岭就在复合查询。MySQL的复合查询说白了就是三件事多表查询JOIN、自连接、子查询。这三件事单独拆开都不难难的是把它们组合起来解决真实业务问题以及在写之前想清楚我要的数据到底怎么拼出来。这篇文章我打算用一个贯穿全文的案例来拆解这三个知识点员工表、部门表、薪资变动表。这个模型几乎覆盖了绝大多数复合查询场景也是面试里出现频率最高的表结构。如果你是刚把单表查询练熟的新手这篇文章能帮你把多表思路给理顺如果你已经在写业务SQL我建议重点看自连接和子查询的优化部分那里有几个我踩了无数坑才总结出来的经验。2. 复合查询的地基搞懂连接查询的底层逻辑2.1 笛卡尔积所有人踩过的第一个坑多表查询的第一课不是JOIN怎么写而是笛卡尔积。这是理解一切连接查询的底层钥匙。所谓笛卡尔积就是两张表的数据做全排列组合。员工表有10条记录部门表有5条记录不做任何条件直接查两个表结果就是50条。这个数字看着不大但换成100万行的订单表和10万行的用户表来一次那就是100亿行MySQL当场直接卡死也不奇怪。我第一次写多表查询时就吃过这个亏写了个SELECT * FROM emp, dept结果刷出来一堆莫名其妙的重复数据当时还以为MySQL坏了。后来才明白连接查询的本质就是先产生笛卡尔积再通过连接条件筛选出有效行。所以在我的实际使用习惯里几乎不写逗号形式的隐式连接全部用明确的JOIN语法-- 隐式连接不推荐 SELECT * FROM emp, dept WHERE emp.deptno dept.deptno; -- 显式连接推荐 SELECT * FROM emp INNER JOIN dept ON emp.deptno dept.deptno;两者的执行结果完全一样但显式JOIN的语义更清楚连接条件和过滤条件也能分开写。尤其当SQL语句长到几十行的时候显式JOIN的可读性优势是碾压级的。2.2 连接查询家族INNER JOIN、LEFT JOIN、RIGHT JOIN怎么选连接查询最核心的就是这几种连接方式连接方式返回结果类比INNER JOIN两个表中都能匹配上的行交集LEFT JOIN左表全部 右表匹配上的行左表全保留RIGHT JOIN右表全部 左表匹配上的行右表全保留CROSS JOIN所有行两两组合笛卡尔积这里有一个非常关键的思路先分清谁是主表。LEFT JOIN左边是主表右边的表哪怕匹配不上左边的数据也得保留没匹配上的右边字段用NULL填充RIGHT JOIN反过来。举个例子我想查每个部门的员工数量包括没有员工的空部门这个包括空部门就决定了部门表必须是主表SELECT d.deptno, d.dname, COUNT(e.empno) AS emp_count FROM dept d LEFT JOIN emp e ON d.deptno e.deptno GROUP BY d.deptno, d.dname;如果用INNER JOIN空部门根本不会出现——因为它没有员工匹配不上直接就被过滤掉了。我见过太多人用INNER JOIN写完统计数才发现空部门不见了然后一脸懵地在排查为什么数据变少。这就是为什么我一直强调动手写JOIN之前先想清楚你的结果集里谁必须是完整的。这个完整的表就是主表主表决定连接方向。2.3 等值连接之外的边角料非等值连接除了员工表和部门表这种用deptno等值关联的场景还有一种情况经常被忽略——非等值连接。什么叫非等值连接就是连接条件不是而是、、BETWEEN这类范围判断。最经典的例子是工资等级表-- 工资等级表 salgrade -- grade, losal, hisal SELECT e.ename, e.sal, s.grade FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;每个员工的工资落在哪个等级区间就把等级带出来。这个场景在报表统计里非常常见比如用户等级划分、VIP梯度计算、订单金额分层。由于等级表和员工表之间没有共同的业务ID只能用范围匹配所以非等值连接是唯一解法。记住一个判断标准连接条件字段是两个表都有的业务ID用等值连接连接条件是一个表的字段落在另一个表的某个区间里用非等值连接。3. 自连接的精髓一张表当成两张表用3.1 员工和上级一个模型的两种视角自连接是很多人的噩梦但理解之后你会发现它实在太巧妙了。自连接的场景有个共同特点同一张表里一行数据和另一行数据之间存在关联关系。最经典的例子就是员工表里的上下级关系-- emp表结构empno, ename, mgr上级的员工编号 -- 查每个员工的姓名以及他上级的姓名 SELECT worker.ename AS employee_name, manager.ename AS manager_name FROM emp worker LEFT JOIN emp manager ON worker.mgr manager.empno;这里的核心技巧就一条给同一张表起两个不同的别名把它当成两张独立的表来操作。worker代表员工视角manager代表上级视角连接条件worker.mgr manager.empno就是在说这个员工的上级编号等于另一个视角里某条记录的员工编号。我第一次讲这个知识点的时候有朋友问这不是自欺欺人吗它物理上就是一张表啊。其实不然SQL里表名加别名之后优化器会把它们当作两个独立的逻辑数据集来处理。你只需要在脑海里把它想象成——MySQL创建了两张内容一模一样、但名字不同的虚拟表副本剩下的连接逻辑和普通两表连接完全一致。3.2 为什么这里必须用LEFT JOIN继续看上面的例子。如果老板PRESIDENT没有上级他的mgr字段就是NULL。如果用INNER JOIN老板这条记录会因为worker.mgr manager.empno匹配不上NULL而被丢掉。但业务上的需求是查每个员工以及他的上级老板也是员工啊凭什么把他丢了所以此时必须用LEFT JOIN以员工表为主表保证所有员工都出现在结果里老板的上级字段显示NULL语义恰好就是这个人是最高领导没有上级。这个细节就是区分菜鸟和老手的地方。写SQL不仅要让结果对还要能说清楚为什么这个连接方向是对的。面试的时候把这个点讲透基本就能证明你是真用过而不是背过。3.3 自连接的另一个经典场景连续记录匹配自连接不只用于上下级关系。我举一个业务开发里更常见的场景。有一个打卡记录表字段是员工ID和打卡日期。现在要查连续三天都有打卡记录的全勤员工。如果不用自连接这个需求怎么实现窗口函数可以但很多老版本的MySQL或者某些团队约定不用窗口函数那么自连接就是最直接的方案SELECT DISTINCT a.emp_id FROM attendance a JOIN attendance b ON a.emp_id b.emp_id AND b.att_date DATE_ADD(a.att_date, INTERVAL 1 DAY) JOIN attendance c ON a.emp_id c.emp_id AND c.att_date DATE_ADD(a.att_date, INTERVAL 2 DAY);这个SQL的思路是a代表第一天b代表第二天c代表第三天。一个人同时拥有这三天记录就是连续三天打卡。把三张虚拟表通过日期错位关联起来一次匹配就把连续性问题解决了。动手写自连接的时候有个非常管用的心法找到那个把两行数据关联起来的业务纽带。在上下级场景里纽带是mgr在连续打卡场景里纽带是日期相差一天。这个纽带找到了自连接就写出来一大半了。4. 子查询嵌套的世界里藏着一整条语法链4.1 从标量子查询开始一个值引发的查询子查询从使用位置来分最常用的有三种SELECT后面、FROM后面、WHERE后面。最简单的是标量子查询它的特点是返回一行一列也就是一个确定的值。比如查每个员工的姓名和所在部门名称除了用JOIN还可以这样写SELECT e.ename, (SELECT d.dname FROM dept d WHERE d.deptno e.deptno) AS dept_name FROM emp e;这个子查询在SELECT子句里对每一条emp记录执行一次拿到对应的部门名称。感受一下主查询有多少行这个子查询就可能被执行多少次这就是相关子查询的特征——内层查询引用了外层查询的字段这里的e.deptno。标量子查询的使用限制很严必须确保它只返回一个值如果子查询返回多行MySQL直接报错Subquery returns more than 1 row。所以在写标量子查询前你得确保连接条件能唯一确定一行。不能保证唯一性的时候老老实实去用JOIN或者加LIMIT 1。从性能角度我个人的习惯是能用JOIN解决的优先用JOIN。标量子查询可读性虽好但在大表上性能可能不太好后面专门讲优化的时候会细说。4.2 IN、ANY、ALL操作符决定子查询的语义当子查询返回的是一列多行的时候就需要在WHERE里配合操作符使用。这里最容易混淆的就是IN、ANY、ALL三兄弟。IN是最常用的语义是匹配集合中的任意一个-- 查在研发部和市场部工作的员工 SELECT ename, job FROM emp WHERE deptno IN ( SELECT deptno FROM dept WHERE dname IN (研发部, 市场部) );ANY和ALL则用于比较运算。拿 ANY(...)和 ALL(...)来说前者表示大于子查询结果中的任意一个约等于大于最小值后者表示大于子查询结果中的全部约等于大于最大值-- 查工资高于任何一个部门平均工资的员工 SELECT ename, sal FROM emp WHERE sal ANY ( SELECT AVG(sal) FROM emp GROUP BY deptno ); -- 查工资高于所有部门平均工资的员工 SELECT ename, sal FROM emp WHERE sal ALL ( SELECT AVG(sal) FROM emp GROUP BY deptno );说实话ANY和ALL在工作里用得不多因为多数情况下可以用MIN/MAX或者EXISTS改写。但它们考的正是对子查询结果集的理解——子查询不是只能返回一个值它能返回一组值而这一组值是怎么参与外层比较的就取决于操作符。4.3 相关子查询 vs 不相关子查询理解执行顺序的关键如果说子查询有一个必须搞懂的概念那一定是相关子查询和不相关子查询的区别。这决定了你写的SQL会不会爆表、会不会扫出来一堆没用的数据。不相关子查询内层查询和外层查询没有关联可以先独立执行结果是一个固定的集合外层再拿这个集合去过滤。比如上面那个IN的例子子查询查部门表不依赖外层任何字段就是先查一次拿到部门编号集合再执行外层。相关子查询内层查询引用了外层查询的字段所以每一行外层记录都要带着自己的值去执行一次内层查询。比如上面那个标量子查询的例子每一条员工记录都要执行一次找部门名称的子查询。区分这两者的意义在于理解性能。不相关子查询执行一次就缓存结果相关子查询相当于每行执行一次数据量大时可能非常慢。所以写相关子查询的时候一定要确认内层查询走索引否则就是一场灾难。4.4 EXISTS与IN的相爱相杀EXISTS是另一个高频操作符它的语义是存在即可不关心子查询返回什么列只关心有没有行。从执行逻辑上说EXISTS一般比IN更高效尤其是子查询结果集非常大、外层表相对较小的时候。经典案例查有实际订单记录的客户-- IN写法 SELECT c.customer_id, c.customer_name FROM customers c WHERE c.customer_id IN ( SELECT o.customer_id FROM orders o ); -- EXISTS写法 SELECT c.customer_id, c.customer_name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.customer_id );两者结果一样但EXISTS的判断逻辑是对每一条客户记录去orders表里找有没有匹配行找到一个就立即停止。而IN是先把orders表的所有customer_id全部查出来形成一个大集合再把客户表每条记录去这个大集合里做查找。所以在MySQL 5.7及以下版本里EXISTS通常更稳妥。但到了MySQL 8.0优化器做了很多改版IN在某些场景下也可能被优化成半连接semi-join性能不差多少。我的建议是先写语义清晰的版本再用EXPLAIN看执行计划有性能瓶颈再改不要一开始就烧脑优化。4.5 FROM子句里的子查询派生表的玩法子查询放在FROM子句里结果就成了一个临时表也叫派生表。这个位置的子查询可以把复杂的聚合逻辑先算好再跟别的表做连接逻辑层次非常清晰。比如先算出每个部门的平均工资再和员工表关联查出每个员工薪资与部门均值的差距SELECT e.ename, e.sal, dept_avg.avg_sal, ROUND(e.sal - dept_avg.avg_sal, 2) AS diff FROM emp e JOIN ( SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno ) dept_avg ON e.deptno dept_avg.deptno;FROM子句里的子查询本质上是在SQL执行的早期就被物化materialize成一个临时表后续所有操作都基于这个临时表。这里有一个很重要的注意点派生表必须要有别名这是MySQL的语法强制要求不写别名直接报错。这种写法特别适合先缩小数据范围再关联的场景。比如先按订单明细聚合出销售额再关联商品表补全商品名称。把复杂问题拆成先处理内层再处理外层两步逻辑一下就清晰了。5. 实战演练三个综合场景串起所有知识点5.1 场景一找出各部门工资最高的人这个场景几乎是面试复合查询的必考题。它的坑在于如果直接按部门分组求最高工资你再把员工表JOIN回来的时候会遇到一个问题——最高工资的值能拿到但对应的员工信息姓名、职位会被GROUP BY搞得很难拿。推荐做法先用子查询算出每个部门的最高工资再用这个结果集去关联员工表把匹配的员工捞出来SELECT e.deptno, e.ename, e.sal FROM emp e JOIN ( SELECT deptno, MAX(sal) AS max_sal FROM emp GROUP BY deptno ) t ON e.deptno t.deptno AND e.sal t.max_sal;这个方案同时用到了派生表、多表连接、聚合函数三层嵌套一气呵成。理解这个SQL复合查询的一半功力就到手了。注意如果同一个人在不同的部门里或者同一个部门有多个人工资并列最高这个SQL会把所有并列的人都查出来这是符合业务期望的。5.2 场景二找到工资比本部门平均工资高的员工这是典型的相关子查询场景。不相关子查询没法做因为每个部门的平均工资不一样必须带着当前行的部门编号去算对应部门的平均值SELECT e.ename, e.sal, e.deptno FROM emp e WHERE e.sal ( SELECT AVG(sal) FROM emp WHERE deptno e.deptno );这个SQL执行的时候外层每一行员工记录都拿到自己的deptno去内层算一遍这个部门的平均工资然后再比较。逻辑很直观性能嘛——员工表只有几千行无所谓如果有百万行这个SQL可能就吃力了。优化思路先按部门把平均值算出来物化成临时表再关联比较也就是用上面场景一的派生表思路改写。5.3 场景三查出连续两次加薪的员工结合薪资变动表字段empno、change_date、new_sal想查哪些员工在相邻两个月里连续调过薪。这里的相邻就是自连接的典型场景SELECT DISTINCT a.empno FROM salary_change a JOIN salary_change b ON a.empno b.empno AND b.change_date DATE_ADD(a.change_date, INTERVAL 1 MONTH);把薪资变动表当成两张表一次变动是a下一次变动是b通过员工相同日期相差一个月来定位。这就是自连接日期函数联合作用的经典写法。这三个场景覆盖了多表查询、自连接、子查询的全部核心用法。我强烈建议你拿着这组SQL去自己的本地库跑一遍把结果集打开一行行对照着看比看十遍理论都管用。SQL这东西光看是学不会的得亲手拉数据才有手感。6. 复合查询的性能底线哪些写法要命哪些写法保命6.1 先看EXPLAIN再聊性能我先说一句现在MySQL 8.0的优化器已经比前几个版本智能多了很多早年间的铁律现在都要打个问号。所以无论网上怎么说动手前先跑EXPLAIN看执行计划这才是最靠谱的判断依据。常用的执行计划指标type字段从好到差大致是const、eq_ref、ref、range、index、ALL。看到ALL就要警惕是否全表扫描。key字段实际用到的索引如果是NULL说明没走索引。rows字段预估扫描的行数数字越大越危险。Extra字段出现Using temporary或者Using filesort时要留意可能是有排序或分组没走索引。6.2 子查询的优化能物化就物化能改JOIN就改JOINMySQL 8.0对子查询做了很多优化比如把IN子查询改为半连接把FROM子句里的派生表做物化并加索引。但我实际测试下来复杂的相关子查询在数据量起来之后还是比较容易成为瓶颈的因为它们每行执行一次的特性很难被完全优化掉。一个比较稳的经验是查询的底层数据量小几千行以内子查询怎么写都无所谓可读性优先。数据量到几十万行以上优先尝试改写为JOIN。JOIN的执行引擎对连接算法的优化非常成熟Nested Loop Join、Hash Join比相关子查询更可控。如果确实需要保留子查询确保被子查询扫描的表上关联字段有索引。一个典型的改写例子查没有订单的客户用NOT IN还是NOT EXISTS当orders表的customer_id有大量NULL时NOT IN的结果可能不符合预期NULL的坑而NOT EXISTS直接跳过NULL语义更正确。-- 这种写法要小心NULL陷阱 SELECT * FROM customers c WHERE c.customer_id NOT IN ( SELECT customer_id FROM orders ); -- 推荐 SELECT * FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.customer_id );6.3 连接顺序和驱动表理解MySQL怎么干活多表JOIN时MySQL会选择一个驱动表第一张被扫描的表然后用它的每一行去另一张表里找匹配。驱动表的选择直接影响扫描量。经验法则是小表驱动大表小结果集做驱动表。让小的那张表先扫用它的每一行去大表里通过索引查找这样大表只需要被索引命中而不是整表扫一遍。当然MySQL优化器会自动决定驱动表不需要你手动指定。但当你发现执行计划里驱动表选得不对、导致扫描行数暴增时可以用STRAIGHT_JOIN强制指定顺序。这个操作在极少数场景能用上日常开发不要乱用强制顺序可能让优化器放弃更优方案。6.4 索引设计的黄金法则复合查询的性能最后都落到索引上。两个核心原则第一连接字段必须建索引。JOIN的关联字段、子查询里关联的字段如果没有索引MySQL就只能做全表扫描加内存里的哈希匹配数据量一大必挂。ON e.deptno d.deptno两边的deptno都该有索引。第二过滤字段建索引排序字段建索引。WHERE里的过滤字段、ORDER BY或GROUP BY的字段如果它们不是连接字段也要考虑建索引。最理想的情况是构造一个联合索引让索引同时覆盖过滤和排序避免Using filesort。最后提一句MySQL 8.0引入了Hash Join对于没索引的大表连接性能比之前的Nested Loop Join好不少。但这并不意味着可以不建索引了——有索引永远是更优解Hash Join只是兜底方案。我个人实际写SQL的心得排序是这样先用可读性最好的写法保证逻辑正确再考虑性能一旦涉及大数据量EXPLAIN永远先跑一步JOIN能解决的别硬用子查询索引不是建得越多越好而是建在真正会走的地方。复合查询的核心姿势我觉得用一句话就能收尾先定主表、再定连接方向、然后选择组装方式——能用JOIN语义表达清楚的就别套子查询能用EXISTS的就别用IN能用一次JOIN解决的就别嵌套三层。把这些习惯刻进肌肉记忆里无论面试还是写业务你都不会在SQL上心虚。 MySQL复合查询实战：JOIN、自连接与子查询全解析
1. 从单表到多表为什么复合查询才是SQL的试金石先问个问题你在写业务代码的时候有没有遇到过这种情况——数据就在那几张表里摆着单表查询写得飞起可一旦需要把员工表和部门表串起来、找出每个部门工资最高的那个人或者查谁拿的工资比本部门平均值高当场就卡壳了我见过不少候选人CRUD写了好几年一到复合查询就露馅IN和EXISTS分不清自连接更是想都不敢想。说句实在话SQL的入门是单表但真正的分水岭就在复合查询。MySQL的复合查询说白了就是三件事多表查询JOIN、自连接、子查询。这三件事单独拆开都不难难的是把它们组合起来解决真实业务问题以及在写之前想清楚我要的数据到底怎么拼出来。这篇文章我打算用一个贯穿全文的案例来拆解这三个知识点员工表、部门表、薪资变动表。这个模型几乎覆盖了绝大多数复合查询场景也是面试里出现频率最高的表结构。如果你是刚把单表查询练熟的新手这篇文章能帮你把多表思路给理顺如果你已经在写业务SQL我建议重点看自连接和子查询的优化部分那里有几个我踩了无数坑才总结出来的经验。2. 复合查询的地基搞懂连接查询的底层逻辑2.1 笛卡尔积所有人踩过的第一个坑多表查询的第一课不是JOIN怎么写而是笛卡尔积。这是理解一切连接查询的底层钥匙。所谓笛卡尔积就是两张表的数据做全排列组合。员工表有10条记录部门表有5条记录不做任何条件直接查两个表结果就是50条。这个数字看着不大但换成100万行的订单表和10万行的用户表来一次那就是100亿行MySQL当场直接卡死也不奇怪。我第一次写多表查询时就吃过这个亏写了个SELECT * FROM emp, dept结果刷出来一堆莫名其妙的重复数据当时还以为MySQL坏了。后来才明白连接查询的本质就是先产生笛卡尔积再通过连接条件筛选出有效行。所以在我的实际使用习惯里几乎不写逗号形式的隐式连接全部用明确的JOIN语法-- 隐式连接不推荐 SELECT * FROM emp, dept WHERE emp.deptno dept.deptno; -- 显式连接推荐 SELECT * FROM emp INNER JOIN dept ON emp.deptno dept.deptno;两者的执行结果完全一样但显式JOIN的语义更清楚连接条件和过滤条件也能分开写。尤其当SQL语句长到几十行的时候显式JOIN的可读性优势是碾压级的。2.2 连接查询家族INNER JOIN、LEFT JOIN、RIGHT JOIN怎么选连接查询最核心的就是这几种连接方式连接方式返回结果类比INNER JOIN两个表中都能匹配上的行交集LEFT JOIN左表全部 右表匹配上的行左表全保留RIGHT JOIN右表全部 左表匹配上的行右表全保留CROSS JOIN所有行两两组合笛卡尔积这里有一个非常关键的思路先分清谁是主表。LEFT JOIN左边是主表右边的表哪怕匹配不上左边的数据也得保留没匹配上的右边字段用NULL填充RIGHT JOIN反过来。举个例子我想查每个部门的员工数量包括没有员工的空部门这个包括空部门就决定了部门表必须是主表SELECT d.deptno, d.dname, COUNT(e.empno) AS emp_count FROM dept d LEFT JOIN emp e ON d.deptno e.deptno GROUP BY d.deptno, d.dname;如果用INNER JOIN空部门根本不会出现——因为它没有员工匹配不上直接就被过滤掉了。我见过太多人用INNER JOIN写完统计数才发现空部门不见了然后一脸懵地在排查为什么数据变少。这就是为什么我一直强调动手写JOIN之前先想清楚你的结果集里谁必须是完整的。这个完整的表就是主表主表决定连接方向。2.3 等值连接之外的边角料非等值连接除了员工表和部门表这种用deptno等值关联的场景还有一种情况经常被忽略——非等值连接。什么叫非等值连接就是连接条件不是而是、、BETWEEN这类范围判断。最经典的例子是工资等级表-- 工资等级表 salgrade -- grade, losal, hisal SELECT e.ename, e.sal, s.grade FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;每个员工的工资落在哪个等级区间就把等级带出来。这个场景在报表统计里非常常见比如用户等级划分、VIP梯度计算、订单金额分层。由于等级表和员工表之间没有共同的业务ID只能用范围匹配所以非等值连接是唯一解法。记住一个判断标准连接条件字段是两个表都有的业务ID用等值连接连接条件是一个表的字段落在另一个表的某个区间里用非等值连接。3. 自连接的精髓一张表当成两张表用3.1 员工和上级一个模型的两种视角自连接是很多人的噩梦但理解之后你会发现它实在太巧妙了。自连接的场景有个共同特点同一张表里一行数据和另一行数据之间存在关联关系。最经典的例子就是员工表里的上下级关系-- emp表结构empno, ename, mgr上级的员工编号 -- 查每个员工的姓名以及他上级的姓名 SELECT worker.ename AS employee_name, manager.ename AS manager_name FROM emp worker LEFT JOIN emp manager ON worker.mgr manager.empno;这里的核心技巧就一条给同一张表起两个不同的别名把它当成两张独立的表来操作。worker代表员工视角manager代表上级视角连接条件worker.mgr manager.empno就是在说这个员工的上级编号等于另一个视角里某条记录的员工编号。我第一次讲这个知识点的时候有朋友问这不是自欺欺人吗它物理上就是一张表啊。其实不然SQL里表名加别名之后优化器会把它们当作两个独立的逻辑数据集来处理。你只需要在脑海里把它想象成——MySQL创建了两张内容一模一样、但名字不同的虚拟表副本剩下的连接逻辑和普通两表连接完全一致。3.2 为什么这里必须用LEFT JOIN继续看上面的例子。如果老板PRESIDENT没有上级他的mgr字段就是NULL。如果用INNER JOIN老板这条记录会因为worker.mgr manager.empno匹配不上NULL而被丢掉。但业务上的需求是查每个员工以及他的上级老板也是员工啊凭什么把他丢了所以此时必须用LEFT JOIN以员工表为主表保证所有员工都出现在结果里老板的上级字段显示NULL语义恰好就是这个人是最高领导没有上级。这个细节就是区分菜鸟和老手的地方。写SQL不仅要让结果对还要能说清楚为什么这个连接方向是对的。面试的时候把这个点讲透基本就能证明你是真用过而不是背过。3.3 自连接的另一个经典场景连续记录匹配自连接不只用于上下级关系。我举一个业务开发里更常见的场景。有一个打卡记录表字段是员工ID和打卡日期。现在要查连续三天都有打卡记录的全勤员工。如果不用自连接这个需求怎么实现窗口函数可以但很多老版本的MySQL或者某些团队约定不用窗口函数那么自连接就是最直接的方案SELECT DISTINCT a.emp_id FROM attendance a JOIN attendance b ON a.emp_id b.emp_id AND b.att_date DATE_ADD(a.att_date, INTERVAL 1 DAY) JOIN attendance c ON a.emp_id c.emp_id AND c.att_date DATE_ADD(a.att_date, INTERVAL 2 DAY);这个SQL的思路是a代表第一天b代表第二天c代表第三天。一个人同时拥有这三天记录就是连续三天打卡。把三张虚拟表通过日期错位关联起来一次匹配就把连续性问题解决了。动手写自连接的时候有个非常管用的心法找到那个把两行数据关联起来的业务纽带。在上下级场景里纽带是mgr在连续打卡场景里纽带是日期相差一天。这个纽带找到了自连接就写出来一大半了。4. 子查询嵌套的世界里藏着一整条语法链4.1 从标量子查询开始一个值引发的查询子查询从使用位置来分最常用的有三种SELECT后面、FROM后面、WHERE后面。最简单的是标量子查询它的特点是返回一行一列也就是一个确定的值。比如查每个员工的姓名和所在部门名称除了用JOIN还可以这样写SELECT e.ename, (SELECT d.dname FROM dept d WHERE d.deptno e.deptno) AS dept_name FROM emp e;这个子查询在SELECT子句里对每一条emp记录执行一次拿到对应的部门名称。感受一下主查询有多少行这个子查询就可能被执行多少次这就是相关子查询的特征——内层查询引用了外层查询的字段这里的e.deptno。标量子查询的使用限制很严必须确保它只返回一个值如果子查询返回多行MySQL直接报错Subquery returns more than 1 row。所以在写标量子查询前你得确保连接条件能唯一确定一行。不能保证唯一性的时候老老实实去用JOIN或者加LIMIT 1。从性能角度我个人的习惯是能用JOIN解决的优先用JOIN。标量子查询可读性虽好但在大表上性能可能不太好后面专门讲优化的时候会细说。4.2 IN、ANY、ALL操作符决定子查询的语义当子查询返回的是一列多行的时候就需要在WHERE里配合操作符使用。这里最容易混淆的就是IN、ANY、ALL三兄弟。IN是最常用的语义是匹配集合中的任意一个-- 查在研发部和市场部工作的员工 SELECT ename, job FROM emp WHERE deptno IN ( SELECT deptno FROM dept WHERE dname IN (研发部, 市场部) );ANY和ALL则用于比较运算。拿 ANY(...)和 ALL(...)来说前者表示大于子查询结果中的任意一个约等于大于最小值后者表示大于子查询结果中的全部约等于大于最大值-- 查工资高于任何一个部门平均工资的员工 SELECT ename, sal FROM emp WHERE sal ANY ( SELECT AVG(sal) FROM emp GROUP BY deptno ); -- 查工资高于所有部门平均工资的员工 SELECT ename, sal FROM emp WHERE sal ALL ( SELECT AVG(sal) FROM emp GROUP BY deptno );说实话ANY和ALL在工作里用得不多因为多数情况下可以用MIN/MAX或者EXISTS改写。但它们考的正是对子查询结果集的理解——子查询不是只能返回一个值它能返回一组值而这一组值是怎么参与外层比较的就取决于操作符。4.3 相关子查询 vs 不相关子查询理解执行顺序的关键如果说子查询有一个必须搞懂的概念那一定是相关子查询和不相关子查询的区别。这决定了你写的SQL会不会爆表、会不会扫出来一堆没用的数据。不相关子查询内层查询和外层查询没有关联可以先独立执行结果是一个固定的集合外层再拿这个集合去过滤。比如上面那个IN的例子子查询查部门表不依赖外层任何字段就是先查一次拿到部门编号集合再执行外层。相关子查询内层查询引用了外层查询的字段所以每一行外层记录都要带着自己的值去执行一次内层查询。比如上面那个标量子查询的例子每一条员工记录都要执行一次找部门名称的子查询。区分这两者的意义在于理解性能。不相关子查询执行一次就缓存结果相关子查询相当于每行执行一次数据量大时可能非常慢。所以写相关子查询的时候一定要确认内层查询走索引否则就是一场灾难。4.4 EXISTS与IN的相爱相杀EXISTS是另一个高频操作符它的语义是存在即可不关心子查询返回什么列只关心有没有行。从执行逻辑上说EXISTS一般比IN更高效尤其是子查询结果集非常大、外层表相对较小的时候。经典案例查有实际订单记录的客户-- IN写法 SELECT c.customer_id, c.customer_name FROM customers c WHERE c.customer_id IN ( SELECT o.customer_id FROM orders o ); -- EXISTS写法 SELECT c.customer_id, c.customer_name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.customer_id );两者结果一样但EXISTS的判断逻辑是对每一条客户记录去orders表里找有没有匹配行找到一个就立即停止。而IN是先把orders表的所有customer_id全部查出来形成一个大集合再把客户表每条记录去这个大集合里做查找。所以在MySQL 5.7及以下版本里EXISTS通常更稳妥。但到了MySQL 8.0优化器做了很多改版IN在某些场景下也可能被优化成半连接semi-join性能不差多少。我的建议是先写语义清晰的版本再用EXPLAIN看执行计划有性能瓶颈再改不要一开始就烧脑优化。4.5 FROM子句里的子查询派生表的玩法子查询放在FROM子句里结果就成了一个临时表也叫派生表。这个位置的子查询可以把复杂的聚合逻辑先算好再跟别的表做连接逻辑层次非常清晰。比如先算出每个部门的平均工资再和员工表关联查出每个员工薪资与部门均值的差距SELECT e.ename, e.sal, dept_avg.avg_sal, ROUND(e.sal - dept_avg.avg_sal, 2) AS diff FROM emp e JOIN ( SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno ) dept_avg ON e.deptno dept_avg.deptno;FROM子句里的子查询本质上是在SQL执行的早期就被物化materialize成一个临时表后续所有操作都基于这个临时表。这里有一个很重要的注意点派生表必须要有别名这是MySQL的语法强制要求不写别名直接报错。这种写法特别适合先缩小数据范围再关联的场景。比如先按订单明细聚合出销售额再关联商品表补全商品名称。把复杂问题拆成先处理内层再处理外层两步逻辑一下就清晰了。5. 实战演练三个综合场景串起所有知识点5.1 场景一找出各部门工资最高的人这个场景几乎是面试复合查询的必考题。它的坑在于如果直接按部门分组求最高工资你再把员工表JOIN回来的时候会遇到一个问题——最高工资的值能拿到但对应的员工信息姓名、职位会被GROUP BY搞得很难拿。推荐做法先用子查询算出每个部门的最高工资再用这个结果集去关联员工表把匹配的员工捞出来SELECT e.deptno, e.ename, e.sal FROM emp e JOIN ( SELECT deptno, MAX(sal) AS max_sal FROM emp GROUP BY deptno ) t ON e.deptno t.deptno AND e.sal t.max_sal;这个方案同时用到了派生表、多表连接、聚合函数三层嵌套一气呵成。理解这个SQL复合查询的一半功力就到手了。注意如果同一个人在不同的部门里或者同一个部门有多个人工资并列最高这个SQL会把所有并列的人都查出来这是符合业务期望的。5.2 场景二找到工资比本部门平均工资高的员工这是典型的相关子查询场景。不相关子查询没法做因为每个部门的平均工资不一样必须带着当前行的部门编号去算对应部门的平均值SELECT e.ename, e.sal, e.deptno FROM emp e WHERE e.sal ( SELECT AVG(sal) FROM emp WHERE deptno e.deptno );这个SQL执行的时候外层每一行员工记录都拿到自己的deptno去内层算一遍这个部门的平均工资然后再比较。逻辑很直观性能嘛——员工表只有几千行无所谓如果有百万行这个SQL可能就吃力了。优化思路先按部门把平均值算出来物化成临时表再关联比较也就是用上面场景一的派生表思路改写。5.3 场景三查出连续两次加薪的员工结合薪资变动表字段empno、change_date、new_sal想查哪些员工在相邻两个月里连续调过薪。这里的相邻就是自连接的典型场景SELECT DISTINCT a.empno FROM salary_change a JOIN salary_change b ON a.empno b.empno AND b.change_date DATE_ADD(a.change_date, INTERVAL 1 MONTH);把薪资变动表当成两张表一次变动是a下一次变动是b通过员工相同日期相差一个月来定位。这就是自连接日期函数联合作用的经典写法。这三个场景覆盖了多表查询、自连接、子查询的全部核心用法。我强烈建议你拿着这组SQL去自己的本地库跑一遍把结果集打开一行行对照着看比看十遍理论都管用。SQL这东西光看是学不会的得亲手拉数据才有手感。6. 复合查询的性能底线哪些写法要命哪些写法保命6.1 先看EXPLAIN再聊性能我先说一句现在MySQL 8.0的优化器已经比前几个版本智能多了很多早年间的铁律现在都要打个问号。所以无论网上怎么说动手前先跑EXPLAIN看执行计划这才是最靠谱的判断依据。常用的执行计划指标type字段从好到差大致是const、eq_ref、ref、range、index、ALL。看到ALL就要警惕是否全表扫描。key字段实际用到的索引如果是NULL说明没走索引。rows字段预估扫描的行数数字越大越危险。Extra字段出现Using temporary或者Using filesort时要留意可能是有排序或分组没走索引。6.2 子查询的优化能物化就物化能改JOIN就改JOINMySQL 8.0对子查询做了很多优化比如把IN子查询改为半连接把FROM子句里的派生表做物化并加索引。但我实际测试下来复杂的相关子查询在数据量起来之后还是比较容易成为瓶颈的因为它们每行执行一次的特性很难被完全优化掉。一个比较稳的经验是查询的底层数据量小几千行以内子查询怎么写都无所谓可读性优先。数据量到几十万行以上优先尝试改写为JOIN。JOIN的执行引擎对连接算法的优化非常成熟Nested Loop Join、Hash Join比相关子查询更可控。如果确实需要保留子查询确保被子查询扫描的表上关联字段有索引。一个典型的改写例子查没有订单的客户用NOT IN还是NOT EXISTS当orders表的customer_id有大量NULL时NOT IN的结果可能不符合预期NULL的坑而NOT EXISTS直接跳过NULL语义更正确。-- 这种写法要小心NULL陷阱 SELECT * FROM customers c WHERE c.customer_id NOT IN ( SELECT customer_id FROM orders ); -- 推荐 SELECT * FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.customer_id );6.3 连接顺序和驱动表理解MySQL怎么干活多表JOIN时MySQL会选择一个驱动表第一张被扫描的表然后用它的每一行去另一张表里找匹配。驱动表的选择直接影响扫描量。经验法则是小表驱动大表小结果集做驱动表。让小的那张表先扫用它的每一行去大表里通过索引查找这样大表只需要被索引命中而不是整表扫一遍。当然MySQL优化器会自动决定驱动表不需要你手动指定。但当你发现执行计划里驱动表选得不对、导致扫描行数暴增时可以用STRAIGHT_JOIN强制指定顺序。这个操作在极少数场景能用上日常开发不要乱用强制顺序可能让优化器放弃更优方案。6.4 索引设计的黄金法则复合查询的性能最后都落到索引上。两个核心原则第一连接字段必须建索引。JOIN的关联字段、子查询里关联的字段如果没有索引MySQL就只能做全表扫描加内存里的哈希匹配数据量一大必挂。ON e.deptno d.deptno两边的deptno都该有索引。第二过滤字段建索引排序字段建索引。WHERE里的过滤字段、ORDER BY或GROUP BY的字段如果它们不是连接字段也要考虑建索引。最理想的情况是构造一个联合索引让索引同时覆盖过滤和排序避免Using filesort。最后提一句MySQL 8.0引入了Hash Join对于没索引的大表连接性能比之前的Nested Loop Join好不少。但这并不意味着可以不建索引了——有索引永远是更优解Hash Join只是兜底方案。我个人实际写SQL的心得排序是这样先用可读性最好的写法保证逻辑正确再考虑性能一旦涉及大数据量EXPLAIN永远先跑一步JOIN能解决的别硬用子查询索引不是建得越多越好而是建在真正会走的地方。复合查询的核心姿势我觉得用一句话就能收尾先定主表、再定连接方向、然后选择组装方式——能用JOIN语义表达清楚的就别套子查询能用EXISTS的就别用IN能用一次JOIN解决的就别嵌套三层。把这些习惯刻进肌肉记忆里无论面试还是写业务你都不会在SQL上心虚。