# 自连接

> 查询同一张表中的关系，并跟踪员工层级。

---

LLMS 索引： [llms.txt](/llms.txt)

---

当一张表与自身连接时，就是*自连接*。你需要为它取两个别名，让 PostgreSQL 能分辨你指的是哪一个副本，并用一个条件把相关行连接起来。只要数据模型表示同类行之间的关系，就会用到自连接——员工与经理、评论与父评论、前驱与后继。对于任意深度的层级关系，合适的工具是递归 CTE（后面的课程会介绍）；只有一层时，自连接简单而且快速。

## 本章要点 {#learn}

- 使用两个别名把一张表与自身连接
- 为自连接选择 `INNER` 还是 `LEFT`
- 建模一层层级关系（员工 → 经理）
- 初步了解任意深度的递归 CTE
- 为列取名以提高清晰度

## 示例 {#example}

```sql {num="31.1" caption="自连接"}
DROP TABLE IF EXISTS employees_example;
CREATE TABLE employees_example (
  id         integer PRIMARY KEY,
  name       text NOT NULL,
  manager_id integer REFERENCES employees_example(id)
);

INSERT INTO employees_example VALUES
  (1, 'Alice',  NULL),    -- top of the hierarchy
  (2, 'Bob',    1),       -- reports to Alice
  (3, 'Carol',  1),       -- reports to Alice
  (4, 'Dave',   2),       -- reports to Bob
  (5, 'Eve',    2);       -- reports to Bob

-- One level: employee and their direct manager
SELECT e.name AS employee, m.name AS manager
FROM employees_example e
LEFT JOIN employees_example m ON e.manager_id = m.id
ORDER BY e.id;

-- Count direct reports per manager
SELECT m.name AS manager, count(e.id) AS direct_reports
FROM employees_example m
LEFT JOIN employees_example e ON e.manager_id = m.id
GROUP BY m.id, m.name
ORDER BY m.id;

-- A taste of recursion: full chain to the top (covered fully in the CTE lesson)
WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees_example
  WHERE id = 4                                       -- start at Dave
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.depth + 1
  FROM employees_example e
  JOIN chain c ON e.id = c.manager_id
)
SELECT * FROM chain;
```

在连接 `FROM employees_example e LEFT JOIN employees_example m ON e.manager_id = m.id` 中，`e` 是*员工*行，`m` 是*经理*行。两者都指向同一张物理表——PostgreSQL 把这些别名当作彼此独立的副本。使用 `LEFT JOIN` 意味着没有经理的员工（Alice）仍会出现，经理列为 NULL。

第二个查询中的 `count(e.id)`（而不是 `count(*)`）是标准写法：`count(*)` 会把未匹配行产生的 NULL 也算进去，错误地让每个叶子经理至少拥有 1 个直接下属。`count(e.id)` 只统计非 NULL 的 id，即实际的下属。

结尾的递归 CTE 是对 [CTE 课程](/38-cte/) 的预览。`WITH RECURSIVE` 让你可以遍历任意深度——锚点行（Dave）是起点；递归步骤重新连接到 `chain`，每次向上走一层。

## 运行示例 {#run}

运行方式：

```text
$ psql -f source/self-join.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 5
 employee | manager
----------+---------
 Alice    |
 Bob      | Alice
 Carol    | Alice
 Dave     | Bob
 Eve      | Bob
(5 rows)
 ...
```

## 常见陷阱 {#pitfalls}

- 用 `INNER JOIN` 而不是 `LEFT JOIN`，会悄悄丢掉层级顶端的行。顶层行没有经理，`INNER JOIN` 会将其丢弃。
- 忘记 `e` 和 `m` 是同一张表。若条件可能允许同一行匹配，可以使用 `WHERE e.id <> m.id` 排除这种情况。
- 想找出“没有子项的组”时，不要统计 `*`，而要统计连接进来的表的 id——`count(child.id)` 才是正确写法。
- 对于任意深度，反复进行自连接（`emp → mgr → mgr's mgr → ...`）无法扩展。请改用 `WITH RECURSIVE`。

> [!TIP] 提示
> 对称关系（friend-of）通常使用单独的关联表，而不是自连接。非对称关系（manager-of、parent-of）很适合用自引用列表示。

> [!NOTE] 动手练习
> 在 Carol 下面新增第六名员工。重新运行直接下属查询——现在 Carol 应该有一名直接下属。然后修改递归 CTE，让它从 Eve 而不是 Dave 开始，看看链条如何变化。

## 下载 {#downloads}

SQL 文件：[self-join.sql](/examples/source/self-join.sql) · [全部示例文件](/examples/examples.zip) · [准备工作](/#prerequisites)