自连接是什么
自连接是把同一张表连接自身，用两个不同别名把它当成两张表来用，典型场景是员工与经理、分类与父分类、同一表内两行的成对比较。代价是中间结果接近表规模的平方，行数估算、连接键索引都要提前确认，否则一次自连接就能把库压住。
基本信息
| 别名 | 两侧必须不同，否则引用歧义，MySQL 报 Not unique table/alias |
|---|---|
| 连接类型 | 保留没有上级的根节点用 LEFT JOIN，只要叶子节点用 INNER JOIN |
| 连接键 | 一般是主键与父级关系列，父级列务必单独建索引 |
| 层级深度 | 只连一层用自连接，两层以上改用递归 CTE |
| 语法形态 | FROM employees e JOIN employees m ON m.id = e.manager_id |
员工与经理
员工与经理
SELECT e.id,
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id -- 同一张表出现两次，靠别名区分
ORDER BY e.id;
-- 去掉 LEFT 后，没有上级的 CEO 会从结果里消失成对比较写法
成对比较写法
-- 找出金额相同的不同订单对，避免自身配对与重复配对
SELECT a.order_no AS order_a,
b.order_no AS order_b,
a.amount
FROM orders a
JOIN orders b ON b.amount = a.amount AND b.id > a.id -- 用大小关系破坏对称
WHERE a.amount > 100
ORDER BY a.amount; -- 漏写 b.id > a.id 会重复且含自身行常见形态对照
| 形态 | 写法要点 | 注意事项 |
|---|---|---|
| 上下级 | e.manager_id = m.id | 根节点关系列为 NULL，必须用 LEFT JOIN |
| 成对比较 | b.id > a.id 打破对称 | 漏写会得到 (a,b) 与 (b,a) 两条并含自身 |
| 先后关系 | a.created_at < b.created_at | 时间相同时补主键做次级条件 |
| 查找孤立行 | LEFT JOIN 自身后判 IS NULL | 比 NOT EXISTS 直观，但性能通常略差 |
| 替代方案 | 窗口函数或分组聚合 | 多数场景只扫一遍表，代价更低 |
典型使用场景
- 组织架构：一张表同时列出员工与直属上级
- 分类树：列出子分类及其父分类名称
- 同一表内成对或相邻比较：找差值、找重复记录
- 查找缺失关系：找出没有下级的管理节点
- 合并去重：按业务键同表连接自身，找出重复键的全部记录
- 作为窗口函数替代方案的对照写法，用于验证两种实现结果是否一致
自连接不是唯一解：找“每组最新一行”“层级展开”这类需求，窗口函数或递归 CTE 通常只扫一遍表，比自连接的平方级中间结果省资源，先评估再动手。
自连接不必然扫两遍全表：连接键有索引时优化器能把内层变成等值查找；没有索引时外层每行都要扫一遍内层，大表上耗时近似按平方增长，执行前先用 EXPLAIN 看实际访问方式。