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 连接的分类体系

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

• 内连接：仅保留两表满足连接条件的匹配行
• 外连接：保留某一侧表的全部行，另一侧匹配填充、不匹配补 null
  • 左外连接
  • 右外连接
  • 全外连接（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左侧的表作为主表，左表的所有行都会被完整保留；右侧表根据连接条件进行匹配：

• 匹配成功：填充右表对应字段数据
• 匹配失败：右表对应字段全部填充为 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右侧的表作为主表，右表的所有行都会被完整保留；左侧表根据连接条件进行匹配：

• 匹配成功：填充左表对应字段数据
• 匹配失败：左表对应字段全部填充为 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.id
union
select * 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.deptno
order by d.deptno asc;
-- 写法2：员工表右外连接部门表（语义等价）
select d.dname, e.* 
from emp e right join dept d 
on d.deptno = e.deptno
order by d.deptno asc;

3.2 经典 oj 题目

1. leetcode 178. 分数排名（rank scores） 核心考点：自连接、窗口排序函数、分组统计
2. leetcode 626. 换座位（exchange seats） 核心考点：自连接、条件判断、行号关联

结尾

到此这篇关于mysql学习的一大重点之表的内外连接的文章就介绍到这了,更多相关mysql表的内外连接内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！