SQL JOIN 实践场景

Q: 如何在 SQL 中连接两张以上的表？

沿着模式中的外键，为每张表添加一个 JOIN ... ON ...。每个连接都把一张新表连接到查询中已有的表上。

Q: 过滤条件应该写在 ON 还是 WHERE 中？

对于 INNER JOIN，没有区别。对于 LEFT JOIN，针对右表的条件写在 ON 中；写在 WHERE 中会删除没有匹配的行，把外连接变成内连接。

第 5.8 课 · 阅读时间：约 8 分钟 · 练习题 ↓

本课汇集了 SQL JOIN 的实践场景：沿着模式中的关系连接多张表、聚合连接后的数据、查找缺失的数据，以及避免两种最常见的连接错误：合计被放大，以及外连接丢失本应保留的行。

你已经了解了各种连接类型，现在的任务是把它们用于实际问题。大多数报表查询都遵循相同的步骤：找到包含所需数据的表，通过键把它们连接起来，然后过滤并聚合。

SQL 连接实践：在模式中从客户到付款、从电影到类别的连接路径，连接后的聚合，以及会放大合计的行倍增

规划连接路径

从问题出发，在数据库关系图中找到表之间的路径。“按电影类别统计收入”需要 category，然后是 film_category、inventory、rental，最后是 payment：

SELECT c.name AS category,
       COUNT(p.payment_id) AS payments,
       SUM(p.amount) AS revenue
FROM category AS c
INNER JOIN film_category AS fc ON fc.category_id = c.category_id
INNER JOIN inventory AS i ON i.film_id = fc.film_id
INNER JOIN rental AS r ON r.inventory_id = i.inventory_id
INNER JOIN payment AS p ON p.rental_id = r.rental_id
GROUP BY c.name
ORDER BY revenue DESC;

结果：按收入排序的 16 个类别；Sports 和 Sci-Fi 排在最前面。

这里并不需要 film 表本身：film_category 和 inventory 都存有 film_id，所以可以直接连接。只连接包含所需数据或者用于建立关联所必需的表。

连接后聚合

每位客户的总消费：

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

结果：消费最多的 10 位客户。

请按键（customer_id）分组，而不要只按姓名分组：两位不同的客户可能同名。

行倍增陷阱：合计被放大

连接会使行倍增：如果同时把付款表和租赁表连接到客户上，一位有 30 笔付款和 30 笔租赁的客户就会变成 900 行。对这种结果做的任何求和都是错误的。

-- 错误：付款被租赁次数成倍放大
SELECT c.customer_id,
       SUM(p.amount) AS total_spent
FROM customer AS c
INNER JOIN payment AS p ON p.customer_id = c.customer_id
INNER JOIN rental AS r ON r.customer_id = c.customer_id
WHERE c.customer_id = 1
GROUP BY c.customer_id;

结果：3797.76，而真实值是 118.68：该客户的 32 笔付款中的每一笔，都针对他的 32 笔租赁各计算了一次。

规则是：不要在一个查询中把两张相互独立的“多”方表连接到同一行。在这里根本不需要 rental 表。当你需要来自两张这样的表的合计时，请先分别聚合每张表；你将在第 6 模块中学习如何用子查询和 CTE 做到这一点。

查找缺失的数据

LEFT JOIN ... WHERE right_key IS NULL 用于查找没有匹配的行。查找自 2005 年 8 月 23 日以来没有租过任何东西的客户：

SELECT c.customer_id, c.first_name, c.last_name
FROM customer AS c
LEFT JOIN rental AS r ON r.customer_id = c.customer_id
                     AND r.rental_date >= '2005-08-23'
WHERE r.rental_id IS NULL
ORDER BY c.customer_id;

结果：在数据最后几天中没有租赁记录的客户。

注意日期条件：它写在 ON 中，因为它限制哪些租赁算作匹配。如果写在 WHERE 中，它会删除所有没有匹配的客户，查询将什么也不返回。

外连接中的 WHERE 与 ON

上一个例子展示了 LEFT JOIN 的通用规则：

条件作用于	写在哪里	效果
右表，用于限制匹配	`ON`	左表的行保留；只过滤匹配行
左表	`WHERE`	从结果中删除左表的行
右表的键 `IS NULL`	`WHERE`	只保留没有匹配的行（反连接）

对于 INNER JOIN，ON 和 WHERE 的结果相同，因此写在哪里只是可读性的问题。

要点：

在模式中规划连接路径，只连接需要的表。
按键分组，而不只是按名称分组。
把两张“多”方表连接到同一行会使行倍增，并放大求和与计数。
LEFT JOIN ... WHERE key IS NULL 用于查找缺失的数据。
在外连接中，针对右表的条件应写在 ON 中。

常见问题

如何在 SQL 中连接两张以上的表？

沿着模式中的外键，为每张表添加一个 JOIN ... ON ...。每个连接都把一张新表连接到查询中已有的表上。

为什么 JOIN 之后 SUM 返回的数字大得多？

因为连接使行倍增了：一张表的每一行都针对另一张表中每个匹配行重复了一次。去掉不需要的表，或者先分别聚合每张表，再连接结果。

过滤条件应该写在 ON 还是 WHERE 中？

对于 INNER JOIN，没有区别。对于 LEFT JOIN，针对右表的条件写在 ON 中；写在 WHERE 中会删除没有匹配的行，把外连接变成内连接。