学 MySQL 的人里流传一句话单表查询只是热身表连接才是真正的第一道分水岭。我见过不少同学WHERE、ORDER BY、GROUP BY 都练得很熟一遇到两张表要合在一起查就发怵要么把所有组合都查出来要么关联条件写错结果集膨胀上千行更别说内连接、外连接、左连接、右连接到底该选哪一个。这篇内容就是专门把这件事讲透的内连接和外连接各自解决什么问题返回结果有什么不同实际业务里怎么写才不容易错以及连接慢的时候到底该从哪儿下手排查。这篇文章适合刚把单表查询学完、正卡在连接这一关的初学者也适合写过一段时间 SQL 但总在 LEFT JOIN 的 ON 和 WHERE 上翻车的人。我会用学生、课程、成绩这种最经典的场景做例子也会穿插员工上下级、孤儿数据核对这种真实业务形态。连接最要紧的不是背语法而是想清楚结果集会是什么样——只要你能在写 SQL 之前预判出返回多少行、哪些位置会出现 NULL内连接和外连接就算真正学会了。1. 拆表容易拼表难为什么连接会成为分水岭1.1 范式拆分与信息重组的基本矛盾先聊一件很多教程开头不讲的事为什么我们非要连接好的表设计都有一个共同特点就是拆。一个稍微像样的业务系统绝不会把用户信息、订单信息、商品信息全部塞进一张大宽表。按范式拆完之后用户表是 user订单表是 order订单明细是 order_item商品表是 product。这样做的收益很明显数据冗余少更新不会出现改一处漏一处的异常每一张表职责单一。但它也带来一个直接后果你脑子里想要的那条完整信息在数据库里是散着的。客户叫什么名字在一张表他买了什么商品在另一张表成交价又在第三张表。查询的时候就必须把这些表横向拼成一个“虚拟宽表”再从这个宽表里取你想要的列。这个拼的过程就是连接。1.2 两个核心概念笛卡尔积与匹配条件要把连接这件事理解到不会踩坑的程度我建议先接受一个教学模型完成一次两表连接在语义上可以拆成两步。第一步把两张表的每一行两两配对形成一个笛卡尔积。一张 100 行的表 A 和一张 50 行的表 B配对结果就是 100×505000 个组合。第二步按 ON 条件去筛选这些组合比如只保留a.user_id b.user_id的那些配对。你可能会问实际执行时数据库真的会先把 5000 行组合全生成出来吗不会。优化器会有 nested loop、hash join 这些更聪明的执行方式MySQL 8.0.18 之后等值连接还能走 hash join但结果语义和上面这个两步模型是完全一致的。学连接时用这个模型去预判结果集基本上不会出错。笛卡尔积这个概念很多人在内连接的时候听过就算了但它恰恰是后面理解外连接和排查结果集膨胀的关键。你写的每个 JOIN本质上都是在告诉数据库请把这两张表的行配对但只保留我指定的那类配对。1.3 连接、子查询与 EXISTS 的边界既然 JOIN 能做关联查询为什么还有那么多 IN 子查询和 EXISTS 的写法因为它们在表达不同的语义。WHERE id IN (SELECT ...)更接近“判断某张表里有没有符合条件的行”它通常只关心是否存在不打算把另一张表的列也带出来。而 JOIN 天然就是把另一张表的列一起投影到结果里。EXISTS 则擅长处理带关联条件的判断比如“找出所有下过订单的用户”。这里给一条实际工作经验如果你的目标就是“把 a 表的若干行拿出来同时带上 b 表某个字段”优先 JOIN如果只是“判断在不在”IN 和 EXISTS 没问题但注意 MySQL 8.0 会把很多 IN 子查询优化成 semi join所以也别迷信某一种写法。最怕的是子查询套三层以上阅读成本和执行风险都直线上升。能用连接把需求表达清楚就尽量用连接。2. 内连接只要匹配成功的记录2.1 等值内连接的语法与结果集特征内连接是使用频率最高、也最容易被小看的连接方式。它的定义很干脆只返回能按 ON 条件匹配成功的组合匹配不上的两边数据都不要。先给你一套贯穿全文的数据。我把后面会反复用的学生、课程、成绩三张表建出来CREATE TABLE student ( id INT PRIMARY KEY, name VARCHAR(20) ); CREATE TABLE course ( id INT PRIMARY KEY, title VARCHAR(30) ); CREATE TABLE score ( student_id INT, course_id INT, grade DECIMAL(5,2), PRIMARY KEY (student_id, course_id) ); INSERT INTO student VALUES (1, 张三), (2, 李四), (3, 王五); INSERT INTO course VALUES (101, 数据库), (102, 操作系统), (103, 计算机网络); INSERT INTO score VALUES (1, 101, 88.00), (1, 102, 76.00), (2, 101, 91.00), (99, 102, 60.00);注意我最后故意插了一条student_id99的成绩记录它没有对应的学生这是为了后面演示外连接和核对孤儿数据用的你先记着。现在写一条经典的内连接SELECT s.id, s.name, sc.course_id, sc.grade FROM student s INNER JOIN score sc ON s.id sc.student_id;结果只会出现张三和李四因为王五没有成绩而那条student_id99的孤儿成绩也因为没有匹配的学生而消失。这就是内连接的边界它取的是两边能够对上的部分。如果你是面试者我会推荐用“交集”这个词来形容它但心里要清楚这里的“交”是基于 ON 条件算出来的配对集合不是朴素的两个集合求交集。还有一个新手容易忽略的知识点内连接返回的行数不等于左表行数也不等于右表行数而是等于“满足条件的配对组合数”。学生表里有一个学生成绩表里他有十门成绩连接后就会出十行。多对多关系下行数可能比两张表各自的行数都大这不是 BUG这是配对逻辑的必然结果。2.2 内连接的 ON 与 WHERE一开始就要养成好习惯内连接里有一个隐藏的“坑前预告”ON 条件和 WHERE 条件在内连接中写在哪里结果一样。比如下面这两条 SQL结果完全一致SELECT s.name, sc.grade FROM student s INNER JOIN score sc ON s.id sc.student_id WHERE sc.course_id 102; SELECT s.name, sc.grade FROM student s INNER JOIN score sc ON s.id sc.student_id AND sc.course_id 102;结果一致是因为内连接丢弃未匹配行无论你是在 ON 里过滤还是在 WHERE 里过滤最终留下来的都是“既有匹配关系又满足课程条件”的行。但这不代表你可以随手乱放。我的建议特别简单ON 子句只写连接条件也就是“两张表之间靠什么字段对应”这种关系WHERE 子句写业务过滤条件。养成这个习惯之后哪天你把 INNER JOIN 改成 LEFT JOINON 里的条件依然稳定表达“有关系”而 WHERE 里的条件会继续过滤整个结果集不会产生语义突变。90% 的 LEFT JOIN 翻车事故都源于 ON 和 WHERE 职责不分。2.3 老式逗号连接能不能用遇到老项目怎么办你肯定在老同事的代码里见过这种写法SELECT s.name, sc.course_id FROM student s, score sc WHERE s.id sc.student_id;这种用逗号分隔多张表、再用 WHERE 补连接条件的写法功能上等价于内连接但隐患很明显如果哪天有人把 WHERE 条件删掉SQL 就会退化成完全笛卡尔积两个表几十万行相互配对瞬间能把数据库拖垮。遇到老项目里这种 SQL我的经验是先读懂 WHERE 里哪些是连接条件、哪些是过滤条件改写成显式 JOIN 时把连接条件拆到 ON 里如果一时没法改全至少别在新代码里继续用逗号连接。这不仅是可读性问题更是在降低未来出事故的概率。显式 JOIN 的另一个好处是它把表关系表达得明明白白接手的后来者不用从 WHERE 的一堆条件里去猜哪几个字段才是关联键。3. 外连接保住主表数据是第一原则3.1 LEFT JOIN 三步理解法外连接和内连接的本质区别在于内连接把未匹配的行丢掉了外连接会把其中一侧的行保留下来。最常用的是 LEFT JOIN我建议你用下面三步来理解它把左表和右表的所有行做笛卡尔积。按 ON 条件留下匹配的组合。把左表中没能在步骤 2 里匹配到的行也加进结果右表的列统一填 NULL。看实际例子。要查询“所有学生各自有哪些成绩”哪怕某个学生一门课都没选也得出现在结果里这时候必须用 LEFT JOINSELECT s.id, s.name, sc.course_id, sc.grade FROM student s LEFT JOIN score sc ON s.id sc.student_id;结果是张三两行、李四一行、王五一行王五那行的 course_id 和 grade 都是 NULL。注意这里不是“王五没有出现”而是“王五出现了但右边没有内容可以填于是用 NULL 占位”。这个 NULL 不是错误它是 LEFT JOIN 明确向你传达的信号左边有这个人右边没有对应数据。业务上什么时候非用 LEFT JOIN 不可主表数据必须全保留的时候。比如用户列表要带上他最近一笔订单用户是主表即使没下过单也要显示订单列表要带上商品名称订单是主表即使商品后来被删了也要保住订单记录。3.2 RIGHT JOIN 就是反过来的 LEFT JOINRIGHT JOIN 和 LEFT JOIN 是镜像关系RIGHT JOIN 保住右表的所有行左表匹配不上的时候左表列填 NULL。照理说右边实践也一样。把上面的查询写成 RIGHT JOINSELECT s.id, s.name, sc.course_id, sc.grade FROM score sc RIGHT JOIN student s ON s.id sc.student_id;结果和前面的 LEFT JOIN 完全一样。因为我只是把主表 student 换到了右边。这个例子能帮你理解 LEFT 和 RIGHT 之间那个“方向”的含义哪一侧要全保留就把哪一侧写在对应方向。但真实工作中RIGHT JOIN 用得很少。原因有二一是大部分开发者的阅读习惯是从左往右主表在左边更容易理解二是很多 ORM 和查询规范干脆禁用了 RIGHT JOIN需要时用 LEFT JOIN 把两张表换个位置就能实现同样效果。如果你在一个团队里看到大部分 SQL 都只有 LEFT JOIN不用觉得奇怪那只是一种约定俗成的风格。3.3 MySQL 没有 FULL JOIN核对孤儿数据用 UNION 兜底很多语言里都有 FULL OUTER JOIN也就是左右两边的未匹配行都保留。MySQL 一直没内置这个语法但不代表业务上没有这个需求。什么时候需要 FULL JOIN最常见的场景是核对孤儿数据。比如 score 表里那条student_id99的成绩它属于一个已经不存在于 student 表的学生同时 student 表里又有王五这种没有任何成绩的学生。你想把两边都在数据库中“落单”的数据一起列出来LEFT JOIN 只看到王五RIGHT JOIN 只看到 99 号成绩都看不到全貌。MySQL 的替代方案是 LEFT JOIN 和 RIGHT JOIN 各查一次再用 UNION 合并SELECT s.id, s.name, sc.course_id, sc.grade FROM student s LEFT JOIN score sc ON s.id sc.student_id UNION SELECT s.id, s.name, sc.course_id, sc.grade FROM student s RIGHT JOIN score sc ON s.id sc.student_id ORDER BY id;这里有个细节必须用 UNION不能随手换成 UNION ALL。因为两边结果里那些能正常匹配的学生成绩会在两个查询中都出现UNION 会去重UNION ALL 会让正常数据出现两遍。UNION 的去重在这里恰恰是在帮我们还原 FULL JOIN 的语义。这条 SQL 在实际项目中不算高频但真的会遇到。曾经有个同事负责清理历史订单数据就是靠这种写法把用户表里已经注销的用户、以及订单表里找不到归属用户的订单一次性查出来几十万条脏数据几分钟定位完。平时可以不了解用到时要知道有这条路。3.4 LEFT JOIN 里把副表条件放在 WHERE结果会变回内连接这是外连接里最值得单独拎出来讲的一个坑。假设你要查所有学生同时标出每个学生是否选了 102 号课程。直觉写法可能是SELECT s.id, s.name, sc.course_id, sc.grade FROM student s LEFT JOIN score sc ON s.id sc.student_id WHERE sc.course_id 102;这个 SQL 的结果会让你一脸迷惑王五消失了张三也只显示选了 102 的那一条。整个查询变成了只有匹配 102 课程的学生才出现LEFT JOIN 那个“保住左表全部行”的语义完全失效了。原因在于 WHERE 子句的执行时机在连接完成之后。LEFT JOIN 先给王五补了一行 NULL然后 WHERE 对整张结果做过滤sc.course_id 102这个条件放到 NULL 上不成立王五那一行就被删掉了。所以只要把副表条件放在 WHERE外连接就退化成了内连接。正确写法是把课程条件放进 ONSELECT s.id, s.name, sc.course_id, sc.grade FROM student s LEFT JOIN score sc ON s.id sc.student_id AND sc.course_id 102;这下王五回来了右侧字段是 NULL张三只显示 102 那一条李四本来就没选 102右侧也是 NULL。你看条件写在 ON 里约束的是“右表拿什么行来跟左表拼”写进 WHERE 约束的是“拼完之后整个结果集留下哪些行”。这两个执行顺序上的差异就是 LEFT JOIN 最容易让人翻车的根源。记忆方法你想限制哪张表的行集就在哪张表参与连接的那一刻通过 ON 条件提前设定。WHERE 永远是对连接结果的终筛别拿它去做外连接的单侧裁剪。4. 多表连接和自连接表一多思维就要分层4.1 三表连接顺序与别名规范前面讲的都是两张表业务里三张四张很常见。把学生、成绩、课程三张表一起连起来查“是谁、选了哪门课、考了多少分、课程名是什么”SELECT s.name, c.title, sc.grade FROM student s INNER JOIN score sc ON s.id sc.student_id INNER JOIN course c ON c.id sc.course_id ORDER BY s.name;这里要理解一个概念JOIN 的执行是逐步进行的。第一步先把 student 和 score 连成一个中间结果第二步再把中间结果和 course 连。虽然优化器可能会调整实际执行顺序内连接里它也拥有一定的重排自由但逻辑上你完全可以沿着“从主表出发一步步带出从表信息”的线索来写。写多表连接我总结出三条经验。第一给每张表起有意义且简短的别名。三张表以上全写表名会让 SQL 长到没法看而且一旦出现两张表都有 id 字段SELECT 里不写别名直接取 id数据库会直接报错。别名不是为了省那几个字符是为了让关联条件一眼可读。第二连接条件要带全。拿 score 这种中间表连接 student 和 course本质上是两两连接必须保证每个 JOIN 都有自己的 ON。不要指望用一个 WHERE 同时完成两个关联那会回到老式写法的混乱状态。第三LEFT JOIN 的顺序尤其不能乱。优化器可以重排内连接的表但未必敢动外连接的顺序因为外连接的结果语义和“哪张表做主表”强绑定。一个 LEFT JOIN 套一个 LEFT JOIN 的复杂查询如果主表顺序错了结果集的行数基准就错了。所以写复杂查询时先从业务的主表开始一层层往外连接。4.2 SELF JOIN同一张表自己和自己连有一类连接特别容易被忽略就是自连接。它的本质是同一张表在查询里出现两次按某种关系自己匹配自己。最典型的场景是员工表里的上下级关系。假设员工表是 emp字段有 id、name、manager_idmanager_id 存的是这个人的直属上级的 idCREATE TABLE emp ( id INT PRIMARY KEY, name VARCHAR(20), manager_id INT ); INSERT INTO emp VALUES (1, 张总, NULL), (2, 李经理, 1), (3, 王主管, 2), (4, 赵员工, 3), (5, 钱员工, 3);要查“每个员工的姓名和直接上级姓名”单靠一张表没法完成因为上级也在同一张表里。于是让 emp 表以两种身份出现SELECT e.name AS 员工, m.name AS 直接上级 FROM emp e LEFT JOIN emp m ON e.manager_id m.id;这里的关键点有两个。一是同一张表出现两次必须用不同别名区分否则数据库分不清你指的是哪一份。二是这里我刻意用了 LEFT JOIN因为张总的 manager_id 是 NULL如果写 INNER JOIN张总这个最高领导会被丢掉而业务查询显然希望所有员工都出现。自连接加 LEFT 组合就是典型的“保留主表全部人员”的诉求。这种写法在组织架构、目录层级、评论回复、好友关系里都非常常见。你只要看到一张表里有某个字段指向同表的另一个 id第一反应就应该是自连接。4.3 连接后重复行与聚合陷阱连接导致结果行数变多不是错误但很多人会在后续统计上吃亏。实际案例张三有两门成绩你想查“每位学生所选课程的平均分”直接这样连student JOIN score 后张三有两行。如果这时候再凑一个聚合函数AVG(grade)因为是按学生分组张三是 88 和 76平均 82没问题。但如果你的业务不是成绩表而是“订单表”和“订单明细表”这种一对多关系情况就不同了。你先把主订单表和明细表连接结果里主表字段重复出现多次再想统计“每个客户订单金额合计”如果统计字段来自明细表那没问题但你如果想顺带统计“每个客户的订单数量”直接COUNT(order.id)会把同一个订单因多条明细而重复计数。这种错误在报表开发里非常常见而且极难排查。我给的通用对策是先想清楚这一行数据代表的是哪个粒度的配对。统计字段属于哪个表就回到哪个表的粒度去统计。更稳妥的办法是先把明细表按需要的维度聚合好再和主表连接或者用COUNT(DISTINCT order.id)绕开重复。很多从“连接结果”里得出来的数看着不对劲不是因为 JOIN 写错了而是聚合粒度和连接粒度没对齐。另外提醒一个 LEFT JOIN 加 COUNT 的细节COUNT(*)会把右侧全是 NULL 的保留行也算进去而COUNT(右表字段)会忽略 NULL。想统计“没选课的学生人数”用COUNT(score.course_id)才是安全的用COUNT(*)会得出完全相反的结论。5. 实战复盘把学生成绩场景从内到外完整跑一遍5.1 数据准备建表与录入这一节相当于前面的集中演练。我按第二节的 DDL 建好 student、course、score 三张表并保证数据里有三类特殊情况王五没有成绩、course 103 无人选、score 表有 student_id99 的孤儿成绩。先把三张表的数据结构整理成一张对照表方便你一边跑查询一边验证。表数据特点连接时要观察的点student王五没有成绩LEFT JOIN 时应保留course103 计算机网络无学生选修右连接/RIGHT 场景能露出score99 号成绩找不到学生内连接会消失FULL 模拟时能露出5.2 内连接找出所有已选课的注册学生第一条需求列出所有有成绩记录的学生以及其课程和分数。SELECT s.name, c.title, sc.grade FROM student s INNER JOIN score sc ON s.id sc.student_id INNER JOIN course c ON c.id sc.course_id ORDER BY s.id, c.id;结果里不会出现王五因为 score 表没有王五的数据也不会出现student_id99那条成绩因为没有匹配的学生更不会出现 103 号课程因为没有人选它。内连接在一次查询里同时完成了双重过滤学生必须有成绩课程必须被成绩引用。这正说明内连接适用于“我要的是完整发生了的业务事实”。这条 SQL 的执行顺序你可以这样理解先连接 student 和 score把有关联的学生和成绩找出再连接 course把课程标题带出来。每连接一次信息就丰富一层。5.3 左连接所有学生都要显示哪怕没选课第二条需求所有学生的选课情况没选课的学生也要出现在清单里。这就必须用 LEFT JOIN 把 student 作为主表保住。SELECT s.id, s.name, c.title, sc.grade FROM student s LEFT JOIN score sc ON s.id sc.student_id LEFT JOIN course c ON c.id sc.course_id ORDER BY s.id;跑完你会看到王五那一行课程名和分数都是 NULL。这就是业务上常说的“全量清单”所有学生都在有些信息空缺。这里容易犯的错是觉得既然王五没有成绩课程名肯定没有就把第二个 LEFT JOIN 写成了 INNER JOIN。一旦中间那个 score 连接的结果里王五的 score 字段是 NULL再用 INNER JOIN course连接条件c.id sc.course_id会拿 NULL 去匹配结果 NULL 匹配不上王五这一行就被 course 连接给吃掉了。多表连接里主表能否一路保到底取决于这一路连接是否全部使用 LEFT JOIN。这个细节我踩过一次之后写多表外连接都会逐个检查每个 JOIN 的方向。5.4 右连接与全连接模拟把孤儿数据挖出来第三条需求找出没有学生选修的课程。这里换成 course 做主表用 LEFT JOIN 更顺SELECT c.id, c.title, sc.student_id FROM course c LEFT JOIN score sc ON c.id sc.course_id WHERE sc.student_id IS NULL;结果会输出 103 计算机网络因为没有任何成绩指向它。这种“用 LEFT JOIN 加 IS NULL 找不存在记录”的写法专业上叫反连接它的性能在很多场景下比NOT IN更稳定尤其是被判断的子查询可能包含 NULL 值的时候NOT IN 会因为 NULL 直接返回空结果而 LEFT JOIN 不会。再换一种需求同时查看没有成绩的学生、以及找不到学生的孤儿成绩。MySQL 没有全外连接照前面讲的 UNION 方案来SELECT s.id, s.name, sc.course_id, sc.grade FROM student s LEFT JOIN score sc ON s.id sc.student_id UNION SELECT s.id, s.name, sc.course_id, sc.grade FROM student s RIGHT JOIN score sc ON s.id sc.student_id ORDER BY id;结果里你既能看到王五也能看到 student_id99 那条成绩。这种查询的价值在数据质量复盘时特别明显它能一次性把一张表中“关系断裂”的两侧数据全部暴露出来。日常开发可能用不到但真到排查脏数据的时候这一招能顶三五个脚本。5.5 EXPLAIN 检查连接慢的时候先看这几个标志连接写对了还得能跑得快。很多人一遇到查询慢就急着加缓存、上中间件其实多表连接的慢查询大半原因出在连接字段没有有效索引。用 EXPLAIN 看连接计划EXPLAIN SELECT s.name, c.title, sc.grade FROM student s INNER JOIN score sc ON s.id sc.student_id INNER JOIN course c ON c.id sc.course_id;需要关注的几个字段type、key、rows、Extra。type 是 ALL 表示全表扫描连接时如果驱动表之外的关联表频繁出现 ALL意味着每次配对都要把整张表扫一遍key 显示实际用到的索引如果能通过主键定位type 会是 eq_ref执行效率非常高rows 是优化器估算要扫描的行数如果你看到 rows 数字相乘后大得离谱就说明某一步产生了海量配对。Extra 里如果出现 Using where通常意味着还有大量行被筛掉也可以回头检查连接条件是否有索引支撑。给连接字段建立索引是最直接的解药。比如 score 表的 student_id 和 course_id 虽然组成了联合主键但如果你建表时没有主键或索引连接 student 时就要反复全表扫描 score。常见优化就是给外键字段建普通索引ALTER TABLE score ADD INDEX idx_student (student_id); ALTER TABLE score ADD INDEX idx_course (course_id);另外说一个历史经验以前常讲“小表驱动大表”因为嵌套循环执行方式下驱动表越小循环次数越少。MySQL 8.0.18 以后有了 hash join无索引等值连接的策略变了但给小表做驱动、给大表关联键建索引这套思路依然不过时因为索引 access 的成本通常比 hash join 低得多。遇到连接慢先别再忙着调 SQL打开 EXPLAIN 看路径才是正经事。最后分享一个我自己的调试习惯遇到任何搞不懂连接结果差异的问题永远先用小数据集把每一步跑一遍把每张表的行数和连接后的行数列出来对照“先笛卡尔积、再按 ON 裁剪、再按 LEFT/RIGHT 补 NULL”的三步模型走一遍。你会发现绝大多数 ON 和 WHERE 放错位置的问题当场就能定位不需要瞎猜。连接这一关跨过去之后SQL 才算真正从“查单表”变成了“查业务”。