PHP小码农

Appearance

自连接（Self Join） ​

本节讲解自连接（Self Join）的概念与必须使用别名的原因、层级数据查询、同表不同行的比较模式、与 EXISTS 的取舍，以及何时应当迁移到递归 CTE。学完本节，你将能熟练处理组织架构、分类树、同表差值等典型自连接场景。

前置知识

阅读本节前，建议先了解：自然连接与 USING

基础概念 ​

自连接（Self Join） 指同一张表与自身连接。它在语法上没有特殊之处，但必须为两个"实例"取不同的别名，否则 PostgreSQL 无法区分引用的是哪一份。

概念术语说明
自连接Self Join同一张表与自身连接，用别名区分两份实例
层级数据Hierarchical Data树形/层级结构，如组织架构、分类树
邻接表Adjacency List用 parent_id 指向父行的层级存储方式
根节点Root Node没有父节点的行（parent_id IS NULL）
叶子节点Leaf Node没有子节点的行
递归查询Recursive Query需要多层展开时用 WITH RECURSIVE

自连接的核心价值在于让同一张表的不同行出现在同一行输出中，从而实现"行与行的比较"。

场景需求自连接的作用
层级查询找出每行的上级子行连接父行
差异比较找出金额高于另一个订单的订单同行连接同行
重复检测找出重复记录同值行配对
前后记录找出相邻时间点的记录前连接后
层级展开展开多层的树无法一次完成，需递归 CTE

语法与用法 ​

自连接的语法要点是别名必须不同：

text
SELECT 列列表
FROM 表名 AS 别名A
JOIN 表名 AS 别名B ON 连接条件(别名A, 别名B)
[WHERE 过滤]
[ORDER BY 排序];

准备本节贯穿使用的示例表：

sql
-- 员工表：用 manager_id 指向直属上级（邻接表结构）
CREATE TABLE employees (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       TEXT    NOT NULL,
  title      TEXT    NOT NULL,
  manager_id INTEGER,                      -- 指向 employees.id，顶级为 NULL
  salary     NUMERIC(10, 2) NOT NULL,
  dept       TEXT    NOT NULL
);

INSERT INTO employees (name, title, manager_id, salary, dept) VALUES
  ('root',   'CEO',         NULL, 80000.00, 'exec'),      -- id=1 根节点
  ('alice',  'CTO',            1, 60000.00, 'tech'),      -- id=2
  ('bob',    'CFO',            1, 60000.00, 'finance'),   -- id=3
  ('carol',  'Tech Lead',      2, 45000.00, 'tech'),      -- id=4
  ('dave',   'Engineer',       4, 30000.00, 'tech'),      -- id=5
  ('eve',    'Engineer',       4, 30000.00, 'tech'),      -- id=6
  ('frank',  'Accountant',     3, 28000.00, 'finance');   -- id=7

-- 分类表：自引用层级
CREATE TABLE categories (
  id        INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name      TEXT    NOT NULL,
  parent_id INTEGER
);

INSERT INTO categories (name, parent_id) VALUES
  ('全部商品',   NULL),   -- id=1
  ('数码',          1),   -- id=2
  ('家电',          1),   -- id=3
  ('手机',          2),   -- id=4
  ('笔记本',        2),   -- id=5
  ('洗衣机',        3);   -- id=6

基本自连接：查出每个员工与其上级：

sql
SELECT
  e.name      AS employee,
  e.title     AS emp_title,
  m.name      AS manager,
  m.title     AS mgr_title
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id
ORDER BY e.id;
text
 employee | emp_title  | manager | mgr_title
----------+------------+---------+-----------
 root     | CEO        | (null)  | (null)
 alice    | CTO        | root    | CEO
 bob      | CFO        | root    | CEO
 carol    | Tech Lead  | alice   | CTO
 dave     | Engineer   | carol   | Tech Lead
 eve      | Engineer   | carol   | Tech Lead
 frank    | Accountant | bob     | CFO
(7 rows)

详细说明 ​

为什么必须用别名 ​

不用别名时，PostgreSQL 无法区分两份实例，会报错或产生歧义：

sql
-- 错误！表名重复，无法区分
SELECT name FROM employees JOIN employees ON employees.id = employees.manager_id;
-- 报错：table name "employees" specified more than once

-- 正确：用别名区分两份实例
SELECT e.name, m.name
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id;

即使 PostgreSQL 允许重复表名（某些数据库会隐式处理），引用列时也无法区分，因此别名是自连接的必要条件。

层级数据查询 ​

自连接一次只能向上/向下看一层。这是它的核心限制，也是判断"是否该用递归 CTE"的关键。

sql
-- 只查一层：每个员工的直属上级
SELECT e.name AS employee, m.name AS direct_manager
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id
ORDER BY e.id;

-- 只查两层：再连接一次得到"上级的上级"
SELECT
  e.name  AS employee,
  m.name  AS manager,
  g.name  AS grand_manager
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id
LEFT JOIN employees AS g ON g.id = m.manager_id
ORDER BY e.id;
text
 employee | manager | grand_manager
----------+---------+---------------
 root     | (null)  | (null)
 alice    | root    | (null)
 bob      | root    | (null)
 carol    | alice   | root
 dave     | carol   | alice
 eve      | carol   | alice
 frank    | bob     | root
(7 rows)

层级深浅不定时，自连接无法胜任，必须改用递归 CTE：

sql
-- 展开任意深度：用递归 CTE（PostgreSQL 8.4+）
WITH RECURSIVE tree AS (
  -- 锚点：根节点
  SELECT id, name, parent_id, 1 AS depth,
         name::text AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- 递归：连接到已展开的父节点
  SELECT c.id, c.name, c.parent_id, t.depth + 1,
         t.path || ' > ' || c.name
  FROM categories AS c
  JOIN tree AS t ON t.id = c.parent_id
)
SELECT id, name, depth, path
FROM tree
ORDER BY path;
text
 id |    name    | depth |              path
----+------------+-------+-------------------------------
  1 | 全部商品    |     1 | 全部商品
  2 | 数码        |     2 | 全部商品 > 数码
  4 | 手机        |     3 | 全部商品 > 数码 > 手机
  5 | 笔记本      |     3 | 全部商品 > 数码 > 笔记本
  3 | 家电        |     2 | 全部商品 > 家电
  6 | 洗衣机      |     3 | 全部商品 > 家电 > 洗衣机
(6 rows)

判断标准：如果能事先确定最大层数（如"最多 3 级组织"），用两三次自连接即可；若层数不定，一律用递归 CTE。

比较同表不同行 ​

自连接最强大的能力是"把同一张表的两行放在一起比较"。典型模式是 a.key = b.key AND a.value > b.value。

sql
-- 找出薪资高于同部门其他同事的员工对
SELECT
  a.name   AS higher_paid,
  a.salary AS a_salary,
  b.name   AS lower_paid,
  b.salary AS b_salary,
  a.dept
FROM employees AS a
JOIN employees AS b
  ON b.dept = a.dept
 AND a.salary > b.salary
ORDER BY a.dept, a.salary DESC;
text
 higher_paid | a_salary | lower_paid | b_salary |   dept
-------------+----------+------------+----------+---------
 alice       | 60000.00 | carol      | 45000.00 | tech
 alice       | 60000.00 | dave       | 30000.00 | tech
 alice       | 60000.00 | eve        | 30000.00 | tech
 carol       | 45000.00 | dave       | 30000.00 | tech
 carol       | 45000.00 | eve        | 30000.00 | tech
 bob         | 60000.00 | frank      | 28000.00 | finance
(6 rows)

查找重复数据也是典型场景：

sql
-- 找出同名的员工（重复检测）
SELECT a.id AS id_a, b.id AS id_b, a.name, a.dept
FROM employees AS a
JOIN employees AS b
  ON b.name = a.name
 AND b.id > a.id            -- 用 id 大小过滤，避免自我配对与重复配对
ORDER BY a.name;
sql
-- 找出同部门同薪资的员工对
SELECT a.name, b.name, a.dept, a.salary
FROM employees AS a
JOIN employees AS b
  ON b.dept = a.dept
 AND b.salary = a.salary
 AND b.id > a.id             -- 关键：只保留一个方向，避免 A-B 与 B-A 重复
ORDER BY a.dept, a.salary DESC;
text
 name  | name |  dept   | salary
-------+------+---------+---------
 alice | carol| tech    | 45000.00
 dave  | eve  | tech    | 30000.00
(2 rows)

b.id > a.id 是自连接比较模式的关键技巧：它保证每对只出现一次，并排除自我配对。

自连接与 EXISTS 的取舍 ​

"是否存在满足条件的另一行"可以用自连接或 EXISTS 表达。EXISTS 通常更快，因为它短路——找到第一行就停止。

sql
-- 自连接：找出至少带一个下属的管理者
SELECT DISTINCT m.id, m.name
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id
ORDER BY m.id;

-- EXISTS：语义更直接，通常执行计划更优（半连接）
SELECT m.id, m.name
FROM employees AS m
WHERE EXISTS (
  SELECT 1 FROM employees AS e WHERE e.manager_id = m.id
)
ORDER BY m.id;
text
 id | name
----+-------
  1 | root
  2 | alice
  3 | bob
  4 | carol
(4 rows)

两种方案的对比：

对比项自连接EXISTS
是否需要 DISTINCT需要（否则重复）不需要
是否短路否，需生成全部匹配行是，找到即停
可否输出另一行的字段可以不可以（除非用子查询）
执行计划Hash Join + UniqueSemi Join / Nested Loop Semi
推荐度需要另一行数据时仅判断存在性时
sql
-- 需要"另一行的具体数据"时，自连接或 LATERAL 是唯一选择
-- 找出每位管理者薪资最高的下属
SELECT m.name AS manager, e.name AS top_paid_sub,
       e.salary AS sub_salary
FROM employees AS m
JOIN LATERAL (
  SELECT name, salary
  FROM employees AS e
  WHERE e.manager_id = m.id
  ORDER BY e.salary DESC
  LIMIT 1
) AS e ON TRUE
ORDER BY m.name;
text
 manager | top_paid_sub | sub_salary
---------+--------------+------------
 alice   | carol        |   45000.00
 bob     | frank        |   28000.00
 carol   | dave         |   30000.00
(3 rows)

自连接的性能与索引要求 ​

自连接的代价约为普通连接的平方级（若不加以限制），因为它连接的是同一份数据。

因素影响应对
表大小结果行数与表大小平方相关（无谓词时）用 >、< 等谓词限制配对方向
连接列索引无索引则退化为嵌套循环 + 全表扫描为 manager_id、parent_id 等自引用列建索引
谓词选择性条件弱则中间结果巨大先用 WHERE 缩小行驶范围
排序需求若需有序输出则额外 Sort索引可提供顺序
sql
-- 自引用列（外键列）必须有索引
CREATE INDEX idx_employees_manager_id ON employees (manager_id);
CREATE INDEX idx_categories_parent_id ON categories (parent_id);

-- 复合索引：连接列 + 过滤列
CREATE INDEX idx_employees_mgr_salary ON employees (manager_id, salary DESC);
sql
-- 有索引时的计划：Nested Loop + Index Scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT e.name, m.name
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id;
text
 Nested Loop Left Join  (cost=0.14..8.62 rows=7 width=64) (actual time=0.021..0.045 rows=7 loops=1)
   ->  Seq Scan on employees e  (cost=0.00..1.07 rows=7 width=36) (actual time=0.008..0.010 rows=7 loops=1)
   ->  Index Scan using employees_pkey on employees m  (cost=0.14..1.06 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=7)
         Index Cond: (id = e.manager_id)
 Planning Time: 0.256 ms
 Execution Time: 0.079 ms

关键读法：loops=7 表示内表被探测 7 次（等于外表行数），每次都是 Index Scan —— 这正是自连接配合索引的高效形态。

实战示例 ​

下面完成三个典型自连接场景。

sql
-- 场景一：组织架构 —— 员工 / 上级 / 上上级 三级视图
SELECT
  e.name                    AS employee,
  e.title,
  coalesce(m.name, '-')     AS manager,
  coalesce(g.name, '-')     AS grand_manager,
  e.salary
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id
LEFT JOIN employees AS g ON g.id = m.manager_id
ORDER BY e.id;
text
 employee |   title    | manager | grand_manager |  salary
----------+------------+---------+---------------+----------
 root     | CEO        | -       | -             | 80000.00
 alice    | CTO        | root    | -             | 60000.00
 bob      | CFO        | root    | -             | 60000.00
 carol    | Tech Lead  | alice   | root          | 45000.00
 dave     | Engineer   | carol   | alice         | 30000.00
 eve      | Engineer   | carol   | alice         | 30000.00
 frank    | Accountant | bob     | root          | 28000.00
(7 rows)
sql
-- 场景二：分类树 —— 只查"二级分类及其父分类"
SELECT
  child.id    AS child_id,
  child.name  AS child_name,
  parent.name AS parent_name
FROM categories AS child
JOIN categories AS parent ON parent.id = child.parent_id
ORDER BY child.id;
text
 child_id | child_name | parent_name
----------+------------+-------------
        2 | 数码        | 全部商品
        3 | 家电        | 全部商品
        4 | 手机        | 数码
        5 | 笔记本      | 数码
        6 | 洗衣机      | 家电
(5 rows)
sql
-- 场景三：同表差值 —— 找出薪资差距最大的同事对（仅限同部门）
SELECT
  a.name                                  AS emp_high,
  b.name                                  AS emp_low,
  a.dept,
  a.salary - b.salary                     AS salary_gap
FROM employees AS a
JOIN employees AS b
  ON b.dept = a.dept
 AND a.salary > b.salary
ORDER BY salary_gap DESC
LIMIT 5;
text
 emp_high | emp_low |   dept  | salary_gap
----------+---------+---------+------------
 alice    | dave    | tech    |   30000.00
 alice    | eve     | tech    |   30000.00
 carol    | dave    | tech    |   15000.00
 carol    | eve     | tech    |   15000.00
 bob      | frank   | finance |   32000.00
(5 rows)
sql
-- 场景四：找出"薪资高于其直属上级"的员工（反常规检测）
SELECT
  e.name   AS employee,
  e.salary AS emp_salary,
  m.name   AS manager,
  m.salary AS mgr_salary
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id
WHERE e.salary > m.salary
ORDER BY e.salary DESC;
text
 employee | emp_salary | manager | mgr_salary
----------+------------+---------+------------
(0 rows)

本示例数据中无此情况，说明数据符合"上级薪资更高"的组织规则。

注意事项 ​

  • 自连接必须使用不同别名。 否则报 "table name specified more than once"。
sql
-- 错误！
-- SELECT name FROM employees JOIN employees ON employees.id = employees.manager_id;

-- 正确
SELECT e.name, m.name FROM employees AS e JOIN employees AS m ON m.id = e.manager_id;
  • 比较模式必须加方向谓词。 只用 = 会得到自我配对（A-A）与双向重复（A-B、B-A）。
sql
-- 错误！会包含 A-A 自我配对与 A-B / B-A 重复
SELECT a.name, b.name FROM employees AS a JOIN employees AS b ON b.dept = a.dept;

-- 正确：用 id 大小限定方向
SELECT a.name, b.name FROM employees AS a JOIN employees AS b ON b.dept = a.dept AND b.id > a.id;
  • 自连接无法展开不定深度的层级。 层数不确定时必须用递归 CTE，不要靠堆叠 LEFT JOIN 硬撑。
sql
-- 不推荐：靠固定层数硬撑，超过层数的数据会丢失
SELECT e.name, m1.name, m2.name, m3.name
FROM employees AS e
LEFT JOIN employees AS m1 ON m1.id = e.manager_id
LEFT JOIN employees AS m2 ON m2.id = m1.manager_id
LEFT JOIN employees AS m3 ON m3.id = m2.manager_id;

  • LEFT JOIN 自连接时，注意根节点的补空行为。 根节点 manager_id IS NULL，连接后上级列为 NULL，这是预期结果。

  • 自连接容易产生"平方级"中间结果。 无方向谓词的全表自连接行数为 n²，在 100 万行表上会产生万亿行。

sql
-- 危险！全表自连接
SELECT count(*) FROM employees AS a JOIN employees AS b ON TRUE;

  • DISTINCT 会抵消自连接的部分性能优势。 若只为判断存在性，用 EXISTS 更好。

  • 自引用外键应加约束。 manager_id / parent_id 应引用本表主键，且要防止形成环。

sql
ALTER TABLE employees
  ADD CONSTRAINT fk_emp_manager
  FOREIGN KEY (manager_id) REFERENCES employees (id);

最佳实践 ​

  1. 为所有自引用列建索引。 这是自连接性能的第一要素。
sql
CREATE INDEX idx_employees_manager_id ON employees (manager_id);
CREATE INDEX idx_categories_parent_id ON categories (parent_id);
  1. 优先用 LEFT JOIN 表达层级关系。 因为根节点没有上级，用 INNER JOIN 会把根节点丢掉。
sql
-- 正确：根节点也要出现在结果中
SELECT e.name, coalesce(m.name, '(顶级)') AS manager
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id;
  1. 比较模式固定使用 b.id > a.id 模式。 保证每对只出现一次，并排除自我配对。
sql
-- 自连接比较模板
SELECT a.*, b.*
FROM t AS a
JOIN t AS b
  ON b.group_key = a.group_key
 AND b.compare_col <op> a.compare_col
 AND b.id > a.id;
  1. 层级深度超过 2~3 层时改用递归 CTE。 代码更短，且能正确处理任意深度。
sql
-- 递归 CTE 是层级查询的标准解法
WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 1 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM categories AS c JOIN tree AS t ON t.id = c.parent_id
)
SELECT * FROM tree ORDER BY depth, id;
  1. 用窗口函数替代部分自连接。 取"前后一条记录"时，lag/lead 通常比自连接更简洁高效。
sql
-- 窗口函数替代自连接：取同一部门中薪资排名前后的人
SELECT
  name, dept, salary,
  lag(name)  OVER (PARTITION BY dept ORDER BY salary) AS prev_person,
  lead(name) OVER (PARTITION BY dept ORDER BY salary) AS next_person
FROM employees
ORDER BY dept, salary;
  1. 用 EXPLAIN (ANALYZE, BUFFERS) 确认内表走的是 Index Scan 而非 Seq Scan。
sql
ANALYZE employees, categories;

EXPLAIN (ANALYZE, BUFFERS)
SELECT e.name, m.name
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id;
  1. 层级数据规模增大时考虑 ltree 扩展。 它能用物化路径（如 tech.dept.team）配合 GiST 索引做高效的祖先/后代查询，性能远超递归 CTE。
sql
-- PostgreSQL 9.1+ 需先安装扩展
CREATE EXTENSION IF NOT EXISTS ltree;

-- ltree 类型的列可用 <@（是后代）、@>（是祖先）操作符直接查询
-- SELECT * FROM org WHERE path <@ 'tech'::ltree;

下一节

继续学习：连接查询优化

参考链接 ​