SQL Academy · 课时

什么是自连接

Q: 「什么是自连接」课时是免费的吗？

是的 — 「什么是自连接」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。

Q: 「什么是自连接」这节课中我会学到什么？

使用别名将表连接到自身 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

Q: 学习 SQL Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 1 节课，共 4 节。

Q: 「什么是自连接」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

Q: 我能在这节 SQL Academy 课中编写并运行代码吗？

能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

使用别名将表连接到自身

什么是自连接 是 CoddyKit 上的免费 SQL Academy 课时。这是第 1 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

将表与自身连接

自连接是指将一个表与自身连接。这起初听起来很不寻常，但当单个表中的行与同一个表中的其他行存在关联时，这是一种强大的技术。

最常见的例子是一个员工表，其中每个员工行还存储其经理的 ID，而这位经理也是同一个表中的员工。

员工表

请考虑一个employees表，其中每一行都有一个id、一个name，以及一个指向同一个表中另一行的manager_id。

这种结构称为自引用关系或递归关系。请运行下面的查询来创建并填充该表。

CREATE TABLE employees (
  id         INT PRIMARY KEY,
  name       VARCHAR(50),
  manager_id INT
);

INSERT INTO employees VALUES
  (1, 'Alice',   NULL),
  (2, 'Bob',     1),
  (3, 'Carol',   1),
  (4, 'Dave',    2),
  (5, 'Eve',     2),
  (6, 'Frank',   3);

为什么普通连接在这里不起作用

如果您想在每个员工旁边显示其经理的姓名，这两部分数据都位于同一个表中。普通连接会连接两个不同的表（或者至少是两个不同的数据源）。

要从同一个表读取两次——一次代表员工，一次代表经理——您需要将自连接与表别名结合使用。

引入别名

要执行自连接，您需要在FROM / JOIN子句中两次写出同一个表名，并为每次出现指定不同的别名。别名可以让查询语言将它们视为两个独立的逻辑表。

通常会使用简短且具有描述性的别名，例如用e表示员工，用m表示经理。

-- Conceptual structure (not yet a full query)
-- FROM employees AS e
-- JOIN employees AS m ON e.manager_id = m.id

您的第一个自连接

下面是一个完整的自连接示例，用于列出每位员工及其经理的姓名。ON条件会将员工的manager_id与经理的id关联起来。

Alice 没有经理，因此会被INNER JOIN排除——因为经理一侧没有与她匹配的行。

SELECT
  e.name   AS employee,
  m.name   AS manager
FROM employees AS e
INNER JOIN employees AS m
  ON e.manager_id = m.id;

包含没有经理的行

使用INNER JOIN时，没有匹配项的行会被删除。Alice 的manager_id = NULL，因此她会从结果中消失。

请改用LEFT JOIN来保留所有员工；没有经理时，只需显示NULL（或一个标签）即可。

SELECT
  e.name             AS employee,
  COALESCE(m.name, 'No Manager') AS manager
FROM employees AS e
LEFT JOIN employees AS m
  ON e.manager_id = m.id;

筛选自连接结果

您可以像处理其他查询一样，向自连接添加WHERE子句。下面的示例只提取直属经理是 Alice 的员工（标识符 = 1）。

SELECT
  e.name  AS employee,
  m.name  AS manager
FROM employees AS e
INNER JOIN employees AS m
  ON e.manager_id = m.id
WHERE m.name = 'Alice';

统计直属下属数量

自连接可以自然地与聚合函数结合使用。下面的查询通过按经理别名分组，统计每位经理拥有多少名直属下属。

SELECT
  m.name           AS manager,
  COUNT(e.id)      AS direct_reports
FROM employees AS e
INNER JOIN employees AS m
  ON e.manager_id = m.id
GROUP BY m.name
ORDER BY direct_reports DESC;

在非层级数据上使用自连接

自连接并不局限于层级结构。只要一个表中的行与同一个表中的其他行存在关联，就可以使用自连接来提供帮助。

例如，您可以查找所有拥有同一经理的员工对：根据匹配的manager_id值将表与自身连接，同时确保不会将员工与自己配成一对。

SELECT
  a.name AS employee_1,
  b.name AS employee_2,
  a.manager_id
FROM employees AS a
INNER JOIN employees AS b
  ON a.manager_id = b.manager_id
  AND a.id < b.id;

别名的作用

自连接必须使用别名——没有别名，数据库无法判断每一列引用的是表的哪个副本，您将会收到歧义错误。

您可以使用任意别名名称。关键规则是：每个列引用都必须带有正确的别名前缀，这样数据库才能知道要查看表的哪个实例。

-- This will fail: column 'name' is ambiguous
-- SELECT name FROM employees JOIN employees ON manager_id = id;

-- This works: aliases remove the ambiguity
SELECT e.name, m.name
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.id;

实际应用场景

自连接在实际数据库中非常常见。常见应用场景包括：

组织架构图——员工及其经理
分类树——父分类和子分类
物料清单——由其他组件构成的组件
社交网络——在单个连接表中查找朋友的朋友

在数据库模式中识别出自引用外键，是可能需要自连接的第一个线索。

快速检查

继续学习之前，请测试您对自连接的理解。

课程回顾

在本课中，您学习了什么是自连接以及何时使用它：

自连接通过使用两个不同的别名两次引用同一个表，将表与自身连接起来
使用INNER JOIN排除没有匹配项的行（例如顶层经理），或使用LEFT JOIN保留这些行
可以像处理其他连接一样，添加WHERE、GROUP BY和聚合函数
在ON子句中使用a.id < b.id，避免比较同一个表中的行时产生重复的配对
自连接是处理层级数据、分类树以及任何自引用关系的首选工具

接下来：使用递归 CTE 探索多层级结构。

常见问题解答