文章目录MySQL内外连接入门从“拼桌子”到“谁做主”新手也能看懂的大白话指南一、先搞懂底层逻辑所有连接的本质都是“笛卡尔积筛选”二、内连接INNER JOIN双向奔赴缺一不可2.1 最经典的场景2.2 两种写法效果一模一样写法1隐式内连接逗号 WHERE写法2显式内连接INNER JOIN ... ON2.3 写法深度对比新手该用哪种2.4 什么时候用内连接三、外连接OUTER JOIN总得有一张表当“主角”3.1 先准备测试数据3.2 左外连接LEFT JOIN左边的表说了算语法示例查询所有学生的成绩没成绩也要显示学生3.3 右外连接RIGHT JOIN右边的表说了算语法示例查询所有成绩没对应学生也要显示成绩3.4 冷知识左连接和右连接可以互相转换3.5 拓展全外连接FULL JOIN——我全都要四、新手第一大坑ON 和 WHERE 别再乱写了4.1 核心区别4.2 直观对比示例语句1条件写在ON里语句2条件写在WHERE里五、实战升级三表连接怎么写六、性能小技巧新手也能用上的优化七、一张表总结连接类型怎么选八、动手练一练基础题参考答案进阶题写在最后MySQL内外连接入门从“拼桌子”到“谁做主”新手也能看懂的大白话指南很多小伙伴刚啃完单表查询信心满满冲进多表联查的世界结果直接被三连击干懵两张表一拼怎么凭空多出几百条数据我明明表里有这条数据怎么查不出来LEFT JOIN、RIGHT JOIN、INNER JOIN到底该用哪个别急今天咱们就用“拼桌子”的大白话把MySQL里的连接查询讲透。从底层原理到写法对比从常见踩坑到实战案例看完不仅能写对还能知道为什么这么写。一、先搞懂底层逻辑所有连接的本质都是“笛卡尔积筛选”在讲任何连接之前必须先搞懂一个底层概念笛卡尔积Cartesian Product。你可以把它理解成「无脑全排列」如果表A有m行记录表B有n行记录笛卡尔积就会生成 m × n 行结果——把两张表的每一行都两两组合一遍。举个生活化的例子你衣柜里有4件上衣对应学生表4条数据有3条裤子对应成绩表3条数据笛卡尔积就是把所有穿搭组合都列出来4×312种不管红上衣配绿裤子好不好看。而我们所有的「连接查询」本质上都是两步走先把多张表做笛卡尔积生成所有可能的组合再用连接条件把不合理、不匹配的组合过滤掉理解了这个你就会发现所有连接类型的区别只是「过滤的规则不一样」。二、内连接INNER JOIN双向奔赴缺一不可内连接是开发中使用频率最高的连接没有之一。它的逻辑非常简单只保留两张表中匹配条件都满足的记录。说白了就是你有我也有咱们才凑一对缺一边直接淘汰。相当于取两张表的「交集」。2.1 最经典的场景员工表EMP里存着员工和部门编号部门表DEPT里存着部门编号和部门名。想查员工「SMITH」的姓名和他对应的部门名称——只有员工有归属部门才能查得到部门名这就是典型的内连接场景。2.2 两种写法效果一模一样内连接有两种主流写法很多新手看到不同教程写法不一样就懵其实执行结果和性能完全相同只是语法风格不同。写法1隐式内连接逗号 WHERE这是SQL早期的传统写法直接用逗号分隔多张表把连接条件和业务过滤条件都写在WHERE子句里。-- 老式写法隐式内连接SELECTename,dnameFROMEMP,DEPTWHEREEMP.deptnoDEPT.deptno-- 表连接条件ANDenameSMITH;-- 业务过滤条件写法2显式内连接INNER JOIN … ON这是SQL-92标准推出的规范写法用INNER JOIN明确表示要做连接ON专门用来写表与表的关联条件。-- 标准写法显式内连接SELECTename,dnameFROMEMPINNERJOINDEPTONEMP.deptnoDEPT.deptno-- 连接条件专属位置WHEREenameSMITH;-- 只留业务过滤条件 小知识点INNER关键字可以省略直接写JOINMySQL默认就是内连接。2.3 写法深度对比新手该用哪种既然效果一样那为什么行业都推荐显式写法我们从5个维度拆解对比维度隐式内连接逗号WHERE显式内连接JOIN … ON语义清晰度差连接条件和过滤条件混在一起高职责分离一目了然多表扩展性差3张表以上WHERE就乱成粥好每张表的关联条件独立改外连接成本高几乎要推翻重写极低改个关键字就行漏写条件风险高漏写直接生成笛卡尔积炸库低语法层面有约束行业规范度淘汰边缘老代码常见企业级开发强制标准⚠️ 新手避坑隐式写法如果漏写连接条件比如忘了写EMP.deptno DEPT.deptno语法不会报错但会输出两张表的全量笛卡尔积。数据量小还好量大直接把数据库跑卡。给初学者的建议从写第一条多表查询开始就养成用JOIN ... ON的习惯。规范的写法不仅少踩坑后续看公司项目代码也会更顺畅。2.4 什么时候用内连接当你只需要「两边都有对应数据」的记录时就用内连接查询有归属部门的正式员工查询有下单记录的用户查询有考试成绩的学生三、外连接OUTER JOIN总得有一张表当“主角”内连接很好用但解决不了一个核心问题如果我想让某一张表的所有数据都保留哪怕另一张表没有匹配的也要显示出来怎么办比如经典需求查询所有学生的考试成绩就算这个学生缺考、没成绩也要把他的名字列出来。这时候就需要「外连接」了。外连接的核心就是有一张表是“主角”它的所有行都必须出现另一张表是“配角”匹配得上就显示匹配不上就补NULL。外连接分两种左外连接LEFT JOIN、右外连接RIGHT JOIN逻辑完全对称。3.1 先准备测试数据我们用学生表和成绩表来演示非常好理解-- 学生表4个学生CREATETABLEstu(idINT,nameVARCHAR(30));INSERTINTOstuVALUES(1,jack),(2,tom),(3,kity),(4,nono);-- 成绩表3条成绩-- 注意只有id1、2有对应学生id11没有对应学生CREATETABLEexam(idINT,gradeINT);INSERTINTOexamVALUES(1,56),(2,76),(11,8);3.2 左外连接LEFT JOIN左边的表说了算左外连接顾名思义FROM后面的左表是主角所有记录全部保留右表匹配不上的字段自动填充NULL。语法SELECT字段列表FROM左表LEFT[OUTER]JOIN右表ON连接条件;日常开发中OUTER一般都省略直接写LEFT JOIN。示例查询所有学生的成绩没成绩也要显示学生SELECT*FROMstuLEFTJOINexamONstu.idexam.id;查询结果idnameidgrade1jack1562tom2763kityNULLNULL4nonoNULLNULL很明显kity和nono虽然没有考试成绩但作为左表的“主角”依然出现在结果里成绩部分用NULL补上了。3.3 右外连接RIGHT JOIN右边的表说了算右外连接和左外连接完全反过来JOIN后面的右表是主角所有记录全部保留左表匹配不上的字段自动填充NULL。语法SELECT字段列表FROM左表RIGHT[OUTER]JOIN右表ON连接条件;示例查询所有成绩没对应学生也要显示成绩SELECT*FROMstuRIGHTJOINexamONstu.idexam.id;查询结果idnameidgrade1jack1562tom276NULLNULL118这次id11的成绩没有对应的学生但作为右表的“主角”成绩依然保留学生信息部分补了NULL。3.4 冷知识左连接和右连接可以互相转换其实LEFT JOIN和RIGHT JOIN没有本质区别只要把两张表的顺序调换一下左连接就能实现右连接的效果。比如下面这两句SQL执行结果完全等价-- 写法1部门表左连员工表SELECTd.dname,e.*FROMdept dLEFTJOINemp eONd.deptnoe.deptno;-- 写法2员工表右连部门表SELECTd.dname,e.*FROMemp eRIGHTJOINdept dONd.deptnoe.deptno;所以实际开发中很多程序员习惯只用LEFT JOIN靠调换表顺序来实现所有外连接需求减少记忆负担。新手也可以这么练先把左连接玩明白。3.5 拓展全外连接FULL JOIN——我全都要有些数据库支持FULL OUTER JOIN全外连接意思是两张表都是主角两边的记录都保留匹配不上的地方都补NULL相当于「左连接 右连接」的并集。可惜的是MySQL目前不支持直接写FULL JOIN。但我们可以用UNION曲线实现-- MySQL 实现全外连接SELECT*FROMstuLEFTJOINexamONstu.idexam.idUNIONSELECT*FROMstuRIGHTJOINexamONstu.idexam.id;四、新手第一大坑ON 和 WHERE 别再乱写了这是90%的MySQL新手都会踩的坑没有之一同样的过滤条件写在ON后面和写在WHERE后面结果可能天差地别4.1 核心区别记住一句话永远不会错ON连接阶段生效用来判断两张表怎么匹配。外连接中就算不满足ON条件主表数据也会保留。WHERE连接完成后生效对最终的结果集进行过滤不满足的行直接删掉。4.2 直观对比示例还是用学生和成绩表我们加一个“分数大于60”的条件分别写在ON和WHERE里看结果差异。语句1条件写在ON里SELECT*FROMstuLEFTJOINexamONstu.idexam.idANDexam.grade60;结果idnameidgrade1jackNULLNULL2tom2763kityNULLNULL4nonoNULLNULL解释jack的成绩56分不满足60所以成绩部分补NULL但学生信息依然保留——因为条件写在ON里不影响主表。语句2条件写在WHERE里SELECT*FROMstuLEFTJOINexamONstu.idexam.idWHEREexam.grade60;结果idnameidgrade2tom276解释WHERE是对最终结果过滤直接把所有不满足grade60的行都删掉了只剩下tom一条。效果和内连接几乎没区别。⚠️ 重要结论外连接中如果你想保留主表的全部数据从表的过滤条件一定要写在ON里不要写在WHERE里。五、实战升级三表连接怎么写实际开发中很少只连两张表比如常见的「学生-课程-成绩」三表场景。需求查询所有学生的所有课程成绩学生没选课、没成绩的也要显示学生姓名。-- 三表左连接示例SELECTs.nameAS学生姓名,c.course_nameAS课程名称,sc.gradeAS分数FROMstu sLEFTJOINscONs.idsc.stu_idLEFTJOINcourse cONsc.course_idc.id;规律很简单第一张表是主表学生表后面每加一张表就写一个LEFT JOIN ... ON连接条件跟着对应的表走六、性能小技巧新手也能用上的优化小表驱动大表连接查询时把数据量小的表放左边当主表MySQL会用小表去匹配大表性能更好。连接字段尽量加索引两张表关联的字段比如deptno、id如果数据量大一定要建索引。没索引的连接就是全表扫描慢到怀疑人生。**尽量避免SELECT ***多表连接字段多SELECT *会返回大量冗余数据按需查字段既快又清晰。七、一张表总结连接类型怎么选连接类型核心逻辑保留的数据典型适用场景内连接 INNER JOIN只保留两边都匹配成功的记录两张表的交集找双方都存在的数据左外连接 LEFT JOIN左表全保留右表匹配不上补NULL左表全部记录以左表为主体补充右表信息右外连接 RIGHT JOIN右表全保留左表匹配不上补NULL右表全部记录以右表为主体补充左表信息全外连接 FULL JOIN两张表都保留匹配不上两边都补NULL两张表的并集MySQL需用UNION间接实现记忆口诀内连接找交集左连左表都要提右连右表不能落ON连WHERE后过滤。八、动手练一练光学不练假把式这几道题可以自己动手试试。基础题基于员工表EMP和部门表DEPT查询每个部门的员工姓名没有员工的部门也要显示部门名。查询所有员工的部门名称没有部门的员工也要显示。参考答案-- 题1部门左连员工SELECTd.dname,e.enameFROMdept dLEFTJOINemp eONd.deptnoe.deptno;-- 题2员工左连部门SELECTe.ename,d.dnameFROMemp eLEFTJOINdept dONe.deptnod.deptno;进阶题可以去LeetCode刷两道经典真题做完对连接的理解直接上一个台阶178. 分数排名Rank Scores626. 换座位Exchange Seats写在最后其实多表连接没那么玄乎核心就是搞清楚两个问题我要以哪张表为主决定用内连还是外连左连还是右连我的条件是用来连表的还是用来过滤最终结果的决定写在ON还是WHERE多写几遍多跑几次看结果差异很快就能熟练掌握。毕竟连接是MySQL的重中之重不管是做开发还是数据分析都是天天要用的技能。