SQL SELF JOIN：表与自身连接

第 5.7 课 · 阅读时间：约 5 分钟 · 练习题 ↓

SQL 自连接（self join）把一张表与它自身连接：同一张表以两个不同的别名在查询中出现两次。在本课中，你将了解为什么必须使用别名、如何不重复地找出具有共同值的行对，以及自连接如何读取员工-经理层级结构。

有时需要关联的行位于同一张表中：员工和他的经理、姓氏相同的两位演员、航班和它的衔接航班。SQL 中并没有专门的 SELF JOIN 关键字。它就是普通的 INNER JOIN 或 LEFT JOIN，只不过两侧是同一张表。

别名是必需的

数据库必须知道每一列来自表的哪一份副本，因此每份副本都需要自己的别名：

SELECT a1.column, a2.column
FROM some_table AS a1
INNER JOIN some_table AS a2 ON a1.some_column = a2.some_column;

可以把 a1 和 a2 看作内容相同的两张独立的表。

查找行对

哪些演员的姓氏相同？

SELECT a1.first_name AS first_actor,
       a2.first_name AS second_actor,
       a1.last_name
FROM actor AS a1
INNER JOIN actor AS a2 ON a2.last_name = a1.last_name
                      AND a2.actor_id > a1.actor_id
ORDER BY a1.last_name, first_actor, second_actor;

结果：108 对姓氏相同的演员，每对只出现一次。

条件 a2.actor_id > a1.actor_id 同时完成两件事：

• 排除演员与自身的匹配，因为每位演员的姓氏都与自己相同；
• 在两个镜像配对中只保留一个：如果用 <>，你会同时得到“CHRISTIAN–DEBBIE AKROYD”和“DEBBIE–CHRISTIAN AKROYD”。

比较行

自连接还可以把一行与同一张表中的其他行进行比较。查找与“ACADEMY DINOSAUR”分级相同但时长更长的电影：

SELECT f2.title, f2.rating, f2.length
FROM film AS f1
INNER JOIN film AS f2 ON f2.rating = f1.rating
                     AND f2.length > f1.length
WHERE f1.title = 'ACADEMY DINOSAUR'
ORDER BY f2.length DESC
LIMIT 10;

结果：比“ACADEMY DINOSAUR”（86 分钟）更长的分级为 PG 的电影中最长的 10 部。

层级结构：员工和经理

经典的自连接是存储在一张表中的层级结构：每个员工行都有一个 manager_id，指向同一张表中的另一行。Sakila 中没有这样的表，所以下面给出通用写法：

SELECT e.name AS employee,
       m.name AS manager
FROM employee AS e
LEFT JOIN employee AS m ON m.employee_id = e.manager_id;

• e 是扮演员工角色的副本，m 扮演经理角色。
• LEFT JOIN 保留最高层的经理，他的 manager_id 为 NULL；如果用 INNER JOIN，这一行会消失。

一次自连接只向上走一层。要遍历任意深度的整个层级结构，你将在第 6.5 课中使用递归 CTE。

要点：

• 自连接是两侧为同一张表的普通连接。
• 表的每份副本都需要自己的别名。
• 使用 a1.id < a2.id 排除行与自身的匹配以及重复的镜像配对。
• 对于层级结构，把 manager_id 与同一张表的键连接；使用 LEFT JOIN 保留最高层。

常见问题

SQL 中的自连接是什么？

表与它自身的连接。这张表以不同的别名列出两次，连接条件把一份副本中的行与另一份副本中的行关联起来，例如把员工与其经理关联。

如何避免自连接中出现重复的配对？

用 < 而不是 <> 比较键：a1.id < a2.id。这样既排除了行与自身的匹配，又在两个镜像配对中只保留一个。

SELF JOIN 是单独的 SQL 关键字吗？

不是。自连接用 INNER JOIN 或 LEFT JOIN 来写；唯一的区别在于连接中的两张表是同一张表。