28. 自连接与交叉连接

本节目标：理解自连接（SELF JOIN）的概念，学会用一张表自己跟自己 JOIN 来查层级关系；理解交叉连接（CROSS JOIN）生成笛卡尔积的原理，掌握适用场景和性能风险。

28.1 自连接（SELF JOIN）

自连接就是一张表跟自己 JOIN。听起来奇怪，但实际需求不少。

比如一个员工表，每行有个 manager_id 指向另一个员工的 id。想查”每个员工和他的直接上级是谁”，就得让员工表跟自己连。

用 users 和 orders 演示

我们的示例表 users 和 orders 之间没有自引用关系。为了演示自连接，先给 users 表加一个 referrer_id 列（推荐人 ID），指向另一个用户的 id：

-- 加推荐人列
ALTER TABLE users ADD COLUMN referrer_id INT;

-- 设置推荐人
UPDATE users SET referrer_id = 2 WHERE id = 1;  -- 张三是李四推荐的
UPDATE users SET referrer_id = 3 WHERE id = 4;  -- 赵六是王五推荐的

现在 users 表的数据：

+----+------+-------------+
| id | name | referrer_id |
+----+------+-------------+
|  1 | 张三 |      2      |
|  2 | 李四 |     NULL     |
|  3 | 王五 |     NULL     |
|  4 | 赵六 |      3      |
+----+------+-------------+

查每个用户和他的推荐人

SELECT
    u.username AS 用户,
    r.username AS 推荐人
FROM users u
LEFT JOIN users r ON u.referrer_id = r.id;

关键点：FROM users u 和 LEFT JOIN users r 是同一张表，但起了不同的别名。u 代表”用户”，r 代表”推荐人”。MySQL 把它们当作两张独立的表来处理。

结果：

+------+--------+
| 用户 | 推荐人 |
+------+--------+
| 张三 | 李四   |
| 李四 | NULL   |
| 王五 | NULL   |
| 赵六 | 王五   |
+------+--------+

Note

自连接的本质就是给同一张表起两个不同的别名，然后像普通 JOIN 一样连接。数据库不关心是不是同一张表，它只认别名。

用 INNER JOIN 还是 LEFT JOIN

上面用了 LEFT JOIN，所以没有推荐人的用户（李四、王五）也会出现，推荐人显示 NULL。如果用 INNER JOIN，没有推荐人的用户就不会出现。

-- 只查有推荐人的用户
SELECT u.username AS 用户, r.username AS 推荐人
FROM users u
INNER JOIN users r ON u.referrer_id = r.id;
-- 李四和王五不会出现

Tip

自连接通常用 LEFT JOIN，因为你想看到所有行，包括那些没有”关联对象”的。

28.2 自连接的经典场景

场景一：层级关系

员工-经理是最经典的层级关系。一张 employees 表，manager_id 指向上级的 id：

SELECT
    e.name AS 员工,
    m.name AS 经理
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

场景二：同一张表内比较

查年龄相同的用户对：

SELECT
    a.username AS 用户A,
    b.username AS 用户B,
    a.age
FROM users a
INNER JOIN users b ON a.age = b.age AND a.id < b.id;

a.id < b.id 是关键，避免自己跟自己连（a.id = b.id），也避免重复对（A-B 和 B-A 只保留一个）。

场景三：连续值查询

查连续注册的用户（后一个的注册时间紧挨着前一个）：

SELECT
    a.username AS 前一个,
    b.username AS 后一个,
    TIMESTAMPDIFF(MINUTE, a.created_at, b.created_at) AS 间隔分钟
FROM users a
INNER JOIN users b ON b.id = a.id + 1
WHERE TIMESTAMPDIFF(MINUTE, a.created_at, b.created_at) < 60;

Warning

自连接时一定要加条件限制（如 a.id < b.id 或 a.id != b.id），否则每行会跟自己连一次，还会产生对称的重复对。两张 1000 行的表自连接不加限制，会产生 100 万行结果。

28.3 交叉连接（CROSS JOIN）

CROSS JOIN 把两张表的每一行跟另一表的每一行都组合一遍，生成笛卡尔积（Cartesian Product）。

SELECT *
FROM users
CROSS JOIN orders;

如果 users 有 4 行，orders 有 10 行，结果就是 4 x 10 = 40 行。每一行都是 users 的一行和 orders 的一行的组合。

users (3行):           orders (2行):
+----+------+          +----+--------+
| id | name |          | id | amount |
+----+------+          +----+--------+
|  1 | 张三 |          |  1 |  100   |
|  2 | 李四 |          |  2 |  200   |
|  3 | 王五 |          +----+--------+
+----+------+

CROSS JOIN 结果 (3 x 2 = 6行):
+----+------+----+--------+
| id | name | id | amount |
+----+------+----+--------+
|  1 | 张三 |  1 |  100   |
|  1 | 张三 |  2 |  200   |
|  2 | 李四 |  1 |  100   |
|  2 | 李四 |  2 |  200   |
|  3 | 王五 |  1 |  100   |
|  3 | 王五 |  2 |  200   |
+----+------+----+--------+

CROSS JOIN 的语法

-- 显式写法
SELECT * FROM users CROSS JOIN orders;

-- 隐式写法（逗号连接，没有 ON 条件）
SELECT * FROM users, orders;

两种写法等价。CROSS JOIN 不需要 ON 条件（写了也会被忽略）。

Note

实际上，INNER JOIN 如果忘了写 ON 条件，效果跟 CROSS JOIN 一样。SELECT * FROM users INNER JOIN orders; 会生成笛卡尔积。这通常是 bug，不是有意为之。

28.4 CROSS JOIN 的适用场景

CROSS JOIN 看起来像”错误操作”，但有些场景确实需要它。

场景一：生成组合数据

比如要生成”所有用户 x 所有状态”的组合，用于做报表的占位：

-- 生成所有用户和所有订单状态的组合
SELECT u.username, s.status
FROM users u
CROSS JOIN (
    SELECT DISTINCT status FROM orders
) AS s;

这样每个用户都会跟每种状态组合一遍，方便后续统计”每个用户每种状态有多少订单”（没有的显示 0）。

场景二：生成序列或日期

-- 生成最近 7 天的日期序列
SELECT
    DATE_SUB(CURDATE(), INTERVAL n.day DAY) AS 日期
FROM (
    SELECT 0 AS day UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
    UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
) AS n;

这不是严格意义的 CROSS JOIN，但利用了类似的多行组合思路。

场景三：测试数据生成

-- 给每个用户生成一条测试订单
INSERT INTO orders (user_id, amount, status)
SELECT u.id, 100.00, 'pending'
FROM users u
CROSS JOIN (SELECT 1 AS x) AS t;

Tip

CROSS JOIN 在实际开发中用得不多。大多数场景用 INNER JOIN + ON 条件就够了。但理解笛卡尔积的概念很重要，因为 JOIN 忘写 ON 条件就会产生笛卡尔积，导致结果爆炸。

28.5 笛卡尔积的性能风险

CROSS JOIN 的结果行数是两表行数的乘积。

左表行数	右表行数	笛卡尔积行数
100	100	10,000
1,000	1,000	1,000,000
10,000	10,000	100,000,000

Warning

两张万行表的 CROSS JOIN 会产生一亿行结果，内存和磁盘都扛不住。MySQL 有个 max_join_size 参数限制连接结果大小，超过会拒绝执行。但别依赖这个保护，写 SQL 时想清楚你要的是不是笛卡尔积。

28.6 自连接 vs 递归 CTE

自连接只能查一层关系（员工和直接上级）。如果想查整个层级树（从 CEO 到最底层员工的完整链路），自连接做不到，需要递归 CTE。

-- 自连接只能查一层：员工 -> 直接经理
SELECT e.name AS 员工, m.name AS 经理
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- 递归 CTE 能查整个层级（下章详讲）
WITH RECURSIVE org_chain AS (
    SELECT id, name, manager_id, 1 AS 层级
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, oc.层级 + 1
    FROM employees e
    INNER JOIN org_chain oc ON e.manager_id = oc.id
)
SELECT * FROM org_chain;

Note

递归 CTE 是 8.0+ 特性，下一章会详细讲。对于简单的单层关系查询，自连接就够了。多层层级查询需要递归 CTE。

28.7 各种 JOIN 汇总

到本章为止，MySQL 的 JOIN 类型都讲完了：

JOIN 类型	说明	结果行数
INNER JOIN	只取匹配行	<= 较小表行数
LEFT JOIN	左表全保留	= 左表行数
RIGHT JOIN	右表全保留	= 右表行数
SELF JOIN	表自己连自己	取决于连接类型
CROSS JOIN	笛卡尔积	左表 x 右表
FULL OUTER JOIN	全外连接	MySQL 不支持

Tip

SELF JOIN 不是一种独立的 JOIN 类型，而是一种用法。它可以配合 INNER JOIN、LEFT JOIN 等使用。同理，CROSS JOIN 可以看作”没有 ON 条件的 INNER JOIN”。

28.8 小结

自连接是同一张表起两个别名后自己跟自己 JOIN，适合查层级关系和同表内比较。交叉连接生成笛卡尔积，每行跟每行组合，适合生成组合数据和测试数据。核心记住：自连接要加条件防止自己连自己，CROSS JOIN 结果行数是乘积关系，慎用在大表上。下一章进入 8.0+ 的高级特性—窗口函数。