MySQL 多表连接 (JOIN)

JOIN 是 SQL 中最核心的操作之一，用于将多个表的数据按特定条件组合在一起。理解 JOIN 的工作原理对于编写高效的数据库查询至关重要。本章将深入讲解 MySQL 中各种 JOIN 类型、执行原理以及优化技巧。

为什么需要 JOIN？

关系型数据库的设计原则

关系型数据库的核心思想是将数据分散到多个相关的表中，通过外键建立关联。这种设计带来的好处：

• 减少数据冗余：相同数据只存储一次
• 保证数据一致性：修改一处即可影响所有关联数据
• 提高存储效率：避免重复存储大量相同信息
• 便于维护：表结构清晰，职责单一

然而，查询时往往需要将分散的数据重新组合，这就是 JOIN 的作用。

实际场景示例

假设我们有一个电商系统，数据分散在多个表中：

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

-- 订单表
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    product_name VARCHAR(100),
    amount DECIMAL(10, 2),
    created_at DATETIME
);

-- 订单详情表
CREATE TABLE order_items (
    id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10, 2)
);

要查询"用户张三的所有订单及其商品明细"，需要将三个表的数据组合起来，这正是 JOIN 的用武之地。

演示数据

为了便于演示，我们先定义示例数据：

users 表

id	name	email	age
1	张三	[email protected]	25
2	李四	[email protected]	30
3	王五	[email protected]	28

orders 表

id	user_id	product	amount
1	1	Phone	5000
2	1	Laptop	8000
3	2	Tablet	3000
4	4	Keyboard	200

departments 表

id	name
1	技术部
2	销售部

employees 表

id	name	department_id	manager_id
1	CEO	NULL	NULL
2	CTO	1	1
3	Engineer	1	2
4	Sales	2	1

JOIN 类型详解

1. INNER JOIN（内连接）

INNER JOIN 返回两个表中都存在匹配的记录。这是最常用的连接类型。

-- 基本语法
SELECT 列名
FROM 表1
INNER JOIN 表2 ON 连接条件;

-- INNER 可以省略
SELECT *
FROM users
JOIN orders ON users.id = orders.user_id;

执行结果：

id	name	email	age	order_id	user_id	product	amount
1	张三	[email protected]	25	1	1	Phone	5000
1	张三	[email protected]	25	2	1	Laptop	8000
2	李四	[email protected]	30	3	2	Tablet	3000

结果分析：

• 张三有 2 个订单，所以出现 2 次
• 李四有 1 个订单，出现 1 次
• 王五（id=3）没有订单，不出现
• user_id=4 的订单没有对应用户，不出现

使用场景：

• 查询有关联数据的记录
• 数据验证（确保引用完整性）
• 统计有订单的用户

2. LEFT JOIN（左连接）

LEFT JOIN 返回左表的所有记录，右表没有匹配的显示 NULL。

SELECT 
    users.id,
    users.name,
    orders.product,
    orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

执行结果：

id	name	product	amount
1	张三	Phone	5000
1	张三	Laptop	8000
2	李四	Tablet	3000
3	王五	NULL	NULL

结果分析：

• 王五没有订单，但仍然出现在结果中
• 未匹配的列（product、amount）显示为 NULL

使用场景：

• 查找"没有关联数据"的记录（如没有订单的用户）
• 生成完整的报表，包含所有主数据
• 统计分析，需要考虑所有记录

常见技巧：查找没有关联的记录

-- 查找没有订单的用户
SELECT u.id, u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

这个模式非常常见：使用 LEFT JOIN 配合 WHERE 右表主键 IS NULL 来找出左表中没有关联记录的行。

3. RIGHT JOIN（右连接）

RIGHT JOIN 返回右表的所有记录，左表没有匹配的显示 NULL。

SELECT 
    users.id,
    users.name,
    orders.product,
    orders.amount
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

执行结果：

id	name	product	amount
1	张三	Phone	5000
1	张三	Laptop	8000
2	李四	Tablet	3000
NULL	NULL	Keyboard	200

结果分析：

• user_id=4 的订单没有对应用户，左表列显示 NULL

使用场景：

• RIGHT JOIN 可以通过交换表的位置用 LEFT JOIN 实现
• 实际开发中 RIGHT JOIN 使用较少，通常统一使用 LEFT JOIN 以保持代码一致性

4. FULL OUTER JOIN（完全外连接）

FULL OUTER JOIN 返回两个表的所有记录，没有匹配的显示 NULL。

-- MySQL 不直接支持 FULL OUTER JOIN
-- 使用 LEFT JOIN 和 RIGHT JOIN 的 UNION 模拟

SELECT users.id, users.name, orders.product, orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id

UNION

SELECT users.id, users.name, orders.product, orders.amount
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

执行结果：

id	name	product	amount
1	张三	Phone	5000
1	张三	Laptop	8000
2	李四	Tablet	3000
3	王五	NULL	NULL
NULL	NULL	Keyboard	200

使用场景：

• 数据对比分析
• 找出两个表中不匹配的记录
• 数据迁移和同步

使用 UNION ALL 优化：

如果确定两个表的主键不重叠，可以使用 UNION ALL 提高性能（避免去重排序）：

SELECT u.id, u.name, o.product, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id

UNION ALL

SELECT u.id, u.name, o.product, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id
WHERE u.id IS NULL;  -- 排除已在 LEFT JOIN 中出现的行

5. CROSS JOIN（交叉连接）

CROSS JOIN 返回两个表的笛卡尔积，即左表的每一行与右表的每一行组合。

SELECT users.name, orders.product
FROM users
CROSS JOIN orders;

如果 users 有 3 行，orders 有 4 行，结果将有 3 × 4 = 12 行。

使用场景：

• 生成测试数据
• 创建所有可能的组合（如所有用户与所有商品的推荐列表）
• 计算排列组合

注意事项：

• CROSS JOIN 不需要 ON 条件
• 数据量大时会产生巨大的结果集，需谨慎使用

6. SELF JOIN（自连接）

SELF JOIN 不是一种特殊的 JOIN 类型，而是一种使用技巧：表与自身进行连接。

-- 查询员工及其上级
SELECT 
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

执行结果：

employee	manager
CEO	NULL
CTO	CEO
Engineer	CTO
Sales	CEO

使用场景：

• 处理层级关系（组织架构、评论回复）
• 比较同一表中不同行的数据
• 查找重复记录

示例：查找同部门的员工对

SELECT e1.name, e2.name AS colleague
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id
WHERE e1.id < e2.id;  -- 避免重复配对（A-B 和 B-A）

7. NATURAL JOIN（自然连接）

NATURAL JOIN 自动基于两个表中同名列进行连接：

-- 自动使用 id 和 name 等同名列连接
SELECT * FROM users
NATURAL JOIN user_profiles;

不推荐使用，原因：

• 连接条件不明确，难以理解
• 如果表结构变化（新增同名列），会导致意外的连接行为
• 代码可读性差，维护困难

8. JOIN ... USING

当两个表有相同列名时，可以使用 USING 简化语法：

-- 使用 USING
SELECT *
FROM users
JOIN orders USING (user_id);

-- 等价于
SELECT *
FROM users
JOIN orders ON users.user_id = orders.user_id;

USING 的特点：

• 结果集中连接列只会出现一次
• 语法更简洁
• 要求两个表中的连接列必须同名

多表连接

实际应用中，经常需要连接三个或更多的表。

基本多表连接

SELECT 
    users.name AS customer,
    orders.product,
    products.category
FROM users
JOIN orders ON users.id = orders.user_id
JOIN products ON orders.product_id = products.id;

连接顺序的影响

MySQL 从左到右依次处理每个 JOIN。合理安排表的顺序可以影响性能：

-- 将过滤效果好的表放在前面
SELECT *
FROM small_table s
JOIN large_table l ON s.id = l.small_id
JOIN another_table a ON l.id = a.large_id;

-- 过滤条件尽早应用
SELECT *
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
WHERE o.status = 'completed'  -- 先过滤订单
  AND u.country = 'China';

多表连接的最佳实践

1. 控制连接表的数量：建议不超过 5 个表
2. 使用有意义的别名：提高代码可读性
3. 为每个连接创建索引：确保连接列有索引
4. 逐步构建复杂查询：先写简单版本，验证后再扩展

JOIN 执行原理深入理解

理解 MySQL 如何执行 JOIN 对于编写高效查询至关重要。

Nested-Loop Join（嵌套循环连接）

这是 MySQL 最基本的 JOIN 算法：

for each row in 驱动表:
    for each row in 被驱动表:
        if 满足连接条件:
            输出组合行

SELECT * FROM t1 JOIN t2 ON t1.id = t2.t1_id;

执行过程：

1. 从 t1 表（驱动表）读取第一行
2. 扫描 t2 表，找到所有匹配的行
3. 输出匹配的组合
4. 重复以上步骤，直到处理完 t1 的所有行

性能分析：

• 时间复杂度：O(N × M)，N 和 M 分别是两个表的行数
• 当被驱动表的连接列有索引时，内层循环可以使用索引查找，大大提高效率

Simple Nested-Loop Join（简单嵌套循环）

最原始的实现方式：

Table A (驱动表): 100 行
Table B (被驱动表): 1000 行

扫描次数：
- 扫描 A 表：1 次
- 扫描 B 表：100 次（对 A 的每一行都扫描一次 B）
- 总扫描：1 + 100 × 1000 = 100,001 行

Block Nested-Loop Join（块嵌套循环连接）

当连接条件没有索引时，MySQL 使用 Block Nested-Loop Join 优化：

┌─────────────────────────────────────────────────────────────┐
│                  Block Nested-Loop Join                     │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  1. 将驱动表的数据读入 Join Buffer                          │
│                                                             │
│  ┌─────────────────────────────┐                            │
│  │      Join Buffer            │                            │
│  │  ┌─────┬─────┬─────┬─────┐  │                            │
│  │  │ R1  │ R2  │ R3  │ ... │  │                            │
│  │  └─────┴─────┴─────┴─────┘  │                            │
│  └─────────────────────────────┘                            │
│              │                                              │
│              ▼                                              │
│  2. 扫描被驱动表，对每一行检查是否与 Buffer 中的数据匹配    │
│                                                             │
│  ┌─────────────────────────────────────────────┐            │
│  │  被驱动表扫描（只需一次完整扫描）            │            │
│  │  S1 → 检查与 Buffer 中所有行                │            │
│  │  S2 → 检查与 Buffer 中所有行                │            │
│  │  ...                                        │            │
│  └─────────────────────────────────────────────┘            │
│                                                             │
└─────────────────────────────────────────────────────────────┘

优势：

• 减少对被驱动表的扫描次数
• Join Buffer 越大，批量匹配的行数越多，效率越高

相关参数：

-- 查看 Join Buffer 大小
SHOW VARIABLES LIKE 'join_buffer_size';  -- 默认 256KB

-- 调整 Join Buffer 大小
SET join_buffer_size = 4194304;  -- 4MB

EXPLAIN 中的体现：

EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.col = t2.col;

-- 如果 Extra 列出现 "Using join buffer (Block Nested Loop)"
-- 说明使用了 Block Nested-Loop Join

Index Nested-Loop Join（索引嵌套循环连接）

当被驱动表的连接列有索引时，MySQL 使用索引查找代替全表扫描：

for each row in 驱动表:
    使用索引在被驱动表中查找匹配行  -- O(log N) 而不是 O(N)
    输出匹配的组合

性能提升：

• 时间复杂度从 O(N × M) 降低到 O(N × log M)
• 这就是为什么连接列必须有索引的原因

Hash Join（MySQL 8.0.18+）

MySQL 8.0.18 引入了 Hash Join，对于没有索引的大表连接性能更好：

┌─────────────────────────────────────────────────────────────┐
│                     Hash Join 工作原理                      │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  阶段一：构建阶段 (Build Phase)                             │
│  ┌─────────────────────────────────────────────┐            │
│  │  较小表的连接列 → 哈希函数 → 哈希表          │            │
│  │                                             │            │
│  │  ┌──────────────────────────────────────┐   │            │
│  │  │ Hash Bucket 0: [row1, row5, ...]     │   │            │
│  │  │ Hash Bucket 1: [row2, row7, ...]     │   │            │
│  │  │ Hash Bucket 2: [row3, ...]           │   │            │
│  │  │ ...                                  │   │            │
│  │  └──────────────────────────────────────┘   │            │
│  └─────────────────────────────────────────────┘            │
│                                                             │
│  阶段二：探测阶段 (Probe Phase)                             │
│  ┌─────────────────────────────────────────────┐            │
│  │  较大表的每一行：                            │            │
│  │  1. 计算连接列的哈希值                       │            │
│  │  2. 到对应的哈希桶中查找匹配                 │            │
│  │  3. 输出匹配的组合                           │            │
│  └─────────────────────────────────────────────┘            │
│                                                             │
└─────────────────────────────────────────────────────────────┘

优势：

• 对于大表无索引连接，性能优于 Block Nested-Loop
• 时间复杂度接近 O(N + M)
• 特别适合等值连接

适用条件：

• MySQL 8.0.18 及以上版本
• 等值连接条件
• 连接列没有索引或索引效率低

示例：

-- MySQL 8.0.18+ 会自动选择 Hash Join
EXPLAIN FORMAT=TREE
SELECT * FROM large_table1 t1
JOIN large_table2 t2 ON t1.col = t2.col;

-- 输出可能包含：
-- -> Inner hash join (t1.col = t2.col)

Join Buffer 优化建议

-- 查看当前配置
SHOW VARIABLES LIKE 'join_buffer_size';

-- 一般建议设置为 1-4MB
-- 太大会占用过多内存，太小影响性能
SET GLOBAL join_buffer_size = 4194304;  -- 4MB

-- 注意：每个连接都会分配 Join Buffer
-- 总内存使用 = join_buffer_size × 连接数

Semi-Join 和 Anti-Join

Semi-Join（半连接）

Semi-Join 用于检查是否存在匹配，但不返回右表的数据：

-- 使用 IN 实现 Semi-Join
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

-- 使用 EXISTS 实现 Semi-Join
SELECT u.* FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

特点：

• 只返回左表的数据
• 对于左表的每一行，只要右表有匹配就停止
• 不会因为右表多行匹配而复制左表行

MySQL 的 Semi-Join 优化：

MySQL 优化器会自动将某些 IN 和 EXISTS 子查询转换为 Semi-Join：

-- 原始查询
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- MySQL 可能优化为：
-- 使用 Semi-Join，避免重复返回用户记录

查看 Semi-Join 状态：

EXPLAIN SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

-- Extra 列可能显示：
-- "Using where; FirstMatch(users)"  -- FirstMatch 策略
-- "Using where; Materialize"         -- 物化策略

Anti-Join（反连接）

Anti-Join 用于查找"不存在匹配"的记录：

-- 使用 NOT IN 实现 Anti-Join
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);

-- 使用 NOT EXISTS 实现 Anti-Join（推荐）
SELECT u.* FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 使用 LEFT JOIN 实现 Anti-Join
SELECT u.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

NOT IN vs NOT EXISTS 的 NULL 陷阱：

-- 危险：如果子查询返回 NULL，NOT IN 结果为 UNKNOWN
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders);
-- 如果 orders.user_id 有 NULL，结果可能不符合预期

-- 安全：NOT EXISTS 不受 NULL 影响
SELECT u.* FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

JOIN 性能优化

1. 为连接列创建索引

这是最重要的优化手段：

-- 在外键列上创建索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 复合索引（考虑查询条件）
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

索引设计原则：

• 连接列必须有索引
• 复合索引中，连接列和过滤列的顺序要合理
• 考虑索引的选择性

2. 选择合适的驱动表

驱动表是 JOIN 操作的起点，选择合适的驱动表至关重要：

-- 原则：小表驱动大表

-- 好的做法：小表在前
SELECT * FROM small_table s
JOIN large_table l ON s.id = l.small_id;

-- 对于 LEFT JOIN，左表是驱动表
SELECT * FROM small_table s
LEFT JOIN large_table l ON s.id = l.small_id;

使用 STRAIGHT_JOIN 强制指定驱动表：

-- 强制 MySQL 按照写的顺序执行 JOIN
SELECT STRAIGHT_JOIN *
FROM large_table l
JOIN small_table s ON l.small_id = s.id;

3. 使用 EXPLAIN 分析执行计划

EXPLAIN SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id;

关注要点：

字段	含义	关注点
type	访问类型	应避免 ALL（全表扫描）
key	使用的索引	确保使用了正确的索引
rows	预估行数	评估查询效率
Extra	额外信息	关注 "Using join buffer"

type 类型（从好到差）：

类型	说明
system	单行系统表
const	主键/唯一索引常量查询
eq_ref	主键/唯一索引关联
ref	非唯一索引关联
range	索引范围扫描
index	全索引扫描
ALL	全表扫描（需要优化）

4. 减少返回的列

-- 不好：SELECT * 返回所有列
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id;

-- 好：只返回需要的列
SELECT u.name, o.product, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

5. 使用覆盖索引

-- 创建覆盖索引
CREATE INDEX idx_orders_user_product_amount ON orders(user_id, product, amount);

-- 查询可以完全使用索引，不需要回表
SELECT o.product, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id = 1;

6. 分解复杂 JOIN

对于非常复杂的查询，考虑分解：

-- 复杂的单条 JOIN
SELECT * FROM t1
JOIN t2 ON ...
JOIN t3 ON ...
JOIN t4 ON ...
JOIN t5 ON ...
WHERE ...;

-- 分解为多个查询（在应用层组合）
-- 第一步：获取核心数据
SELECT id FROM t1 WHERE ...;

-- 第二步：基于核心数据获取关联数据
SELECT * FROM t2 WHERE t1_id IN (...);

-- 第三步：进一步获取其他数据
SELECT * FROM t3 WHERE t2_id IN (...);

7. 使用临时表或 CTE

-- 使用 CTE 简化复杂查询
WITH active_users AS (
    SELECT * FROM users WHERE status = 'active'
),
user_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    WHERE created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)
    GROUP BY user_id
)
SELECT u.name, COALESCE(o.order_count, 0) AS recent_orders
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id;

常见错误与陷阱

1. 忘记 ON 条件

-- 错误：产生笛卡尔积
SELECT * FROM users, orders;

-- 正确
SELECT * FROM users
JOIN orders ON users.id = orders.user_id;

-- 错误：CROSS JOIN 不需要 ON，但可能导致意外结果
SELECT * FROM users
CROSS JOIN orders;

2. 笛卡尔积

-- 危险：两个大表的笛卡尔积
SELECT COUNT(*) FROM large_table1
CROSS JOIN large_table2;
-- 如果两个表各有 100 万行，结果将有 1 万亿行！

3. 列名歧义

-- 错误：id 列在两个表中都存在
SELECT id FROM users
JOIN orders ON users.id = orders.user_id;
-- ERROR: Column 'id' in field list is ambiguous

-- 正确：使用表名或别名限定
SELECT users.id, orders.id AS order_id
FROM users
JOIN orders ON users.id = orders.user_id;

4. NULL 值处理不当

-- LEFT JOIN 产生的 NULL 需要特别处理
SELECT 
    u.name,
    COALESCE(SUM(o.amount), 0) AS total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- 如果不处理 NULL，聚合结果可能错误

5. 过滤条件位置错误

-- 错误：WHERE 条件放在 LEFT JOIN 之后会改变 JOIN 语义
SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';  -- 这会排除没有订单的用户

-- 正确：如果需要保留所有用户
SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed';

6. 过度 JOIN

-- 不推荐：JOIN 太多表
SELECT * FROM t1
JOIN t2 ON ...
JOIN t3 ON ...
JOIN t4 ON ...
JOIN t5 ON ...
JOIN t6 ON ...
JOIN t7 ON ...;

-- 建议：拆分为多个查询

实战案例

案例1：电商订单统计

-- 查询每个用户的订单统计
SELECT 
    u.id,
    u.name,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.amount), 0) AS total_amount,
    COALESCE(AVG(o.amount), 0) AS avg_amount,
    MAX(o.created_at) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY total_amount DESC;

案例2：多层级组织架构查询

-- 查询员工及其所有上级（使用递归 CTE）
WITH RECURSIVE org_tree AS (
    -- 基础查询：顶级员工（没有上级）
    SELECT 
        id,
        name,
        manager_id,
        1 AS level,
        CAST(name AS CHAR(1000)) AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询：查找下属
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        ot.level + 1,
        CONCAT(ot.path, ' > ', e.name)
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, path;

案例3：查找缺失的关联数据

-- 查找没有订单的用户
SELECT u.id, u.name, u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- 查找没有用户的订单（数据异常）
SELECT o.id, o.user_id, o.product
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;

案例4：复杂的多表关联查询

-- 查询用户购买的商品分类统计
SELECT 
    u.name,
    c.name AS category,
    COUNT(oi.id) AS item_count,
    SUM(oi.quantity * oi.price) AS total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
WHERE o.status = 'completed'
GROUP BY u.id, u.name, c.id, c.name
ORDER BY u.name, total_spent DESC;

案例5：同期对比分析

-- 本月与上月销售对比
WITH monthly_sales AS (
    SELECT 
        DATE_FORMAT(order_date, '%Y-%m') AS month,
        SUM(amount) AS total
    FROM orders
    WHERE order_date >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT 
    m1.month,
    m1.total AS current_month,
    m2.total AS last_month,
    ROUND((m1.total - m2.total) / m2.total * 100, 2) AS growth_rate
FROM monthly_sales m1
LEFT JOIN monthly_sales m2 
    ON m2.month = DATE_FORMAT(DATE_SUB(STR_TO_DATE(CONCAT(m1.month, '-01'), '%Y-%m-%d'), INTERVAL 1 MONTH), '%Y-%m');

小结

本章我们学习了：

1. JOIN 类型：INNER、LEFT、RIGHT、FULL OUTER、CROSS、SELF JOIN
2. 执行原理：Nested-Loop、Block Nested-Loop、Hash Join
3. 高级技术：Semi-Join、Anti-Join
4. 性能优化：索引、驱动表选择、EXPLAIN 分析
5. 常见陷阱：NULL 处理、笛卡尔积、条件位置
6. 实战案例：订单统计、层级查询、数据对比

最佳实践总结：

场景	建议
连接列	创建索引
驱动表选择	小表驱动大表
LEFT JOIN	用于保留左表所有记录
查找缺失关联	LEFT JOIN + IS NULL
子查询转 JOIN	使用 EXISTS/IN
复杂查询	使用 CTE 提高可读性

练习

1. 查询所有用户的订单信息（包含没有订单的用户）
2. 查询有订单的用户及其订单数量
3. 查询没有订单的用户列表
4. 实现一个三表 JOIN 查询，统计每个用户的商品分类消费
5. 使用 SELF JOIN 查询同一部门中薪资相近的员工对

参考资料

• MySQL 官方文档 - JOIN 语法
• MySQL 官方文档 - Nested-Loop Join
• MySQL 官方文档 - Hash Join
• MySQL 官方文档 - Block Nested-Loop