SQL JOIN 多表联查

学习 INNER JOIN 连接多张表 · 难度：进阶 · +15XP

SQL JOIN 多表联查 — 打通数据孤岛

一、什么是 JOIN？为什么它是关系型数据库的精髓？

JOIN（连接）是 SQL 中最强大的功能之一，用于根据关联条件将多张表的数据合并成一张结果集。在关系型数据库设计中，数据通常分散在多个表中（这叫规范化 Normalization），以避免数据冗余。例如：用户信息存在 users 表，订单信息存在 orders 表，需要通过用户 ID 将两张表关联起来才能看到"谁买了什么"。

为什么 JOIN 如此重要？在实际业务中，很少有查询只涉及一张表。用户的订单历史、商品的分类信息、文章的作者资料……几乎所有有意义的查询都需要 JOIN。掌握 JOIN 是 SQL 能力的分水岭：不会 JOIN 只能做单表的 CRUD，会 JOIN 才能真正挖掘数据的价值。

JOIN 的本质是把两张（或更多）表按照指定的条件进行"横向拼接"。最常见的条件是两个表中相等的字段（如 users.id = orders.user_id），但也可以用不等条件、范围条件等进行连接。

二、六种 JOIN 类型一图看懂

INNER JOIN: LEFT JOIN: RIGHT JOIN: ┌───┬───┐ ┌───┬───┐ ┌───┬───┐ ┌───┬───┐ ┌───┬───┐ ┌───┬───┐ │ A │ B │ │ B │ C │ │ A │ B │ │ B │ C │ │ A │ B │ │ B │ C │ ├───┼───┤ ├───┼───┤ ├───┼───┤ ├───┼───┤ ├───┼───┤ ├───┼───┤ │ 1 │ a │ │ a │ x │ │ 1 │ a │ │ a │ x │ │ 1 │ a │ │ a │ x │ │ 2 │ b │ │ b │ y │ │ 2 │ b │ │ b │ y │ │ 2 │ b │ │ b │ y │ │ 3 │ c │ │ d │ z │ │ 3 │ c │ │ d │ z │ │ 3 │ c │ │ d │ z │ └───┴───┘ └───┴───┘ └───┴───┘ └───┴───┘ └───┴───┘ └───┴───┘ 结果: 1-a-x 结果: 1-a-x 结果: 1-a-x 2-b-y 2-b-y 2-b-y 3-c-NULL NULL-d-z （只有匹配的）（左表全部保留）（右表全部保留）

FULL OUTER JOIN: CROSS JOIN: SELF JOIN: 结果: 1-a-x 结果: 1-a-x 同一张表自己连自己 2-b-y 1-a-z （用不同别名区分） 3-c-NULL 2-b-x NULL-d-z 2-b-y 3-c-x （两表全部保留） 3-c-z （笛卡尔积）

三、七种 JOIN 对比速查表

JOIN 类型	保留哪些行	不匹配时	典型场景
INNER JOIN	两表都匹配	丢弃不匹配的行	查询有订单的用户
LEFT JOIN	左表全部 + 右表匹配	右表字段为 NULL	所有用户及其订单（含无订单用户）
RIGHT JOIN	右表全部 + 左表匹配	左表字段为 NULL	所有订单及其用户（含无主订单）
FULL OUTER JOIN	两表全部	不匹配侧为 NULL	两表数据完整对比（MySQL 不直接支持）
CROSS JOIN	笛卡尔积（每行 × 每行）	无匹配概念	生成组合（如颜色 × 尺码）
SELF JOIN	表自己连自己	同 INNER/LEFT	员工-经理关系、树形结构
NATURAL JOIN	自动按同名列匹配	—	不推荐（隐式行为，易出错）

四、详细代码示例（逐行注释）

-- ======== 准备示例数据 ========
-- users 表：用户
-- | id | name    |
-- | 1  | 小明    |
-- | 2  | 小红    |
-- | 3  | 小刚    |
--
-- orders 表：订单
-- | id | user_id | product  | amount |
-- | 1  | 1       | 手机     | 3999   |
-- | 2  | 1       | 电脑     | 6999   |
-- | 3  | 2       | 耳机     | 299    |
-- | 4  | 5       | 键盘     | 499    |（user_id=5 不存在于 users 表）
-- ======== 1. INNER JOIN（内连接）— 仅返回两表匹配的行 ========
-- 查询每个订单属于哪个用户（丢弃没有订单的用户和没有用户的订单）
SELECT u.name AS 用户名, o.product AS 商品, o.amount AS 金额
FROM users AS u                             -- u 是 users 的别名
INNER JOIN orders AS o                      -- o 是 orders 的别名
    ON u.id = o.user_id;                    -- 连接条件
-- 结果：小明-手机, 小明-电脑, 小红-耳机
-- 小刚没有订单 → 不出现，键盘订单无对应用户 → 不出现
-- 写法等价（隐式内连接，不推荐）
SELECT u.name, o.product
FROM users u, orders o
WHERE u.id = o.user_id;
-- ======== 2. LEFT JOIN（左连接）— 保留左表全部行 ========
-- 查询所有用户及其订单（没有订单的用户也要显示）
SELECT u.name AS 用户名, o.product AS 商品, o.amount AS 金额
FROM users AS u
LEFT JOIN orders AS o
    ON u.id = o.user_id;
-- 结果：小明-手机, 小明-电脑, 小红-耳机, 小刚-NULL
-- 小刚没有订单 → 保留，订单字段为 NULL
-- 常用：找出没有订单的用户
SELECT u.name AS 用户名
FROM users AS u
LEFT JOIN orders AS o
    ON u.id = o.user_id
WHERE o.id IS NULL;                         -- 订单表主键为 NULL = 没有订单
-- 结果：小刚
-- ======== 3. RIGHT JOIN（右连接）— 保留右表全部行 ========
-- 查询所有订单及其用户（包括孤立的订单）
SELECT u.name AS 用户名, o.product AS 商品
FROM users AS u
RIGHT JOIN orders AS o
    ON u.id = o.user_id;
-- 结果：小明-手机, 小明-电脑, 小红-耳机, NULL-键盘（键盘订单无对应用户）
-- 实际中可以用 LEFT JOIN 代替（交换表顺序即可），RIGHT JOIN 不常用
-- ======== 4. FULL OUTER JOIN（全外连接）— 保留两表全部行 ========
-- MySQL 不直接支持 FULL OUTER JOIN，用 UNION 模拟
SELECT u.name AS 用户名, o.product AS 商品
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
UNION
SELECT u.name AS 用户名, o.product AS 商品
FROM users AS u
RIGHT JOIN orders AS o ON u.id = o.user_id;
-- 结果：小明-手机, 小明-电脑, 小红-耳机, 小刚-NULL, NULL-键盘
-- ======== 5. CROSS JOIN（交叉连接）— 笛卡尔积 ========
-- 生成所有可能的组合
SELECT u.name AS 用户, o.product AS 商品
FROM users AS u
CROSS JOIN orders AS o;
-- 3 个用户 × 4 个订单 = 12 行结果（每种组合都出现）
-- 实用场景：生成 SKU 组合
-- 如：颜色表有 3 种颜色，尺码表有 4 种尺码，CROSS JOIN 生成 12 个 SKU
-- ======== 6. SELF JOIN（自连接）— 表自己连接自己 ========
-- 示例：员工表 employees (id, name, manager_id)
-- 查询每个员工及其直属经理
SELECT e.name AS 员工, m.name AS 经理
FROM employees AS e                          -- e 代表员工
LEFT JOIN employees AS m                     -- m 代表经理（同一张表的另一个别名）
    ON e.manager_id = m.id;                  -- 员工的经理 ID = 经理的员工 ID
-- ======== 7. 多表 JOIN（3 张表以上） ========
-- 查询用户的订单详情
SELECT
    u.name AS 用户名,
    o.product AS 商品,
    o.amount AS 金额,
    c.name AS 商品分类
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
LEFT JOIN categories AS c ON o.category_id = c.id  -- 继续连接第三张表
ORDER BY o.amount DESC;
-- ======== 8. JOIN + 聚合函数 ========
-- 查询每个用户的订单总额
SELECT
    u.name AS 用户名,
    COUNT(o.id) AS 订单数,                    -- 统计订单数量
    COALESCE(SUM(o.amount), 0) AS 总消费       -- 总额（NULL 转为 0）
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
GROUP BY u.id, u.name;                      -- 按用户分组
-- ======== 9. 复合条件的 JOIN ========
-- ON 条件不只是等值，可以包含多个条件和不等条件
SELECT u.name, o.product
FROM users AS u
JOIN orders AS o
    ON u.id = o.user_id
    AND o.amount > 500;                     -- 只要金额大于 500 的订单
-- 等价于：JOIN + WHERE o.amount > 500
-- ======== 10. JOIN 查询优化建议 ========
-- 说明：解释 EXPLAIN 输出，关注 type、key、rows 字段
-- EXPLAIN SELECT ...
-- type 从好到差：system > const > eq_ref > ref > range > index > ALL
-- 确保 JOIN 的 ON 条件列都有索引！
-- 为 orders.user_id 创建索引可以大幅提升 JOIN 性能

五、INNER JOIN vs LEFT JOIN — 核心区别

场景	INNER JOIN	LEFT JOIN
查询有订单的用户	INNER JOIN（天然过滤）	LEFT JOIN + WHERE o.id IS NOT NULL
查询所有用户（含无订单）	做不到	LEFT JOIN 直接实现
统计用户订单数	只统计有订单的用户	统计全部用户（含 0 单）
性能	通常更快（数据量更小）	可能更慢（数据量大）
使用频率	高（约 60% 的场景）	高（约 35% 的场景）

六、JOIN 常见错误与注意事项

错误	后果	正确做法
ON 条件遗漏	笛卡尔积，返回大量错误数据	总是显式写 ON 条件
连接列无索引	大表 JOIN 极慢	对 JOIN 的关联列建索引
多对多关联	数据膨胀（行数异常增多）	检查关联条件是否唯一
WHERE 过滤与 ON 混淆	LEFT JOIN 后 WHERE 右表条件会将左连接变成内连接	右表的过滤条件写在 ON 里
SELECT * 且列名冲突	同名列被覆盖或混淆	明确列出需要的列并加别名

七、实践任务

创建三张表：students（id, name, class_id）、classes（id, class_name）、scores（id, student_id, subject, score）。每张表插入至少 5 条数据
使用 INNER JOIN 查询每个学生的班级名称
使用 LEFT JOIN 查询所有学生及其成绩（包括没有成绩的学生）
使用三表 JOIN 查询每个学生的班级名称和各科成绩
统计每个班级的学生人数和平均成绩（使用 JOIN + GROUP BY）
使用 SELF JOIN：在员工表中查询每个员工的直属上级

SQL JOIN 多表联查