课程 5.7: 自连接 - 将表与自身连接

自连接并不是一个SQL关键字，而只是一个常用术语，用于描述将一个表与自身连接的情况。在实践中，这通常使用常规的JOIN实现，最常见的是INNER JOIN或LEFT JOIN，具体取决于所需的逻辑。这在查询层次数据或比较同一表中的行时非常有用。

什么是自连接？

要执行自连接，您必须将一个表视为两个独立的表。为此，您必须使用表别名为表的每个实例提供一个唯一的名称。如果没有别名，数据库将无法知道哪个列属于哪个表的实例。

可视化（员工层级）： 想象一个employee表，其中每一行都有一个指向其主管的manager_id，该主管的employee_id。

   表 A (员工)          表 B (经理)
   +----+-------+---------+     +----+-------+
   | id | name  | mgr_id  |     | id | name  |
   +----+-------+---------+     +----+-------+
   | 1  | Alice | NULL    |     | 1  | Alice |
   | 2  | Bob   | 1       | <-> | 1  | Alice | (Bob的经理是Alice)
   | 3  | Carol | 1       | <-> | 1  | Alice | (Carol的经理是Alice)
   +----+-------+---------+     +----+-------+

自连接语法

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

• employee AS e: 第一个实例（代表员工）。
• employee AS m: 第二个实例（代表经理）。
• ON e.manager_id = m.id: 将它们连接的条件。

实际示例（Sakila数据库）

1. 查找时长相同的影片

假设我们想找到时长完全相同的影片对。我们可以将film表与自身连接。

SELECT
    f1.title AS film_1,
    f2.title AS film_2,
    f1.length
FROM
    film AS f1
INNER JOIN
    film AS f2 ON f1.length = f2.length
WHERE
    f1.film_id <> f2.film_id -- 确保我们不将影片与自身匹配
LIMIT 10;

条件f1.film_id <> f2.film_id是关键。没有它，每部影片都会与自身匹配（因为它与自身的时长相同）。

2. 查找来自同一城市的客户

如果我们想查看哪些客户住在同一城市（基于这个简化示例中的address_id）：

SELECT
    c1.first_name AS cust_1_first,
    c1.last_name AS cust_1_last,
    c2.first_name AS cust_2_first,
    c2.last_name AS cust_2_last,
    c1.address_id
FROM
    customer AS c1
INNER JOIN
    customer AS c2 ON c1.address_id = c2.address_id
WHERE
    c1.customer_id < c2.customer_id; -- 使用'<'而不是'<>'以避免重复对（A-B和B-A）

本课的关键要点

• 自连接是将一个表与自身连接的术语，而不是一个单独的SQL关键字。
• 通常使用常规的JOIN类型，如INNER JOIN或LEFT JOIN来实现。
• 表别名是区分表的两个实例的必要条件。
• 使用ON条件来定义行之间的关系（例如，层级或共享属性）。
• 使用过滤条件，如id1 <> id2或id1 < id2，以避免将行与自身匹配或返回冗余对。在LEFT JOIN的情况下，这部分逻辑可能不仅放在WHERE中，也可以放在ON条件中。