MySQL / 系统课时

自连接查询

自连接查询

自连接用于把一张表当成两个或多个逻辑角色参与连接。掌握表别名后,可以直接查询员工与直属主管、分类与父分类等同表关系,而不需要为了层级关系复制出第二张表。

学习目标

  • 理解自连接本质上仍是普通表连接,只是连接双方来自同一张表
  • 能使用表别名区分当前记录与关联记录
  • 能处理没有父级记录的顶层节点

自连接为什么需要别名

假设员工表同时保存员工信息和直属主管编号,manager_id 指向同一张表中的 id。查询员工姓名时需要读取当前员工这一行;查询主管姓名时,又需要根据 manager_id 再读取同表中的另一行。

因此 SQL 必须为同一张表建立两个逻辑身份,例如 e 表示 employee,m 表示 manager。没有别名时,name、id 等列无法明确属于连接中的哪一个角色。

员工表自连接关系

查询员工与直属主管

下面先建立最小数据集。顶层负责人没有直属主管,因此 manager_id 使用 NULL。

sql
CREATE TEMPORARY TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    manager_id INT NULL
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, '陈总', NULL),
    (2, '李经理', 1),
    (3, '王工程师', 2),
    (4, '赵工程师', 2);

SELECT
    e.name AS employee_name,
    m.name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.id
ORDER BY e.id;

预期结果:

text
+---------------+--------------+
| employee_name | manager_name |
+---------------+--------------+
| 陈总          | NULL         |
| 李经理        | 陈总         |
| 王工程师      | 李经理       |
| 赵工程师      | 李经理       |
+---------------+--------------+

这里使用 LEFT JOIN 而不是 INNER JOIN,是为了保留 manager_id 为 NULL 的顶层员工。如果改成内连接,陈总这一行会因为找不到主管而被过滤掉。

把自连接规则读成两张逻辑表

阅读自连接 SQL 时,不要反复想着“这是同一张表”,而应暂时把两个别名当成两张逻辑表。e 负责提供当前员工,m 负责提供被引用的主管,连接条件只负责描述两者的关系。

这种读法同样适用于分类树。若分类表包含 id 与 parent_id,那么可以让 c 表示当前分类、p 表示父分类,并使用 c.parent_id = p.id 建立关联。

自连接的边界

自连接非常适合查询固定的一层关系,例如“员工与直属主管”或“分类与直接父分类”。如果要查询任意深度的完整组织树,仅不断增加 JOIN 会把层级写死,而且 SQL 会迅速变长。

还要注意连接字段的索引。示例中的主管目标列 id 是主键,查找父记录成本较低;实际表中若自连接使用普通业务字段,应确认该字段是否需要索引。自连接不是特殊 SQL,它仍然遵循普通连接的匹配、索引和 NULL 规则。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

自连接查询

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通