课程 5.8: 实用的 JOIN 场景和技术

到目前为止，我们已经探讨了不同连接类型的机制。在本课中，我们将超越基础，看看如何应用连接来解决常见的业务问题，处理多个表，并将连接与聚合结合起来。

1. 连接多个表 (3+)

在复杂的数据库中，您需要的数据通常分布在三个或更多通过连接表连接的表中。

场景： 我们想查看演员及其出演的电影标题的列表。 这需要三个表：actor、film_actor（桥接表）和 film。

SELECT
    a.first_name,
    a.last_name,
    f.title
FROM
    actor AS a
INNER JOIN
    film_actor AS fa ON a.actor_id = fa.actor_id
INNER JOIN
    film AS f ON fa.film_id = f.film_id
ORDER BY
    a.last_name
LIMIT 10;

工作原理：

• 每个 JOIN 创建一个新的虚拟表，供下一个 JOIN 使用。
• 连接的顺序通常遵循 ERD（实体关系图）中的关系路径。

2. 在 JOIN 中使用聚合函数

连接的一个最强大用途是计算相关表的统计信息。您可以在连接后使用 COUNT、SUM 和 AVG 等函数。

场景： 计算每位客户的总消费金额。

SELECT
    c.first_name,
    c.last_name,
    SUM(p.amount) AS total_spent
FROM
    customer AS c
INNER JOIN
    payment AS p ON c.customer_id = p.customer_id
GROUP BY
    c.customer_id, c.first_name, c.last_name
ORDER BY
    total_spent DESC;

注意：在使用 GROUP BY 和连接时，始终包括主键 (customer_id)，以确保如果两个客户有相同的名字，结果是唯一的。

3. 查找缺失数据（“反连接”）

我们可以使用 LEFT JOIN 结合 WHERE 子句来查找 没有 在另一个表中对应条目的记录。

场景： 查找当前不在我们库存中的所有电影（意味着我们有记录但没有实体副本）。

SELECT
    f.title
FROM
    film AS f
LEFT JOIN
    inventory AS i ON f.film_id = i.film_id
WHERE
    i.inventory_id IS NULL;

4. FILTER 陷阱：WHERE 与 ON

一个常见的错误是在使用 LEFT JOIN 时将过滤条件放在 WHERE 子句中，这会意外地将其变回 INNER JOIN。

错误：

-- 这会移除没有付款的客户，因为在连接后检查 p.payment_date
SELECT c.last_name, p.amount
FROM customer c
LEFT JOIN payment p ON c.customer_id = p.customer_id
WHERE p.payment_date > '2005-08-01';

正确（保留所有客户）：

-- 这保留所有客户，但仅连接与日期匹配的付款数据
SELECT c.last_name, p.amount
FROM customer c
LEFT JOIN payment p ON c.customer_id = p.customer_id 
    AND p.payment_date > '2005-08-01';

本课的关键要点

• 连接链： 通过添加更多的 JOIN 语句，您可以连接任意数量的表。
• 报告： 将 JOIN 与 GROUP BY 结合使用，可以在业务实体之间进行复杂的报告。
• 数据审计： 使用 LEFT JOIN ... WHERE ... IS NULL 查找数据中的空白。
• 逻辑精确性： 在处理外连接时，要小心放置过滤条件的位置（在 ON 与 WHERE 中）。