MySQL学习的一大重点之表的内外连接

在MySQL中，表的连接（Join）是SQL查询中非常重要的一个概念，它允许我们将两个或多个表中的数据按照某种关系组合起来。根据不同的需求，我们可以使用内连接（INNER JOIN）和外连接（OUTER JOIN）来实现这一点。立即掌握MySQL表连接核心技巧,从内连接到外连接一网打尽,学会用LEFT JOIN保留主表全量数据,RIGHT JOIN灵活转换外连接,避免ON与WHERE混用导致的查询陷阱,通过实战案例和经典面试题,快速提升SQL查询能力,让数据关联变得简单高效

0 ~> 表连接的基础本质

0.1 笛卡尔积（CROSS JOIN）

两张表的所有行进行全量排列组合，若表 A 有 m 行、表 B 有 n 行，则结果集为 m×n 行。笛卡尔积是所有表连接的底层基础，其他连接类型本质都是在笛卡尔积基础上做不同规则的筛选。

0.2 连接的分类体系

按照结果集的保留规则，分为四大类：

1. 内连接：仅保留两表满足连接条件的匹配行
2. 外连接：保留某一侧表的全部行，另一侧匹配填充、不匹配补 NULL
   1. 左外连接
   2. 右外连接
   3. 全外连接（MySQL 原生不支持，需间接实现）

1 ~> 内连接（INNER JOIN）

1.1 核心概念

内连接基于指定连接条件对两表笛卡尔积进行筛选，仅保留两张表中同时满足匹配条件的行，是业务开发中使用频率最高的连接查询方式。 底层等价于：笛卡尔积 + WHERE 连接条件过滤。

1.2 语法格式

存在两种语义完全等价的写法：

1. 隐式内连接：逗号分隔表，连接条件写在 WHERE 子句
2. 显式内连接：使用INNER JOIN ... ON ...语法，INNER关键字可省略

-- 1. 隐式内连接（传统写法）SELECT 字段列表 FROM 表1, 表2 WHERE 连接条件 AND 其他业务过滤条件;-- 2. 显式内连接（标准SQL写法，推荐）SELECT 字段列表FROM 表1 [INNER] JOIN 表2 ON 连接条件WHERE 其他业务过滤条件;

1.3 经典案例：查询 SMITH 的姓名与所属部门名称

基于 scott 示例库的emp员工表、dept部门表演示：

-- 隐式内连接写法SELECT ename, dname FROM EMP, DEPT WHERE EMP.deptno = DEPT.deptno   AND ename = 'SMITH';-- 显式内连接标准写法（语义分层更清晰）SELECT ename, dname FROM EMP INNER JOIN DEPT ON EMP.deptno = DEPT.deptno WHERE ename = 'SMITH';-- 条件全写在ON后的写法（内连接下结果等价，不推荐）SELECT ename, dname FROM EMP INNER JOIN DEPT ON EMP.deptno = DEPT.deptno    AND ename = 'SMITH';

1.4 编写规范说明

内连接场景下，过滤条件写在ON或WHERE中执行结果一致。但从代码可维护性角度，连接关联条件统一写 ON、业务过滤条件统一写 WHERE，逻辑分层更清晰。

2 ~> 外连接（OUTER JOIN）

2.1 左外连接（LEFT JOIN / LEFT OUTER JOIN）

2.1.1 核心概念

以LEFT JOIN左侧的表作为主表，左表的所有行都会被完整保留；右侧表根据连接条件进行匹配：

1. 匹配成功：填充右表对应字段数据
2. 匹配失败：右表对应字段全部填充为 NULL 适用场景：需要保留主表全量数据，同时关联从表补充信息的场景，例如「查询所有学生的成绩，缺考学生也要展示」。

2.1.2 语法格式

SELECT 字段列表FROM 左表 LEFT [OUTER] JOIN 右表ON 连接条件WHERE 后续结果过滤条件;

其中OUTER关键字可省略。

2.1.3 案例演示

基于学生表stu与成绩表exam演示：

-- 数据准备CREATE TABLE stu (id INT, name VARCHAR(30));  -- 学生表CREATE TABLE exam (id INT, grade INT);        -- 成绩表INSERT INTO stu VALUES(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');INSERT INTO exam VALUES(1, 56),(2,76),(11, 8);-- 左外连接：查询所有学生的成绩，无成绩的学生信息也保留SELECT * FROM stu LEFT JOIN exam ON stu.id = exam.id;

执行结果：stu表 4 条数据全部保留，id 为 3、4 的学生无对应成绩，成绩字段显示为 NULL。

2.2 右外连接（RIGHT JOIN / RIGHT OUTER JOIN）

2.2.1 核心概念

以RIGHT JOIN右侧的表作为主表，右表的所有行都会被完整保留；左侧表根据连接条件进行匹配：

1. 匹配成功：填充左表对应字段数据
2. 匹配失败：左表对应字段全部填充为 NULL

2.2.2 语法格式

SELECT 字段列表FROM 左表 RIGHT [OUTER] JOIN 右表ON 连接条件WHERE 后续结果过滤条件;

2.2.3 案例演示

-- 右外连接：查询所有成绩对应的学生信息，无对应学生的成绩也保留SELECT * FROM stu RIGHT JOIN exam ON stu.id = exam.id;

执行结果：exam表 3 条数据全部保留，id 为 11 的成绩无对应学生，学生字段显示为 NULL。

2.3 左右外连接的等价转换

右外连接可以通过调换两表的书写顺序，完全等价改写为左外连接，二者执行逻辑与结果完全一致。

-- 原右外连接语句SELECT * FROM stu RIGHT JOIN exam ON stu.id = exam.id;-- 等价改写为左外连接SELECT * FROM exam LEFT JOIN stu ON stu.id = exam.id;

2.4 高频考点：ON 与 WHERE 的本质区别（外连接场景）

外连接中ON与WHERE的执行时机与语义完全不同，是面试高频易错点：

1. ON 子句：在两表连接生成中间结果的阶段生效，是连接的匹配规则。即使条件不满足，主表的行也会被保留，仅从表字段补 NULL。
2. WHERE 子句：在连接生成最终结果集后生效，对所有行进行统一过滤。若 WHERE 条件包含从表字段的非空判断，会剔除 NULL 行，导致外连接退化为内连接效果。

-- 语句1：过滤条件写在ON中SELECT * FROM stu LEFT JOIN exam ON stu.id = exam.id AND exam.grade > 60;-- 结果：左表4行全部保留，仅匹配分数>60的成绩，不满足的成绩字段为NULL-- 语句2：过滤条件写在WHERE中SELECT * FROM stu LEFT JOIN exam ON stu.id = exam.id WHERE exam.grade > 60;-- 结果：仅保留2行匹配数据，等同于内连接，无成绩/成绩不及格的行被全部过滤

2.5 拓展：全外连接实现

MySQL 原生不支持 SQL 标准的FULL OUTER JOIN，可通过「左外连接 + UNION + 右外连接」间接实现，保留两张表的全部行。

SELECT * FROM stu LEFT JOIN exam ON stu.id = exam.idUNIONSELECT * FROM stu RIGHT JOIN exam ON stu.id = exam.id;

3 ~> 实战场景与经典题型

3.1 业务场景：全量部门员工关联查询

需求：列出所有部门名称及对应员工信息，同时展示没有员工的空部门。 核心思路：以部门表为主表，左外连接员工表。

-- 写法1：部门表左外连接员工表（推荐，语义直观）SELECT d.dname, e.* FROM dept d LEFT JOIN emp e ON d.deptno = e.deptnoORDER BY d.deptno ASC;-- 写法2：员工表右外连接部门表（语义等价）SELECT d.dname, e.* FROM emp e RIGHT JOIN dept d ON d.deptno = e.deptnoORDER BY d.deptno ASC;

3.2 经典 OJ 题目

1. LeetCode 178. 分数排名（Rank Scores） 核心考点：自连接、窗口排序函数、分组统计
2. LeetCode 626. 换座位（Exchange Seats） 核心考点：自连接、条件判断、行号关联

结尾