掌握 MySQL JOIN：内连接、左连接、右连接入门 – wiki基地

深入浅出：掌握 MySQL JOIN 查询 – 从内连接、左连接到右连接的完全入门指南

在关系型数据库的世界里，数据通常被分散存储在多个相互关联的表中，而不是全部挤在一个庞大的表里。这种设计有诸多好处，比如减少数据冗余、提高数据一致性和灵活性。然而，当我们进行数据查询时，经常需要将这些分散在不同表中的数据整合起来，以便获取完整的、有意义的信息。这时，关系型数据库的精髓——JOIN（连接）——就派上用场了。

如果你刚刚开始学习 SQL 和数据库，理解 JOIN 可能是你迈向高级数据库查询的关键一步。本文将带你深入了解 MySQL 中最常用也最重要的三种 JOIN 类型：内连接 (INNER JOIN)、左连接 (LEFT JOIN) 和 右连接 (RIGHT JOIN)。我们将通过丰富的例子、清晰的解释和可视化图解（通过文字描述模拟）来帮助你彻底掌握它们。

文章结构：

为什么需要 JOIN？理解关系型数据库中的连接
- 数据分散存储的好处
- 关系（Relation）与键（Keys）：主键与外键
- JOIN 的基本概念与作用
准备环境：创建示例数据库和表
- 设计 students 表和 courses_taken 表
- 插入示例数据
JOIN 的基本语法
- SELECT ... FROM table1 JOIN table2 ON condition
- 表别名（Aliases）的妙用
核心 JOIN 类型详解
- 内连接 (INNER JOIN)
  - 概念：交集
  - 可视化理解：Venn 图
  - 语法与示例：查询同时存在于两个表中的匹配数据
  - 详细步骤解析示例执行过程
- 左连接 (LEFT JOIN 或 LEFT OUTER JOIN)
  - 概念：左表全部 + 右表匹配
  - 可视化理解：Venn 图
  - 语法与示例：查询左表所有数据及其在右表中的匹配项，无匹配则显示 NULL
  - 详细步骤解析示例执行过程
  - 使用左连接查找不匹配的数据
- 右连接 (RIGHT JOIN 或 RIGHT OUTER JOIN)
  - 概念：右表全部 + 左表匹配
  - 可视化理解：Venn 图
  - 语法与示例：查询右表所有数据及其在左表中的匹配项，无匹配则显示 NULL
  - 详细步骤解析示例执行过程
  - 右连接与左连接的互换性
JOIN 类型对比与选择
- 汇总表格对比
- 根据需求选择合适的 JOIN 类型
进阶与实践建议
- 连接多个表
- ON 子句与 WHERE 子句的区别（在 JOIN 中的应用）
- JOIN 性能考虑（索引的重要性）
- 理解 NULL 值在 JOIN 结果中的含义
总结

1. 为什么需要 JOIN？理解关系型数据库中的连接

想象一下，你正在设计一个简单的学校数据库。你需要存储学生的信息（姓名、学号等）以及他们选修的课程信息。

一种粗暴的方式是将所有信息放在一个大表里：

StudentID	StudentName	CourseID	CourseName	EnrollmentDate
101	张三	1001	数据库原理	2023-09-01
101	张三	1002	数据结构	2023-09-01
102	李四	1001	数据库原理	2023-09-01
103	王五	(空)	(空)	(空)

这种方式存在明显的问题：

数据冗余： 张三和李四的姓名和学号在表中出现了多次。如果一个学生选了很多门课，他们的信息就会被重复存储很多次，浪费存储空间。
数据一致性问题： 如果张三改了名字，你需要在所有包含他信息的行里都进行修改。一旦遗漏了某个地方，数据就会变得不一致。
更新和删除异常： 如果你想删除张三选修数据结构的记录，你可能会不小心删掉张三的其他信息（如果设计不当）。

为了解决这些问题，关系型数据库采用范式化的设计思想，将数据分解到多个逻辑上相关的表中。在我们的学校例子中，我们可以创建两个表：

students 表： 存储学生的基本信息，每行代表一个唯一的学生。
courses_taken 表： 存储学生选课的记录，每行代表一个学生选修了一门特定的课程。

这两个表通过一个共同的字段建立联系：student_id。

`students` 表
`student_id` (PK)	`name`
101	张三
102	李四
103	王五
104	赵六

`courses_taken` 表		:———–
`enrollment_id` (PK)	`student_id` (FK)	`course_name`
1	101	数据库原理
2	101	数据结构
3	102	数据库原理
4	102	操作系统
5	101	算法基础
6	105	计算机网络

在这个设计中：

students.student_id 是 students 表的主键 (Primary Key)，唯一标识每个学生。
courses_taken.enrollment_id 是 courses_taken 表的主键，唯一标识每次选课记录。
courses_taken.student_id 是 courses_taken 表的外键 (Foreign Key)，它引用（或“指向”）students 表中的 student_id。这表示 courses_taken 表中的每一条记录都必须对应 students 表中存在的一个学生。外键是建立表之间关系的桥梁。

现在问题来了：如果我想知道每个学生选了哪些课（比如“张三选了数据库原理和数据结构”），我需要从 students 表获取学生的姓名，从 courses_taken 表获取课程名。这两个表的数据是分开的，如何将它们重新组合起来呢？

这就是 JOIN 的作用！ JOIN 操作允许你根据两个或多个表之间的关系（通常是通过匹配主键和外键）将这些表中的行组合起来，形成一个包含来自所有参与表的数据的新结果集。

理解 JOIN 的关键在于理解它如何根据你指定的连接条件 (ON clause) 来匹配不同表中的行。

2. 准备环境：创建示例数据库和表

为了方便后续的实践和理解，我们先在 MySQL 中创建上面提到的示例数据库和表，并插入一些数据。

“`sql
— 创建数据库 (如果不存在)
CREATE DATABASE IF NOT EXISTS school_db;

— 使用数据库
USE school_db;

— 创建 students 表
CREATE TABLE IF NOT EXISTS students (
student_id INT PRIMARY KEY,
name VARCHAR(100)
);

— 创建 courses_taken 表
CREATE TABLE IF NOT EXISTS courses_taken (
enrollment_id INT PRIMARY KEY,
student_id INT,
course_name VARCHAR(100),
— 添加外键约束，确保 courses_taken.student_id 引用 students.student_id
FOREIGN KEY (student_id) REFERENCES students(student_id)
— ON DELETE CASCADE — 可选，如果学生被删除，其选课记录也随之删除
— ON UPDATE CASCADE — 可选，如果学生ID更新，选课记录中的学生ID也随之更新
);

— 插入数据到 students 表
INSERT INTO students (student_id, name) VALUES
(101, ‘张三’),
(102, ‘李四’),
(103, ‘王五’), — 王五没有选课记录
(104, ‘赵六’); — 赵六也没有选课记录

— 插入数据到 courses_taken 表
INSERT INTO courses_taken (enrollment_id, student_id, course_name) VALUES
(1, 101, ‘数据库原理’),
(2, 101, ‘数据结构’),
(3, 102, ‘数据库原理’),
(4, 102, ‘操作系统’),
(5, 101, ‘算法基础’),
(6, 105, ‘计算机网络’); — 注意: student_id 105 不在 students 表中。
— ❗ 如果 courses_taken 表有外键约束并严格执行，这条插入会失败。
— 为了演示 RIGHT JOIN 时左边可能无匹配的情况（尽管这在有严格FK时很少发生），
— 我们暂时假设外键约束不严格或我们暂时移除它进行演示。
— 在实际生产环境中，外键约束是推荐的。
— 如果你的环境严格执行外键，可以忽略 student_id 105 的插入。
— 为了演示效果，我们在后面的 RIGHT JOIN 解释中会基于允许 105 存在的情况进行描述。
— 实际运行时如果外键生效，student_id 105 的数据不会被插入。

— 假设外键不严格执行，继续插入 105 的数据（或者你暂时删除 FK constraint）
— ALTER TABLE courses_taken DROP FOREIGN KEY courses_taken_ibfk_1; — 临时删除外键
INSERT INTO courses_taken (enrollment_id, student_id, course_name) VALUES (6, 105, ‘计算机网络’);
— ALTER TABLE courses_taken ADD FOREIGN KEY (student_id) REFERENCES students(student_id); — 演示后可以加回
`` **重要说明：** 在实际应用中，严格执行外键约束非常重要。这里插入student_id` 为 105 的记录是为了演示 RIGHT JOIN 的一种可能性（右表有但在左表无匹配），但这通常意味着你的数据存在异常或设计有缺陷。标准的有外键约束的数据库是不会允许这种情况发生的。在本文后续的 RIGHT JOIN 示例中，我们将同时说明“有严格外键”和“无严格外键（或数据异常）”两种情况下的结果差异。

现在我们有了 students 表和 courses_taken 表，以及一些示例数据，可以开始学习 JOIN 了。

3. JOIN 的基本语法

JOIN 查询的基本语法如下：

sql SELECT column_list FROM table1 JOIN table2 ON table1.joining_column = table2.joining_column;

SELECT column_list: 你想要从结果中选择的列。这些列可以来自 table1 或 table2，甚至两者都有。为了避免列名冲突（例如，两个表都有 name 列）或不确定性，通常建议使用 table_name.column_name 的形式来指定列。
FROM table1: 指定第一个表（通常称为左表，尤其在 LEFT JOIN 中这个顺序很重要）。
JOIN table2: 指定要与 table1 连接的第二个表（通常称为右表）。JOIN 关键字前面可以加上连接类型（INNER, LEFT, RIGHT 等）。如果只写 JOIN，默认是 INNER JOIN。
ON table1.joining_column = table2.joining_column: 指定连接条件。这是 JOIN 的核心！数据库会根据这个条件来匹配两个表中的行。只有当 table1 中某行的 joining_column 的值等于 table2 中某行的 joining_column 的值时，这两行才会被考虑组合到结果集中。

表别名 (Aliases)

为了简化查询语句，尤其是在表名较长或者连接多个表时，我们通常会给表设置别名：

sql SELECT s.name, ct.course_name FROM students AS s -- 给 students 表设置别名 s JOIN courses_taken AS ct ON s.student_id = ct.student_id; -- 给 courses_taken 表设置别名 ct

使用 AS 关键字来设置别名，然后就可以在 SELECT 和 ON 子句中使用这个别名来代替完整的表名。AS 关键字是可选的，所以 FROM students s 也是合法的。在复杂的查询中，使用别名可以大大提高语句的可读性和简洁性。

4. 核心 JOIN 类型详解

现在，让我们深入了解三种主要的 JOIN 类型。理解它们的区别是掌握 JOIN 的关键。我们将使用上面创建的 students 表和 courses_taken 表进行演示。

4.1 内连接 (INNER JOIN)

概念： 内连接是最常见的连接类型。它返回两个表中都存在匹配关系的行。换句话说，只有在 ON 子句指定的连接条件在两个表中都能找到匹配的行时，这些行才会被包含在结果集中。

可视化理解：Venn 图

如果我们用 Venn 图来表示两个表的记录集合，内连接的结果就是两个集合的交集部分。

+-------------------+ +---------------------+ | students | | courses_taken | | | | | | +---------------+ | | +-----------------+ | | | 张三 (101) | | | | (1, 101, 数据库) | | | | 李四 (102) | |-----| | (2, 101, 数据结构) | | | | 王五 (103) | | | | (3, 102, 数据库) | | | | 赵六 (104) | | | | (4, 102, 操作系统) | | | +---------------+ | | | (5, 101, 算法) | | | | | +-----------------+ | +-------------------+ +---------------------+ \ / \ / \ / +-------------------+ | INNER JOIN | | (匹配 student_id) | | | | 张三 | 数据库原理 | | 张三 | 数据结构 | | 张三 | 算法基础 | | 李四 | 数据库原理 | | 李四 | 操作系统 | +-------------------+

结果只包含那些在 students 表中有对应 student_id，并且在 courses_taken 表中也有相同 student_id 的记录。那些只存在于 students 表（如王五、赵六）或只存在于 courses_taken 表（如 student_id 为 105 的记录，如果它能插入的话）的记录都不会出现在结果中。

语法：

sql SELECT column_list FROM table1 INNER JOIN table2 ON table1.joining_column = table2.joining_column;
或者简写为：
sql SELECT column_list FROM table1 JOIN table2 ON table1.joining_column = table2.joining_column;
（默认就是 INNER JOIN）

示例：查询选修了课程的学生姓名和课程名

sql SELECT s.name AS student_name, -- 从 students 表选择 name 列，取别名 student_name ct.course_name -- 从 courses_taken 表选择 course_name 列 FROM students AS s -- 左表 students，别名 s INNER JOIN courses_taken AS ct ON s.student_id = ct.student_id; -- 右表 courses_taken，别名 ct -- 连接条件：s.student_id 等于 ct.student_id

示例执行过程及结果解析：

数据库扫描 students 表和 courses_taken 表。
对于 students 表的每一行，数据库尝试在 courses_taken 表中找到满足 s.student_id = ct.student_id 条件的匹配行。
匹配过程：
- students 行 (101, ‘张三’):
  - 在 courses_taken 中找到 (1, 101, ‘数据库原理’)：匹配成功。组合结果行：(‘张三’, ‘数据库原理’)。
  - 在 courses_taken 中找到 (2, 101, ‘数据结构’)：匹配成功。组合结果行：(‘张三’, ‘数据结构’)。
  - 在 courses_taken 中找到 (5, 101, ‘算法基础’)：匹配成功。组合结果行：(‘张三’, ‘算法基础’)。
- students 行 (102, ‘李四’):
  - 在 courses_taken 中找到 (3, 102, ‘数据库原理’)：匹配成功。组合结果行：(‘李四’, ‘数据库原理’)。
  - 在 courses_taken 中找到 (4, 102, ‘操作系统’)：匹配成功。组合结果行：(‘李四’, ‘操作系统’)。
- students 行 (103, ‘王五’):
  - 在 courses_taken 中找不到任何 student_id 等于 103 的行：无匹配。王五的行不会出现在最终结果中。
- students 行 (104, ‘赵六’):
  - 在 courses_taken 中找不到任何 student_id 等于 104 的行：无匹配。赵六的行不会出现在最终结果中。
- courses_taken 行 (6, 105, ‘计算机网络’) (如果存在且外键不严格):
  - 在 students 中找不到任何 student_id 等于 105 的行：无匹配。这条课程记录不会出现在最终结果中。

查询结果：

student_name	course_name
张三	数据库原理
张三	数据结构
张三	算法基础
李四	数据库原理
李四	操作系统

总结： 内连接用于查找两个表中都有对应记录的情况。它排除了那些只存在于其中一个表的记录。当你只需要获取相互关联的数据时，内连接是你的首选。

4.2 左连接 (LEFT JOIN 或 LEFT OUTER JOIN)

概念： 左连接返回左表 (FROM 子句中的第一个表) 中的所有行，以及右表 (JOIN 子句中的第二个表) 中与左表匹配的行。如果左表中的某行在右表中没有匹配项，那么结果集中右表对应的列将显示为 NULL。

可视化理解：Venn 图

左连接的结果是左表的全部内容加上与右表匹配的部分（即两个集合的交集）。

+-------------------+ +---------------------+ | students | | courses_taken | | | | | | +---------------+ | | +-----------------+ | | | 张三 (101) | |-----| | (1, 101, 数据库) | | | | 李四 (102) | |-----| | (2, 101, 数据结构) | | | | 王五 (103) | | | | (3, 102, 数据库) | | | | 赵六 (104) | | | | (4, 102, 操作系统) | | | +---------------+ | | | (5, 101, 算法) | | | --------> | | +-----------------+ | | | | | +-------------------+ +---------------------+ \ / \ / \ / +-------------------+ | LEFT JOIN | | (匹配 student_id) | | | | 张三 | 数据库原理 | | 张三 | 数据结构 | | 张三 | 算法基础 | | 李四 | 数据库原理 | | 李四 | 操作系统 | | 王五 | NULL | | 赵六 | NULL | +-------------------+
注意：这里的箭头表示“包含所有左表数据”。

语法：

sql SELECT column_list FROM table1 -- 左表 LEFT JOIN table2 -- 右表 ON table1.joining_column = table2.joining_column;
OUTER 关键字是可选的，LEFT JOIN 和 LEFT OUTER JOIN 是等效的。

示例：查询所有学生及其选修的课程（包括没有选课的学生）

sql SELECT s.name AS student_name, -- 从 students 表选择 name 列 ct.course_name -- 从 courses_taken 表选择 course_name 列 FROM students AS s -- 左表 students LEFT JOIN courses_taken AS ct ON s.student_id = ct.student_id; -- 右表 courses_taken -- 连接条件：s.student_id 等于 ct.student_id

示例执行过程及结果解析：

数据库扫描 students 表（左表）。
对于 students 表的每一行，数据库尝试在 courses_taken 表（右表）中找到满足 s.student_id = ct.student_id 条件的匹配行。
匹配过程：
- students 行 (101, ‘张三’):
  - 在 courses_taken 中找到 (1, 101, ‘数据库原理’)：匹配成功。组合结果行：(‘张三’, ‘数据库原理’)。
  - 在 courses_taken 中找到 (2, 101, ‘数据结构’)：匹配成功。组合结果行：(‘张三’, ‘数据结构’)。
  - 在 courses_taken 中找到 (5, 101, ‘算法基础’)：匹配成功。组合结果行：(‘张三’, ‘算法基础’)。
  - （对于左表的一行，如果在右表有多个匹配行，那么左表的这一行会与右表的所有匹配行分别组合，生成多行结果）。
- students 行 (102, ‘李四’):
  - 在 courses_taken 中找到 (3, 102, ‘数据库原理’)：匹配成功。组合结果行：(‘李四’, ‘数据库原理’)。
  - 在 courses_taken 中找到 (4, 102, ‘操作系统’)：匹配成功。组合结果行：(‘李四’, ‘操作系统’)。
- students 行 (103, ‘王五’):
  - 在 courses_taken 中找不到任何 student_id 等于 103 的行：无匹配。数据库仍然包含王五的行，并将其与右表的 NULL 值组合。组合结果行：(‘王五’, NULL)。
- students 行 (104, ‘赵六’):
  - 在 courses_taken 中找不到任何 student_id 等于 104 的行：无匹配。数据库仍然包含赵六的行，并将其与右表的 NULL 值组合。组合结果行：(‘赵六’, NULL)。
- courses_taken 行 (6, 105, ‘计算机网络’) (如果存在且外键不严格):
  - 在 students 中找不到任何 student_id 等于 105 的行：无匹配。由于这是 LEFT JOIN，并且这条记录只存在于右表且在左表无匹配，所以这条记录不会出现在最终结果中。

查询结果：

student_name	course_name
张三	数据库原理
张三	数据结构
张三	算法基础
李四	数据库原理
李四	操作系统
王五	NULL
赵六	NULL

使用左连接查找不匹配的数据：

左连接的一个常见用途是找出左表中那些在右表中没有匹配项的记录。这可以通过在 LEFT JOIN 的结果上添加 WHERE right_table.joining_column IS NULL 条件来实现。

示例：查找没有选修任何课程的学生

sql SELECT s.name AS student_name FROM students AS s LEFT JOIN courses_taken AS ct ON s.student_id = ct.student_id WHERE ct.enrollment_id IS NULL; -- 或者 WHERE ct.student_id IS NULL，因为如果右表无匹配，右表的所有列都是 NULL

查询结果：

student_name
王五
赵六

总结： 左连接适用于需要获取“左边”表的全部信息，并尽可能包含“右边”表相关信息的需求。即使右边没有匹配项，左边的记录也会被保留，右边对应的列则为 NULL。这非常适合查找“所有X及其对应的Y（如果存在）”，或者“所有X但没有对应的Y”的情况。

4.3 右连接 (RIGHT JOIN 或 RIGHT OUTER JOIN)

概念： 右连接与左连接类似，但它是以右表 (JOIN 子句中的第二个表) 为主。它返回右表中的所有行，以及左表 (FROM 子句中的第一个表) 中与右表匹配的行。如果右表中的某行在左表中没有匹配项，那么结果集中左表对应的列将显示为 NULL。

可视化理解：Venn 图

右连接的结果是右表的全部内容加上与左表匹配的部分（即两个集合的交集）。

+-------------------+ +---------------------+ | students | | courses_taken | | | | | | +---------------+ | | +-----------------+ | | | 张三 (101) | |-----| | (1, 101, 数据库) | | | | 李四 (102) | |-----| | (2, 101, 数据结构) | | | | 王五 (103) | | | | (3, 102, 数据库) | | | | 赵六 (104) | | | | (4, 102, 操作系统) | | | +---------------+ | | | (5, 101, 算法) | | | | | | (6, 105, 计算机网络)| -- 假设此数据存在 +-------------------+ | +-----------------+ | \ /------------ \ / \ / +-------------------+ | RIGHT JOIN | | (匹配 student_id) | | | | 张三 | 数据库原理 | | 张三 | 数据结构 | | 张三 | 算法基础 | | 李四 | 数据库原理 | | 李四 | 操作系统 | | NULL | 计算机网络 | -- 假设 student_id 105 存在于右表但左表无匹配 +-------------------+
注意：这里的箭头表示“包含所有右表数据”。

语法：

sql SELECT column_list FROM table1 -- 左表 RIGHT JOIN table2 -- 右表 ON table1.joining_column = table2.joining_column;
OUTER 关键字也是可选的，RIGHT JOIN 和 RIGHT OUTER JOIN 是等效的。

示例：查询所有选课记录及其对应的学生姓名（包括那些可能没有对应学生的选课记录 – 如果数据存在异常）

sql SELECT s.name AS student_name, -- 从 students 表选择 name 列 ct.course_name -- 从 courses_taken 表选择 course_name 列 FROM students AS s -- 左表 students RIGHT JOIN courses_taken AS ct ON s.student_id = ct.student_id; -- 右表 courses_taken -- 连接条件：s.student_id 等于 ct.student_id

示例执行过程及结果解析：

数据库扫描 courses_taken 表（右表）。
对于 courses_taken 表的每一行，数据库尝试在 students 表（左表）中找到满足 s.student_id = ct.student_id 条件的匹配行。
匹配过程：
- courses_taken 行 (1, 101, ‘数据库原理’):
  - 在 students 中找到 (101, ‘张三’): 匹配成功。组合结果行：(‘张三’, ‘数据库原理’)。
- courses_taken 行 (2, 101, ‘数据结构’):
  - 在 students 中找到 (101, ‘张三’): 匹配成功。组合结果行：(‘张三’, ‘数据结构’)。
- courses_taken 行 (3, 102, ‘数据库原理’):
  - 在 students 中找到 (102, ‘李四’): 匹配成功。组合结果行：(‘李四’, ‘数据库原理’)。
- courses_taken 行 (4, 102, ‘操作系统’):
  - 在 students 中找到 (102, ‘李四’): 匹配成功。组合结果行：(‘李四’, ‘操作系统’)。
- courses_taken 行 (5, 101, ‘算法基础’):
  - 在 students 中找到 (101, ‘张三’): 匹配成功。组合结果行：(‘张三’, ‘算法基础’)。
- courses_taken 行 (6, 105, ‘计算机网络’) (如果存在且外键不严格):
  - 在 students 中找不到任何 student_id 等于 105 的行：无匹配。数据库仍然包含这条课程记录，并将其与左表的 NULL 值组合。组合结果行：(NULL, ‘计算机网络’)。
- students 行 (103, ‘王五’) 和 (104, ‘赵六’):
  - 在 courses_taken 中找不到任何匹配行。由于这是 RIGHT JOIN，并且这些记录只存在于左表且在右表无匹配，所以这些记录不会出现在最终结果中。

查询结果：

student_name	course_name
张三	数据库原理
张三	数据结构
张三	算法基础
李四	数据库原理
李四	操作系统
NULL	计算机网络

重要提示： 在严格遵循关系型数据库设计（使用外键约束）的情况下，courses_taken 表中的 student_id 必须在 students 表中存在。因此，示例数据中 student_id 为 105 的记录在有外键约束时是无法插入的。在这种标准情况下，RIGHT JOIN 的结果将只包含前 5 行，与 INNER JOIN 的结果相同。这是因为右表 (courses_taken) 中的所有 student_id 都能在左表 (students) 中找到匹配。

右连接与左连接的互换性：

仔细观察 LEFT JOIN 和 RIGHT JOIN 的定义和结果，你会发现大多数时候一个 RIGHT JOIN 查询可以被等效地改写为一个 LEFT JOIN 查询，只需要交换 FROM 和 JOIN 子句中的表顺序。

例如，上面的 RIGHT JOIN 查询：
sql SELECT s.name AS student_name, ct.course_name FROM students AS s RIGHT JOIN courses_taken AS ct ON s.student_id = ct.student_id;
可以改写为 LEFT JOIN：
sql SELECT s.name AS student_name, ct.course_name FROM courses_taken AS ct -- 现在 courses_taken 是左表 LEFT JOIN students AS s -- 现在 students 是右表 ON ct.student_id = s.student_id; -- 连接条件依然是 student_id 相等
这个 LEFT JOIN 的结果将与上面的 RIGHT JOIN 结果完全相同。

由于这种互换性，许多数据库开发者更倾向于只使用 LEFT JOIN，因为始终将主要的表放在 FROM 子句中（作为左表）可以帮助保持查询逻辑的一致性。RIGHT JOIN 相对较少使用。

总结： 右连接适用于需要获取“右边”表的全部信息，并尽可能包含“左边”表相关信息的需求。即使左边没有匹配项，右边的记录也会被保留，左边对应的列则为 NULL。在实践中，RIGHT JOIN 较少直接使用，常被等效的 LEFT JOIN 替代。

5. JOIN 类型对比与选择

下表总结了 INNER JOIN, LEFT JOIN 和 RIGHT JOIN 的主要区别：

特性	INNER JOIN (内连接)	LEFT JOIN (左连接)	RIGHT JOIN (右连接)
匹配方式	返回两个表中所有匹配的行	返回左表所有行 + 右表匹配的行	返回右表所有行 + 左表匹配的行
无匹配行	不包含来自任何一个表且无匹配的行	保留左表中无匹配的行，右表列显示 NULL	保留右表中无匹配的行，左表列显示 NULL
Venn 图	交集	左圆 + 交集	右圆 + 交集
适用场景	只需获取两个表都有关联的数据记录时	需要获取左表全部数据，并关联右表数据时	需要获取右表全部数据，并关联左表数据时
常见用途	查找共同的用户、订单项等	查找所有用户及其订单 (有无都列出)	查找所有订单项及其关联的用户 (即使用户已删除)
与对方互换	与 INNER JOIN 互换	可与 RIGHT JOIN 互换 (交换表顺序)	可与 LEFT JOIN 互换 (交换表顺序)

如何选择合适的 JOIN 类型？

选择哪种 JOIN 类型取决于你想要在结果中包含哪些数据：

如果你只关心两个表中都有对应记录的数据（例如，“哪些学生选了哪些课”），使用 INNER JOIN。
如果你想列出左表的所有记录，并包含它们在右表中的匹配记录（例如，“列出所有学生以及他们选的课，包括没选课的学生”），使用 LEFT JOIN。
如果你想列出右表的所有记录，并包含它们在左表中的匹配记录（例如，“列出所有选课记录以及对应的学生，包括可能没有对应学生的异常记录”），使用 RIGHT JOIN。但考虑到可读性，通常推荐将这个 RIGHT JOIN 改写为等价的 LEFT JOIN。

在实际开发中，LEFT JOIN 的使用频率通常高于 RIGHT JOIN，因为它更符合“从一个主表出发，查找相关联信息”的思维模式。

6. 进阶与实践建议

6.1 连接多个表

关系型数据库通常包含许多表。你可以使用 JOIN 将三个或更多的表连接起来。语法上，你只需要连续使用 JOIN 子句：

sql SELECT ... FROM table1 JOIN table2 ON table1.col1 = table2.col2 JOIN table3 ON table2.col3 = table3.col4 WHERE ...;
连接多个表时，每增加一个 JOIN 子句，就需要提供一个连接条件来指定如何将当前结果集与新的表连接。连接的顺序可能会影响性能，但通常不会影响最终结果的正确性（除非使用了外连接且连接条件复杂）。

6.2 ON 子句与 WHERE 子句的区别（在 JOIN 中的应用）

这是初学者经常困惑的地方。虽然它们都用于过滤数据，但在 JOIN 中的作用不同：

ON 子句： 用于指定如何连接两个表。它定义了两个表中的行如何进行匹配以形成中间结果集。ON 条件在 JOIN 操作执行之前应用。对于 OUTER JOIN (LEFT/RIGHT JOIN)，即使 ON 条件不满足，主表（左表或右表）的行也会被保留，只是另一个表的列会显示 NULL。
WHERE 子句： 用于在 JOIN 操作完成之后过滤结果集。它在 FROM 和 JOIN 子句生成了完整的连接结果集后应用。WHERE 条件会从这个结果集中移除不符合条件的行。

示例：

假设我们想查找所有选了“数据库原理”课的学生：

使用 ON 过滤（错误示范，尤其在 OUTER JOIN 时）：
sql -- 错误的方式来过滤课程名（对于 INNER JOIN 可能工作，但对于 OUTER JOIN 会改变语义） SELECT s.name, ct.course_name FROM students AS s INNER JOIN courses_taken AS ct ON s.student_id = ct.student_id AND ct.course_name = '数据库原理'; -- 对于 INNER JOIN，这等价于在 WHERE 中过滤
使用 WHERE 过滤（正确且通用的方式）：
“`sql
— 正确的方式来过滤课程名
SELECT s.name, ct.course_name
FROM students AS s
INNER JOIN courses_taken AS ct ON s.student_id = ct.student_id
WHERE ct.course_name = ‘数据库原理’;

— 对于 LEFT JOIN 来说，区别就很重要了
SELECT s.name, ct.course_name
FROM students AS s
LEFT JOIN courses_taken AS ct ON s.student_id = ct.student_id AND ct.course_name = ‘数据库原理’;
— 结果是：所有学生，以及他们选的“数据库原理”课。没选这门课的学生，课程名显示 NULL。

SELECT s.name, ct.course_name
FROM students AS s
LEFT JOIN courses_taken AS ct ON s.student_id = ct.student_id
WHERE ct.course_name = ‘数据库原理’;
— 结果是：只显示选了“数据库原理”课的学生。没有选这门课的学生（包括没选任何课的学生）会被 WHERE 子句过滤掉。
`` 可以看到，对于 LEFT JOIN，在ON中添加条件会影响连接的结果集（保留了左表所有行并根据条件匹配右表），而在WHERE中添加条件则是在连接结果集的基础上再进行过滤。理解这个区别对于编写正确的 JOIN 查询至关重要。通常，连接条件放在ON中，而对连接结果集的进一步过滤放在WHERE` 中。

6.3 JOIN 性能考虑（索引的重要性）

JOIN 操作的性能在很大程度上取决于数据库如何有效地找到匹配的行。如果在 ON 子句中使用的列上建立了索引，数据库通常可以更快地定位匹配项，从而显著提高 JOIN 查询的速度。

在我们示例中，students.student_id 和 courses_taken.student_id 是连接列。students.student_id 是主键，通常会自动创建索引。如果 courses_taken.student_id 是外键，并且通常也会自动或手动为其创建索引，这将极大地优化涉及这两个表的 JOIN 查询。

在设计数据库时，确保用于连接的列（特别是外键）上建立了索引是提高查询性能的关键措施。

6.4 理解 NULL 值在 JOIN 结果中的含义

在 LEFT JOIN 和 RIGHT JOIN 的结果中，NULL 值表示在另一个表中没有找到匹配的行。当你在处理 JOIN 结果时，需要注意 NULL 值的存在，并在进行计算、过滤或显示时妥善处理它们（例如，使用 IFNULL() 或 COALESCE() 函数将 NULL 替换为其他值，或使用 IS NULL/IS NOT NULL 进行条件判断）。

7. 总结

恭喜你！通过本文，你已经深入了解了 MySQL 中三种重要的 JOIN 类型：INNER JOIN、LEFT JOIN 和 RIGHT JOIN。你学会了：

理解为什么需要 JOIN 来整合来自多个表的数据。
掌握 JOIN 的基本语法和表别名的使用。
区分 INNER JOIN（交集）、LEFT JOIN（左表全部+交集）和 RIGHT JOIN（右表全部+交集）的概念和用法。
通过示例了解每种 JOIN 类型如何根据连接条件生成结果集，以及无匹配行如何被处理（是否保留，是否出现 NULL）。
理解 LEFT JOIN 和 RIGHT JOIN 的互换性，以及为何 LEFT JOIN 更常用。
认识到 ON 子句用于连接条件，而 WHERE 子句用于结果集过滤。
了解索引对 JOIN 性能的重要性。

JOIN 是关系型数据库查询的基石之一。熟练掌握不同类型的 JOIN 是编写强大、高效 SQL 查询的关键。实践是最好的老师，尝试修改本文的示例数据和查询，观察结果的变化，这将帮助你更牢固地掌握这些概念。

继续加油，在数据库的世界里探索更多有趣的查询和功能吧！

深入浅出：掌握 MySQL JOIN 查询 – 从内连接、左连接到右连接的完全入门指南

1. 为什么需要 JOIN？理解关系型数据库中的连接

2. 准备环境：创建示例数据库和表

3. JOIN 的基本语法

4. 核心 JOIN 类型详解

4.1 内连接 (INNER JOIN)

4.2 左连接 (LEFT JOIN 或 LEFT OUTER JOIN)

4.3 右连接 (RIGHT JOIN 或 RIGHT OUTER JOIN)

5. JOIN 类型对比与选择

6. 进阶与实践建议

6.1 连接多个表

6.2 ON 子句与 WHERE 子句的区别（在 JOIN 中的应用）

6.3 JOIN 性能考虑（索引的重要性）

6.4 理解 NULL 值在 JOIN 结果中的含义

7. 总结

发表评论 取消回复

发表评论取消回复