SQL中如何使用SELF JOIN查询层级关系-SQL-PHP中文网

SELF JOIN是将同一张表用两个不同别名视为两张表进行关联，专用于处理自关联层级关系（如员工-上级）；普通JOIN要求两张物理表，无法实现“自己关联自己”，否则报错或逻辑错误。

sql中如何使用self join查询层级关系

SELF JOIN 是什么，为什么不能用普通 JOIN

SELF JOIN 不是 SQL 的独立语法，而是把同一张表当作两个不同别名来用——本质是自关联。普通 JOIN 默认连接两张物理表，而层级关系（比如员工-上级、分类-父类）数据全在一张表里，id 和 parent_id 在同一张表，必须靠别名区分“自己”和“上级”才能关联。

常见错误是漏写别名，或者混淆字段归属，比如写成 SELECT * FROM category WHERE id = parent_id——这只会查出根节点或自环，不是层级关系。

必须给表起两个别名，例如 t1 和 t2
ON 条件里明确指向关系：通常是 t1.parent_id = t2.id（t1 是子，t2 是父）
别名一用到底：所有字段都带前缀，避免 Column 'id' in field list is ambiguous

查直接上级：一次 SELF JOIN 就够

适用于只查一级关系，比如“每个员工的直属领导姓名”。这是最轻量、最常用的场景。

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

注意用 LEFT JOIN 而不是 INNER JOIN：否则 CEO（manager_id 为 NULL）会被过滤掉。

e.manager_id = m.id 是关键连接条件，不是 e.id = m.manager_id
如果表里有循环引用（A 管 B，B 又管 A），会查出异常结果，需提前校验数据
索引建议：在 manager_id 字段建索引，否则大表性能急剧下降

查完整路径：递归 CTE 比多层 SELF JOIN 更靠谱

想查“张三 → 李四 → 王五 → CEO”这种完整链条？硬写 5 层 JOIN 不仅难维护，还卡死查询计划。MySQL 8.0+、PostgreSQL、SQL Server 都支持递归 WITH RECURSIVE，这才是正解。

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees 
  WHERE manager_id IS NULL  -- 根节点
  UNION ALL
  SELECT e.id, e.name, e.manager_id, ot.level + 1
  FROM employees e
  INNER JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY level;

关键点：

锚点（anchor）必须先选根，递归部分再找子；顺序反了会无限循环或无结果
MAXRECURSION（SQL Server）或 cte_max_recursion_depth（MySQL）要设合理上限，防止死循环拖垮数据库
PostgreSQL 默认无深度限制，但生产环境务必加 WHERE level 类似保护

容易被忽略的 NULL 和空字符串陷阱

层级字段常混着 NULL、0、'' 表示“无上级”，但不同数据库对 JOIN 中 NULL 的处理一致：不匹配。所以 LEFT JOIN 后，父级字段全是 NULL 是正常现象——但如果你用了 WHERE parent_id != NULL 这种写法，就错了，应该写 WHERE parent_id IS NOT NULL。