P41 41. 基础-多表查询-自连接

一句话理解

自连接就是一张表当两张用：给同一张表起两个别名，一个当员工、一个当上级，再按 manager_id = id 连起来。

按这 4 步做

1. 先给同一张表两个身份。e 代表“我”，m 代表“我的上级”；不给别名，MySQL 分不清你要哪一行。 别名起得有含义（e 员工、m 上级），比 a、b 好排查。
2. 连接条件是 e.manager_id = m.id，即“我的上级编号 = 对方的员工编号”。这跟普通两表连接没区别，只是两张表恰好同名。
3. 顶层老板没有上级，manager_id 是 NULL。要把他留在结果里就用 LEFT JOIN；用内连接他会消失，看起来像“少了个人”。
4. 字段一律带别名前缀。同名列（两张表都叫 name、id）不带前缀会报 ambiguous，带上就清楚了。

小例

先准备本集练习数据：p41.sql · 练习库说明

-- 表：department(1 研发部, 2 测试部, 3 运营部)
-- employee：小林22/研发8000/上级2，小周25/研发10000/无上级，小陈30/测试12000/上级2，小李21/无部门/NULL/上级2
-- 建表与插入语句见本页练习脚本 p41.sql（独立练习库 mysqlnotes_p41，跑一次就有这两张表）

SELECT e.name AS 员工, m.name AS 上级
FROM employee e
LEFT JOIN employee m ON e.manager_id = m.id
ORDER BY e.id;

4 行：小林-小周、小周-NULL、小陈-小周、小李-小周。小周是老板，manager_id 为空，所以上级列是 NULL。把 LEFT 改成 INNER，只剩 3 行，小周整行不见——这是自连接最容易踩的坑。 把 ON 改成 e.id = m.manager_id，方向就反过来了，查的是“我的下属”，别名含义要跟着改。

易错点

• 两个别名写成同一个、或干脆不起别名，会报表名重复 / 字段 ambiguous。
• 用内连接查“所有员工及其上级”会漏掉没有上级的人；要全量必须 LEFT JOIN。
• 把 manager_id 理解成“部门编号”之类的别的字段，连接条件一错，结果会很整齐地错。

30 秒速记

自测（3 题，先答再展开）

材料与来源