JOIN 连接

关系型数据库的精髓在于"把数据拆成多张表,用的时候再连起来"。比如学生表存基本信息、班级表存班级信息,通过 class_id 关联——这种设计避免了"同一条班级信息重复存"的冗余。把多张表连起来查询,用的就是 JOIN。

准备演示数据

为了讲清 JOIN 的几种类型,我们先准备两张表:

-- 准备两张表用来演示 JOIN
CREATE TABLE classes (
    id         INT PRIMARY KEY,
    class_name VARCHAR(50)
);

CREATE TABLE students (
    id       INT PRIMARY KEY,
    name     VARCHAR(50),
    class_id INT              -- 指向 classes.id
);

INSERT INTO classes VALUES (1, '一班'), (2, '二班'), (3, '三班');
INSERT INTO students VALUES
(10, '小明', 1),
(11, '小红', 1),
(12, '小刚', 2),
(13, '小华', NULL);          -- 这位同学还没分班

注意"小华"的 class_id 是 NULL(还没分班),后面会看到不同 JOIN 对它的处理。

1. INNER JOIN 内连接(最常用)

INNER JOIN 只返回两表都能匹配上的行。日常 80% 的 JOIN 都是这种。

-- INNER JOIN(内连接):只返回两表【都匹配】的行
-- 最常用的 JOIN,可省略 INNER 写成 JOIN
SELECT s.name, c.class_name
FROM students s
INNER JOIN classes c ON s.class_id = c.id;

-- 结果:
-- name | class_name
-- 小明 | 一班
-- 小红 | 一班
-- 小刚 | 二班
-- (小华因为 class_id 是 NULL,没匹配上,不出现)

关键点:小华因为 class_id 是 NULL,在 INNER JOIN 的结果里不出现——它没有匹配的 classes 行。

2. LEFT JOIN 左连接(保留全部左表)

LEFT JOIN 保留左表的全部行,右表没匹配的字段填 NULL。常用于"列出所有 X,即使没有 Y 也要显示"的场景:

-- LEFT JOIN(左连接):左表【全保留】,右表没匹配的字段填 NULL
SELECT s.name, c.class_name
FROM students s
LEFT JOIN classes c ON s.class_id = c.id;

-- 结果:
-- name | class_name
-- 小明 | 一班
-- 小红 | 一班
-- 小刚 | 二班
-- 小华 | NULL          ← 左表的"小华"被保留,右表字段为 NULL

-- 实战:找出【没分班】的学生(右表关键字段为 NULL)
SELECT s.name
FROM students s
LEFT JOIN classes c ON s.class_id = c.id
WHERE c.id IS NULL;
-- 结果:小华

一个经典技巧:用 LEFT JOIN + WHERE 右表字段 IS NULL 找出"没匹配"的行。比如"找出没分班的学生"、"找出没下过单的用户"、"找出从没评论过的文章"。

3. RIGHT JOIN 右连接

RIGHT JOIN 反过来——右表全保留,左表没匹配的字段填 NULL。实际很少用,因为把表换个位置用 LEFT 即可:

-- RIGHT JOIN(右连接):右表【全保留】,左表没匹配的字段填 NULL
-- 和 LEFT JOIN 反过来,实际很少用(把表换个位置用 LEFT 即可)
SELECT s.name, c.class_name
FROM students s
RIGHT JOIN classes c ON s.class_id = c.id;

-- 结果(假设三班没人):
-- name | class_name
-- 小明 | 一班
-- 小红 | 一班
-- 小刚 | 二班
-- NULL | 三班          ← 右表的"三班"被保留,左表字段为 NULL

4. FULL JOIN 全外连接

FULL JOIN 把左右两表都全保留,任一边没匹配的字段都填 NULL。MySQL 不直接支持,要用 UNION 合并 LEFT JOIN 和 RIGHT JOIN:

-- FULL JOIN(全连接 / 全外连接):左右两表【都全保留】
-- MySQL 不直接支持,要用 UNION 合并 LEFT JOIN 和 RIGHT JOIN
-- PostgreSQL / Oracle / SQL Server 原生支持

-- 标准 FULL JOIN
SELECT s.name, c.class_name
FROM students s
FULL OUTER JOIN classes c ON s.class_id = c.id;

-- MySQL 模拟
SELECT s.name, c.class_name FROM students s LEFT JOIN classes c ON s.class_id = c.id
UNION
SELECT s.name, c.class_name FROM students s RIGHT JOIN classes c ON s.class_id = c.id;

5. CROSS JOIN 笛卡尔积

CROSS JOIN 返回两表的所有组合(M 行 × N 行 = M×N 行)。慎用,结果集会爆炸:

-- CROSS JOIN(交叉连接 / 笛卡尔积):返回两表【所有组合】
-- 行数 = 左表行数 × 右表行数,慎用!
SELECT s.name, c.class_name
FROM students s
CROSS JOIN classes c;

-- 4 个学生 × 3 个班级 = 12 行结果
-- 实战场景:生成所有"学生-班级"的组合用于排课、批量赋值

-- 陷阱:忘了写 ON 条件的 INNER JOIN 会变成 CROSS JOIN
SELECT * FROM students JOIN classes;     -- 笛卡尔积,小心!

实战场景有限:生成所有"用户 × 角色"组合用于批量赋权、生成测试数据。一个常见陷阱:忘写 ON 条件的 INNER JOIN 会退化成 CROSS JOIN,百万行表上一跑就把库卡死。

6. 多表连续 JOIN

真实业务里 JOIN 3-5 张表是家常便饭。语法就是连续 JOIN:

-- 多表连接:连续 JOIN 三张或更多表
-- 假设有 students / classes / teachers 三张表
-- students.class_id → classes.id
-- classes.teacher_id → teachers.id

SELECT s.name AS student, c.class_name, t.name AS teacher
FROM students s
JOIN classes c   ON s.class_id = c.id
JOIN teachers t  ON c.teacher_id = t.id
WHERE c.class_name = '一班';

-- JOIN 的执行顺序:从左到右
-- 先 students JOIN classes → 中间结果 JOIN teachers
-- 给表起单字母别名(s/c/t),SQL 短很多,真实项目几乎都用别名

-- ⚠️ JOIN 太多表会显著变慢,通常 ≤ 5 张表
-- 如果超过,考虑反范式(冗余字段)或拆成多次查询

给表起单字母别名(s、c、t)能让 SQL 短小精悍,真实项目里几乎都用别名。但要小心:JOIN 太多表会显著变慢,通常 ≤ 5 张。超过的话考虑反范式设计(冗余一些字段)或拆成多次查询。

7. 自连接(self join)

有时一张表里有关联自己的字段——最经典的例子是员工表里的 manager_id 指向另一名员工。这时用自连接:同一张表用两个不同别名 JOIN 起来:

-- 自连接(self join):一张表和自己 JOIN
-- 需要给同一张表起两个不同别名

-- 场景:员工表里 manager_id 指向另一名员工
CREATE TABLE employees (
    id        INT PRIMARY KEY,
    name      VARCHAR(50),
    manager_id INT           -- 上司的 id
);

-- 查"每个员工和他的直接上司"
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- 同一张表用两次,分别起别名 e(员工)和 m(上司)

自连接还常用于"查找同类项"、"层级关系"(树形结构、组织架构)。配合递归 CTE(WITH RECURSIVE)能查任意深度的层级。

8. 老式 JOIN 语法(避坑)

看老代码或某些教程时,会遇到逗号分隔的 JOIN 写法。它等价于 INNER JOIN,但不推荐:

-- 老式 JOIN 语法(逗号分隔,WHERE 写连接条件)
SELECT s.name, c.class_name
FROM students s, classes c
WHERE s.class_id = c.id;

-- 等价于 INNER JOIN,但【不推荐】:
-- 1. 忘了 WHERE 条件就变成笛卡尔积
-- 2. 连接条件和过滤条件混在 WHERE 里,可读性差
-- 3. LEFT/RIGHT JOIN 没法用这种语法

-- 现代写法永远用显式 JOIN ... ON ...

原因:连接条件和过滤条件混在 WHERE 里可读性差;忘了 WHERE 就变成笛卡尔积;LEFT/RIGHT JOIN 没法用这种语法。现代写法永远用显式 JOIN ... ON ...。

JOIN 类型对照速查

• INNER JOIN:两表都匹配的行(最常用)。
• LEFT JOIN:左表全保留,右表不匹配填 NULL。
• RIGHT JOIN:右表全保留,左表不匹配填 NULL(很少用)。
• FULL JOIN:左右都保留(两表不匹配的都填 NULL)。
• CROSS JOIN:笛卡尔积(M×N 行)。
• self join:同一张表的自连接(用别名区分)。

小结

JOIN 是 SQL 区别于其他语言的最大特色。理解 JOIN 的关键是搞清楚"哪张表的数据不能丢"——INNER 是两边都不能丢、LEFT 是左表不能丢、RIGHT 是右表不能丢、FULL 是两边都不能丢。下一篇我们看另一个核心概念:GROUP BY 分组聚合。

← 上一篇 WHERE 与排序

下一篇 GROUP BY 分组 →