第 7 章多表连接JOIN

站长2026-10-22 16:00:00

内容摘要

真实业务里数据是分散在多张表里的——学生表、课程表、成绩表、院系表。要把它们"拼"在一起查，就靠 `JOIN`。本篇讲最常用的 INNER JOIN、LEFT JOIN、RIGHT JOIN、CROSS JOIN，以及自连接、多表连接，重点区分 ON 与 WHERE 的差异、LEFT JOIN 后加 WHERE 会变 INNER 的经典坑。学完你能写出后台 80% 的列表查询 SQL。本示例基于 MySQL 8.0，所有 SQL 均按该版本语法书写。

📖 本篇简介：真实业务里数据是分散在多张表里的——学生表、课程表、成绩表、院系表。要把它们"拼"在一起查，就靠 JOIN。本篇讲最常用的 INNER JOIN、LEFT JOIN、RIGHT JOIN、CROSS JOIN，以及自连接、多表连接，重点区分 ON 与 WHERE 的差异、LEFT JOIN 后加 WHERE 会变 INNER 的经典坑。学完你能写出后台 80% 的列表查询 SQL。本示例基于 MySQL 8.0，所有 SQL 均按该版本语法书写。

1 为什么需要 JOIN

假设我们有两张表：

students：学生（id, name, dept_id）
departments：院系（id, dept_name）

学生表里只存了 dept_id（数字），不存院系名字。要查"张三在哪个院系"，就得把两张表"按 dept_id 相等"拼起来——这就是 JOIN。

不写 JOIN 怎么办？用子查询或者在应用里分两次查询再拼，但效率低、代码啰嗦。JOIN 是数据库层面的"高效拼图"。

2 示例表准备

为了贯穿全篇，先建两张示例表并插入测试数据：

-- 院系表
DROP TABLE IF EXISTS departments;
CREATE TABLE departments (
    dept_id    INT PRIMARY KEY,
    dept_name  VARCHAR(50) NOT NULL
);

INSERT INTO departments VALUES
    (1, '计算机系'),
    (2, '中文系'),
    (3, '机械系');
-- 注意：没有 dept_id=4 的院系

-- 学生表
DROP TABLE IF EXISTS students;
CREATE TABLE students (
    id       INT PRIMARY KEY,
    name     VARCHAR(50),
    dept_id  INT,
    score    DECIMAL(5,2)
);

INSERT INTO students VALUES
    (1, '张三', 1, 90.0),
    (2, '李四', 1, 85.0),
    (3, '王五', 2, 78.0),
    (4, '赵六', NULL, 60.0),     -- 没分配院系
    (5, '钱七', 99, 50.0);        -- dept_id=99 在院系表里不存在（孤儿数据）

注意两个"特殊学生"：赵六 dept_id 是 NULL，钱七 dept_id=99 在 departments 里没有。这种"对不上"的数据是讲 JOIN 差异的关键。

3 INNER JOIN 内连接

3.1 基本语法

INNER JOIN 只保留两边都匹配的行：

-- 查每个学生的姓名和所在院系名
SELECT s.name, d.dept_name
FROM students s
INNER JOIN departments d ON s.dept_id = d.dept_id;

结果：

| name | dept_name |

|---|---|

| 张三 | 计算机系 |

| 李四 | 计算机系 |

| 王五 | 中文系 |

赵六（dept_id=NULL）和钱七（dept_id=99）都没出现在结果里——因为他们在 departments 表里找不到匹配行。这就是"内连接"的含义：两表交集。

3.2 表别名

students s 给 students 起了别名 s，departments d 起了别名 d。写 JOIN 时几乎都要用别名，否则列名前面要写全表名很长。

3.3 多个连接条件

-- 假设还要限定成绩 >= 80
SELECT s.name, d.dept_name, s.score
FROM students s
INNER JOIN departments d ON s.dept_id = d.dept_id
WHERE s.score >= 80;

WHERE 是在 JOIN 完之后再过滤。

4 LEFT JOIN 左连接

4.1 基本语法

LEFT JOIN（也叫 LEFT OUTER JOIN）保留左表所有行，右表匹配不上的列填 NULL：

-- 查所有学生，不管他有没有院系
SELECT s.name, d.dept_name
FROM students s
LEFT JOIN departments d ON s.dept_id = d.dept_id;

结果：

| name | dept_name |

|---|---|

| 张三 | 计算机系 |

| 李四 | 计算机系 |

| 王五 | 中文系 |

| 赵六 | NULL |

| 钱七 | NULL |

赵六、钱七都出现了，只是 dept_name 是 NULL。这就是"左连接"——左表全保留，右表对不上补 NULL。

4.2 找"孤儿数据"

LEFT JOIN 最经典的用法是找"左表里在右表没有匹配"的行：

-- 找出没有院系的学生（dept_id 为 NULL 或在 departments 里不存在）
SELECT s.id, s.name
FROM students s
LEFT JOIN departments d ON s.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

结果：

| id | name |

|---|---|

| 4 | 赵六 |

| 5 | 钱七 |

这种"LEFT JOIN + WHERE 右表 IS NULL"是排查外键完整性的标准写法。

4.3 LEFT JOIN 与聚合

-- 统计每个院系的学生人数（包括没有学生的院系）
SELECT d.dept_name, COUNT(s.id) AS student_count
FROM departments d
LEFT JOIN students s ON d.dept_id = s.dept_id
GROUP BY d.dept_id, d.dept_name;

注意是 COUNT(s.id) 而不是 COUNT(*)——因为 LEFT JOIN 后，没有学生的院系那一行 s.id 是 NULL，COUNT(*) 会把它数成 1，COUNT(s.id) 才是真实学生数。

5 RIGHT JOIN 右连接

RIGHT JOIN 保留右表所有行，左表对不上补 NULL。逻辑上和 LEFT JOIN 对称：

-- 查所有院系，包括没有学生的
SELECT d.dept_name, s.name
FROM students s
RIGHT JOIN departments d ON s.dept_id = d.dept_id;

结果：

| dept_name | name |

|---|---|

| 计算机系 | 张三 |

| 计算机系 | 李四 |

| 中文系 | 王五 |

| 机械系 | NULL |

机械系没有学生，但因为在右表 departments 里，所以保留，左表 s.name 填 NULL。

实战建议：RIGHT JOIN 用得很少，因为它和 LEFT JOIN 只是"把表顺序反过来"。所有人都习惯把"要全保留的表"放左边，用 LEFT JOIN。

6 CROSS JOIN 笛卡尔积

CROSS JOIN 把左表每一行和右表每一行拼接，结果行数 = 左表行数 × 右表行数：

-- 5 个学生 × 3 个院系 = 15 行
SELECT s.name, d.dept_name
FROM students s
CROSS JOIN departments d;

这种结果在业务上几乎没用，但在两种场景下常见：

生成"所有组合"的测试数据；
配合 WHERE 过滤（其实等价于 INNER JOIN）。

警告：大表 CROSS JOIN 会爆内存——10 万 × 10 万 = 100 亿行。生产环境严禁无意识写 CROSS JOIN。

7 ON vs WHERE 的区别

这是 JOIN 最重要的一个考点。

7.1 INNER JOIN 里 ON 和 WHERE 等价

-- 写法 A：条件写在 ON 里
SELECT s.name, d.dept_name
FROM students s
INNER JOIN departments d ON s.dept_id = d.dept_id AND d.dept_name = '计算机系';

-- 写法 B：条件写在 WHERE 里（等价）
SELECT s.name, d.dept_name
FROM students s
INNER JOIN departments d ON s.dept_id = d.dept_id
WHERE d.dept_name = '计算机系';

INNER JOIN 里两种写法结果一样，因为 JOIN 完之后都是对结果集过滤。

7.2 LEFT JOIN 里 ON 和 WHERE 不等价

-- 写法 A：过滤条件写在 ON 里
-- 意思：LEFT JOIN 时，只拿"计算机系"去匹配学生，其他院系不参与匹配
SELECT s.name, d.dept_name
FROM students s
LEFT JOIN departments d ON s.dept_id = d.dept_id AND d.dept_name = '计算机系';

结果：

| name | dept_name |

|---|---|

| 张三 | 计算机系 |

| 李四 | 计算机系 |

| 王五 | NULL |

| 赵六 | NULL |

| 钱七 | NULL |

王五虽然属于中文系，但因为 LEFT JOIN 是"全保留左表"，他还是出现了，只是 dept_name 是 NULL（因为 ON 条件过滤掉了中文系的匹配）。

-- 写法 B：过滤条件写在 WHERE 里
-- 意思：先 LEFT JOIN 完，再过滤只保留"计算机系"的行
SELECT s.name, d.dept_name
FROM students s
LEFT JOIN departments d ON s.dept_id = d.dept_id
WHERE d.dept_name = '计算机系';

结果：

| name | dept_name |

|---|---|

| 张三 | 计算机系 |

| 李四 | 计算机系 |

王五、赵六、钱七都被 WHERE 过滤掉了——LEFT JOIN 加 WHERE 右表条件，等价于 INNER JOIN。这是最经典的坑。

记忆口诀：

想"先过滤右表再匹配"，写 ON；
想"匹配完再过滤结果"，写 WHERE；
业务要"左表全保留"就别在 WHERE 里写右表的非空条件。

8 自连接

8.1 什么是自连接

一张表自己 JOIN 自己，叫自连接。常见于"树形结构"——比如员工表里有 manager_id 指向同表的员工：

-- 假设 employees 表：id, name, manager_id
-- 查每个员工及其直属上级的名字
SELECT
    e1.name       AS employee,
    e2.name       AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

e1 是"员工视角"，e2 是"上级视角"，同一张表起两个别名。

8.2 应用场景

组织架构（员工-上级）；
评论楼中楼（comment 表自引用 parent_id）；
商品同类推荐（商品表里 self_id 和 similar_id）。

9 多表连接

业务后台经常要连 3~5 张表：

-- 查学生姓名、课程名、成绩
SELECT
    s.name        AS student_name,
    c.course_name AS course_name,
    sc.score
FROM students s
INNER JOIN scores sc   ON s.id = sc.student_id
INNER JOIN courses c   ON c.id = sc.course_id
ORDER BY s.id;

多表 JOIN 的注意事项：

1. 别连超过 5~6 张表，性能会急降；

2. 每张表的连接字段都要有索引；

3. 用 LEFT JOIN 时想清楚"谁该全保留"。

10 JOIN 速查表

| JOIN 类型 | 结果 | 典型场景 |

|---|---|---|

| INNER JOIN | 两表交集 | 普通列表查询 |

| LEFT JOIN | 左表全 + 右表匹配 | 保留主表所有行 |

| RIGHT JOIN | 右表全 + 左表匹配 | 同 LEFT，少用 |

| CROSS JOIN | 笛卡尔积 | 生成组合，慎用 |

| LEFT JOIN + WHERE 右表 IS NULL | 左表孤儿 | 数据完整性检查 |

| 自连接 | 同表两别名 | 树形/层级数据 |

11 常见坑与注意事项

坑 1：LEFT JOIN 后在 WHERE 里写右表条件，结果变 INNER。

这是面试最高频的坑。业务要"左表全保留"时，右表的过滤条件写 ON，别写 WHERE。

坑 2：多表 JOIN 忘写连接条件。

写成笛卡尔积，结果行数爆炸，数据库直接卡住。

坑 3：SELECT * 多表 JOIN。

两张表都有 id 列时，SELECT * 输出两个 id，应用层解析崩溃。

坑 4：LEFT JOIN 后 COUNT(*) 数错行数。

应该用 COUNT(右表.主键) 而不是 COUNT(*)。

坑 5：连接字段类型不一致。

比如左表 dept_id 是 INT，右表 dept_id 是 VARCHAR，会隐式转换导致索引失效。

坑 6：大表 JOIN 没索引。

连接字段没有索引就是 Nested Loop 全扫，性能差几个数量级。

坑 7：把 RIGHT JOIN 当 LEFT JOIN 用。

可读性差，统一用 LEFT JOIN 把"要全保留的表"放左边。

坑 8：JOIN 三张以上表性能骤降。

MySQL 优化器对多表 JOIN 的优化有限，超过 5 张表考虑拆查询。

12 练习题

练习 1：基础 INNER JOIN

题目：查每个学生的姓名和院系名，只显示有院系的学生。

SELECT s.name, d.dept_name
FROM students s
INNER JOIN departments d ON s.dept_id = d.dept_id;

</details>

练习 2：LEFT JOIN 保留所有学生

题目：查所有学生（包括没院系的），显示姓名和院系名（没有显示 NULL）。

SELECT s.name, d.dept_name
FROM students s
LEFT JOIN departments d ON s.dept_id = d.dept_id;

</details>

练习 3：找孤儿学生

题目：找出没有对应院系的学生（dept_id 在 departments 里不存在或为 NULL）。

SELECT s.id, s.name
FROM students s
LEFT JOIN departments d ON s.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

</details>

练习 4：ON vs WHERE

题目：查所有学生，但只显示计算机系的匹配（其他学生仍然要出现，只是院系名是 NULL）。

SELECT s.name, d.dept_name
FROM students s
LEFT JOIN departments d
  ON s.dept_id = d.dept_id
 AND d.dept_name = '计算机系';
-- 注意：条件写在 ON 里，不是 WHERE

</details>

练习 5：统计每个院系的人数

题目：统计每个院系（包括没有学生的院系）的学生人数，没人的院系显示 0。

SELECT d.dept_name, COUNT(s.id) AS student_count
FROM departments d
LEFT JOIN students s ON d.dept_id = s.dept_id
GROUP BY d.dept_id, d.dept_name;

</details>

练习 6：自连接

题目：假设有一张 employees(id, name, manager_id)，写 SQL 查每个员工及其上级姓名。

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

</details>

下一篇预告：第 8 章子查询与派生表

— 全文完 —回到顶部 ↑

下载推广海报

DISCUSSION

文章回复

0 条公开回复

还没有回复，欢迎参与讨论。

第 7 章多表连接JOIN

目录

1 为什么需要 JOIN

2 示例表准备

3 INNER JOIN 内连接

3.1 基本语法

3.2 表别名

3.3 多个连接条件

4 LEFT JOIN 左连接

4.1 基本语法

4.2 找"孤儿数据"

4.3 LEFT JOIN 与聚合

5 RIGHT JOIN 右连接

6 CROSS JOIN 笛卡尔积

7 ON vs WHERE 的区别

7.1 INNER JOIN 里 ON 和 WHERE 等价

7.2 LEFT JOIN 里 ON 和 WHERE 不等价

8 自连接

8.1 什么是自连接

8.2 应用场景

9 多表连接

10 JOIN 速查表

11 常见坑与注意事项

12 练习题

练习 1：基础 INNER JOIN

练习 2：LEFT JOIN 保留所有学生

练习 3：找孤儿学生

练习 4：ON vs WHERE

练习 5：统计每个院系的人数

练习 6：自连接

文章推广海报

文章回复

第 7 章 多表连接JOIN

目录

1 为什么需要 JOIN

2 示例表准备

3 INNER JOIN 内连接

3.1 基本语法

3.2 表别名

3.3 多个连接条件

4 LEFT JOIN 左连接

4.1 基本语法

4.2 找"孤儿数据"

4.3 LEFT JOIN 与聚合

5 RIGHT JOIN 右连接

6 CROSS JOIN 笛卡尔积

7 ON vs WHERE 的区别

7.1 INNER JOIN 里 ON 和 WHERE 等价

7.2 LEFT JOIN 里 ON 和 WHERE 不等价

8 自连接

8.1 什么是自连接

8.2 应用场景

9 多表连接

10 JOIN 速查表

11 常见坑与注意事项

12 练习题

练习 1：基础 INNER JOIN

练习 2：LEFT JOIN 保留所有学生

练习 3：找孤儿学生

练习 4：ON vs WHERE

练习 5：统计每个院系的人数

练习 6：自连接

第 7 章多表连接JOIN