sql-self-joins

SQL：自连接

自连接（Self Join）是一种常规的连接操作，但它不是连接两个不同的表，而是将表与其自身连接。这种技术对于查询层次结构数据或查找同一表中行之间的关系非常强大。

要执行自连接，您必须使用表别名（alias），在查询中为同一个表指定两个不同的名称。这使得数据库可以将其视为两个独立的表，从而能够比较不同行中的列。

自连接的现代语法

自连接使用标准的 JOIN 语法。关键在于为表设置别名，并在两个别名之间定义逻辑连接条件。

现代最佳实践是使用显式的 JOIN ... ON 语法，而不是旧的、隐式的基于逗号的 WHERE 子句语法。这使得查询更具可读性，并且不易出错。

语法

SELECT
    a.column_name,
    b.column_name
FROM
    table_name AS a
JOIN
    table_name AS b ON a.common_column = b.related_column;

一个实际示例：员工和经理

自连接最常见的用例是查询员工表，其中一列存储员工的 ID，另一列存储其经理的 ID。由于经理也是一名员工，经理的 ID 指向同一表中的另一行。

让我们创建一个 Employees 表：

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50) NOT NULL,
    manager_id INT -- 顶级员工的经理ID可以为 NULL
);

INSERT INTO Employees (employee_id, employee_name, manager_id) VALUES
(1, 'Alice Smith (CEO)', NULL),
(2, 'Bob Johnson', 1),
(3, 'Charlie Brown', 1),
(4, 'Diana Prince', 2);

该表看起来像这样：

员工 ID	员工姓名	经理 ID
1	Alice Smith (CEO)	NULL
2	Bob Johnson	1
3	Charlie Brown	1
4	Diana Prince	2

为了获取每个员工及其经理姓名的列表，我们可以将 Employees 表与其自身连接。我们将使用别名 e 代表员工，m 代表经理。

SELECT
    e.employee_name AS "Employee Name",
    m.employee_name AS "Manager Name"
FROM
    Employees AS e
INNER JOIN
    Employees AS m ON e.manager_id = m.employee_id;

输出

此查询通过名称有效地将每个员工与其经理关联起来：

员工姓名	经理姓名
Bob Johnson	Alice Smith (CEO)
Charlie Brown	Alice Smith (CEO)
Diana Prince	Bob Johnson

使用 LEFT JOIN 进行自连接

请注意，首席执行官 Alice Smith 没有出现在上一个结果的“员工姓名”列中。这是因为 INNER JOIN 只包含满足连接条件的行，而她的 manager_id 是 NULL。为了包含所有员工，甚至那些没有经理的员工，我们可以使用 LEFT JOIN。

SELECT
    e.employee_name AS "Employee Name",
    COALESCE(m.employee_name, 'No Manager') AS "Manager Name"
FROM
    Employees AS e
LEFT JOIN
    Employees AS m ON e.manager_id = m.employee_id
ORDER BY
    e.employee_id;

注意：我们使用 COALESCE 函数在经理姓名是 NULL 时显示“无经理”，使输出更清晰。

输出

现在结果包含了所有员工：

员工姓名	经理姓名
Alice Smith (CEO)	No Manager
Bob Johnson	Alice Smith (CEO)
Charlie Brown	Alice Smith (CEO)
Diana Prince	Bob Johnson

最佳实践

• 始终使用别名： 别名对于自连接是强制性的。使用有意义的别名（例如，e 代表员工，m 代表经理）来提高可读性。
• 使用显式 JOIN 语法： 始终使用 JOIN ... ON 语法。它将连接逻辑与过滤逻辑（WHERE 子句）清晰地分离。
• 选择正确的 JOIN 类型： 如果您只想要存在关系的行，请使用 INNER JOIN。如果您想要关系一侧的所有行，无论另一侧是否存在匹配项，请使用 LEFT JOIN。