11.7 自连接：特殊但有用的查询

      在所有的连接类型中，有一种特殊的连接——自连接（Self Join）。它不是连接两张不同的表，而是将一张表与它自身进行连接，就像两张独立的表一样。自连接在处理表中行与行之间的内部关系时非常有用，例如组织结构、层级关系、比较同一表中的数据等。

一、什么是自连接？

自连接是指在同一张表上执行连接操作。由于是同一个表，我们需要为表指定不同的别名，以区分“左表”和“右表”。从逻辑上讲，可以看作是这张表的两个副本在进行连接。

自连接本身并不是一种独立的连接类型（可以是内连接、左连接等），而是指连接的对象是自己。

二、基本语法

SELECT 列名
FROM 表名 AS 别名1
JOIN 表名 AS 别名2 ON 别名1.列 = 别名2.列;

这里的 JOIN 可以是 INNER JOIN、LEFT JOIN 等，根据需求选择。关键是必须使用别名，否则无法区分两个实例。

三、经典示例：员工与经理

最经典的自连接场景是员工表，其中一列指向该员工的经理（经理本身也是员工）。假设我们有一张 employees 表：

• emp_id：员工ID

• name：员工姓名

• manager_id：该员工的经理ID，指向 emp_id，如果为 NULL 表示没有上级（CEO）。

需求：查询每个员工的姓名以及他的经理姓名。

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

结果：

这里我们使用了左连接，因为员工可能没有经理（manager_id 为 NULL），但我们仍希望显示该员工。如果使用内连接，会丢失 CEO 的记录。

四、其他自连接场景

1. 查找同一表中可比较的行

假设有一张 products 表，包含产品价格。我们要找出价格相同的不同产品（即找出“竞争产品”）：

SELECT    p1.name AS product1,    p2.name AS product2,    p1.price FROM products p1 JOIN products p2 ON p1.price = p2.price AND p1.product_id < p2.product_id;

条件 p1.product_id < p2.product_id 避免了重复配对（如手机A-手机B 和 手机B-手机A 只出现一次），也排除自身比较。

结果：

2. 查找层级关系（如评论回复）

假设有一个 comments 表，支持嵌套回复：

查询每条评论及其父评论的内容：

SELECT 
    c.comment_id,
    c.content AS comment,
    p.content AS parent_comment
FROM comments c
LEFT JOIN comments p ON c.parent_id = p.comment_id;

3. 查找连续数据或相邻行

自连接也可用于处理有序数据，例如找出连续登录的用户或相邻的日期记录。

五、自连接的注意事项

1. 必须使用别名：这是自连接的基本要求，否则 SQL 无法区分两个表的实例。

2. 注意连接条件：务必确保条件正确，否则可能产生笛卡尔积，导致结果爆炸。

3. 选择合适的连接类型：根据业务需求选择内连接、左连接等。

4. 索引优化：为连接列（如 manager_id）建立索引可以极大提升自连接查询的性能。

六、练习

1. 基于上面的 employees 表，查询所有经理以及他们直接管理的员工人数。

2. 在 products 表中，找出价格高于另一指定产品（例如“手机A”）的所有其他产品。

答案提示：

• 对于问题1，可以用 GROUP BY 对经理分组计数，但需要自连接先关联员工与经理，然后按经理分组。

• 对于问题2，可以用自连接比较价格。

七、小结

• 自连接 是同一张表与自身连接，通过别名区分两个角色。

• 常用场景包括层级关系（如员工-经理）、比较同一表中的行、查找重复或相关记录。

• 自连接可以是内连接、左连接等，取决于业务需求。

• 正确使用别名和连接条件是关键，同时注意性能优化。

自连接虽然特殊，但在处理表内关系时不可或缺。掌握它，你就又添了一件处理复杂查询的利器。