多表查询与 JOIN

本章目标：理解主键、外键和三种表关系，掌握六种 JOIN，能识别并避免一对多关联造成的金额膨胀。 前置知识：SQL 基础、聚合与分组。 预计用时：60 分钟。

1. 为什么需要它

品类在 demo_products，金额在 demo_order_items，客户城市在 demo_customers。业务问题很少能在一张表里答完，必须把多张表按共同字段拼起来，这就是 JOIN。

JOIN 是 SQL 面试的分水岭。会写 JOIN 只是入门，能说清楚"关联后行数怎么变、金额会不会翻倍"，才是数据开发。真实事故里最常见的一类 bug 是：两张一对多的表直接关联再 SUM，报表金额直接翻几倍。

本章继续用玩具库 project/sql_demo.duckdb 的九张 demo_* 小表。

2. 核心概念

2.1 主键、外键与三种关系

• 主键（primary key）：唯一标识一行，不能重复、不能为空。例如 demo_customers.customer_id。
• 外键（foreign key）：指向另一张表的主键，用来建立关联。例如 demo_orders.customer_id。
• 复合主键：由多列共同唯一标识一行。demo_order_items 的业务主键是 (order_id, item_id)。

这份数据的核心关系：

demo_customers 1--N demo_orders 1--N demo_order_items N--1 demo_products

                         |
                         +--N demo_payments
                         +--N demo_reviews

demo_departments 1--N demo_employees
demo_employees 1--N demo_employees（manager_id 自关联）

2.2 六种 JOIN

INNER JOIN：两边都匹配上的行才保留。

SELECT o.order_id, i.product_id, i.unit_price
FROM demo_orders o
JOIN demo_order_items i ON o.order_id = i.order_id
LIMIT 10;

JOIN 默认就是 INNER JOIN。

LEFT JOIN：左表全部保留，右表匹配不上补 NULL。

SELECT o.order_id, i.product_id
FROM demo_orders o
LEFT JOIN demo_order_items i ON o.order_id = i.order_id
ORDER BY o.order_id
LIMIT 10;

RIGHT JOIN 与 LEFT 对称，右表全保留；FULL OUTER JOIN 两边都保留，写法是 FROM demo_customers c FULL OUTER JOIN demo_orders o ON ...。MySQL 不支持 FULL，需要用 LEFT UNION RIGHT 模拟。

CROSS JOIN：笛卡尔积，左表每行和右表每行都组合一次。6 个商品乘 3 个品类等于 18 行。

SELECT COUNT(*) AS 组合数
FROM demo_products
CROSS JOIN (SELECT DISTINCT category FROM demo_products) t;

自连接：同一张表起两个别名互相关联。用来比较同表的两行，比如找同城的客户对：

SELECT a.city, a.customer_id, b.customer_id
FROM demo_customers a
JOIN demo_customers b ON a.city = b.city AND a.customer_id < b.customer_id
ORDER BY a.city, a.customer_id, b.customer_id;

结果 4 对：北京 3 对、上海 1 对。

反连接：LEFT JOIN 之后只保留右表主键为 NULL 的行，用来找"没有关联记录"的数据。

SELECT c.customer_id, c.customer_name
FROM demo_customers c
LEFT JOIN demo_orders o
       ON o.customer_id = c.customer_id AND o.status = 'paid'
WHERE o.order_id IS NULL;

结果只有客户 7，他从没有下过已支付订单。

选择原则：只要匹配上的用 INNER；要保留左表全量用 LEFT；找"缺失"用 LEFT + WHERE 右表.id IS NULL。

2.3 JOIN 最大的坑：fan-out 膨胀

demo_orders 与 demo_order_items 是 1:N：

SELECT COUNT(*) AS 行数, COUNT(DISTINCT o.order_id) AS 订单数
FROM demo_orders o
JOIN demo_order_items i ON o.order_id = i.order_id;

结果是 15 行、12 个订单。JOIN 之后的行粒度变成了明细，不再是订单。

如果此时再关联 demo_payments 并求支付金额：

SELECT COUNT(*) AS 行数,
       COUNT(DISTINCT o.order_id) AS 订单数,
       ROUND(SUM(p.amount), 2) AS 支付金额
FROM demo_orders o
JOIN demo_order_items i ON o.order_id = i.order_id
JOIN demo_payments p ON o.order_id = p.order_id;

结果：17 行、12 个订单、支付金额 5,623.60。而 demo_payments 的真实合计是 5,007.10。一笔支付被订单的每条明细各算了一次，金额被放大了 616.50。

避免方法：

1. 先想清楚结果要什么粒度。要订单级指标，就先聚合到 order_id 再 JOIN。
2. 用 COUNT(DISTINCT 主键) 代替 COUNT(*) 数实体。
3. 不要在一条 SQL 里同时关联两个 1:N 表再求和；先分别聚合，再按同一粒度 JOIN。
4. 上线前对比 JOIN 前后的行数和主键去重数。

2.4 ON 与 WHERE，以及 NULL 匹配

对于 INNER JOIN，条件写在 ON 还是 WHERE 结果通常一样。但对 LEFT JOIN，两者完全不同：

-- 条件在 ON：12 个订单都保留，只有高价明细被匹配
SELECT COUNT(DISTINCT o.order_id)
FROM demo_orders o
LEFT JOIN demo_order_items i
       ON o.order_id = i.order_id AND i.unit_price > 1000;

-- 条件在 WHERE：没匹配到高价明细的订单被筛掉，LEFT 退化成 INNER
SELECT COUNT(DISTINCT o.order_id)
FROM demo_orders o
LEFT JOIN demo_order_items i ON o.order_id = i.order_id
WHERE i.unit_price > 1000;

结果分别是 12 和 2。右表的过滤条件想保留左表全量，就必须写在 ON 里。

NULL 不参与等值匹配：NULL = NULL 的结果是未知。用客户推荐关系可以直观看到：

-- INNER：只有有推荐人的客户能匹配上，4 行
SELECT COUNT(*)
FROM demo_customers c
JOIN demo_customers r ON c.referrer_id = r.customer_id;

-- LEFT：7 位客户都保留，其中 3 位的推荐人为空
SELECT COUNT(*)
FROM demo_customers c
LEFT JOIN demo_customers r ON c.referrer_id = r.customer_id
WHERE r.customer_id IS NULL;

写关联前先确认关联键是否可能为空。

2.5 用 CTE 组织多表查询

WITH paid_orders AS (
    SELECT order_id, customer_id, created_at
    FROM demo_orders
    WHERE status = 'paid'
),
detail AS (
    SELECT o.order_id, o.customer_id, o.created_at,
           i.product_id, i.quantity, i.unit_price
    FROM paid_orders o
    JOIN demo_order_items i ON i.order_id = o.order_id
)
SELECT * FROM detail LIMIT 5;

CTE（WITH）把一段查询命名，后面的步骤直接引用。好处是可读、可复用、可逐层调试。先把每张表清洗到同一粒度，再 JOIN，是避免膨胀最有效的习惯。

3. 动手示例

3.1 有效明细的口径

玩具库里把 status = 'paid' 视为有效订单。先确认粒度：

SELECT COUNT(*) AS 明细行数,
       COUNT(DISTINCT o.order_id) AS 订单数
FROM demo_orders o
JOIN demo_order_items i ON i.order_id = o.order_id
WHERE o.status = 'paid';

13 行明细、10 个订单。一个订单可以有多行明细，所以两个数字不同。

3.2 品类销售额

demo_products 对明细是 1:1（一个商品一行），不会膨胀，可以放心 JOIN 后 SUM：

SELECT p.category AS 品类,
       ROUND(SUM(i.quantity * i.unit_price), 2) AS 销售额
FROM demo_order_items i
JOIN demo_orders o ON o.order_id = i.order_id
JOIN demo_products p ON p.product_id = i.product_id
WHERE o.status = 'paid'
GROUP BY p.category
ORDER BY 销售额 DESC;

3.3 客户城市销售额

SELECT c.city AS 城市,
       ROUND(SUM(i.quantity * i.unit_price), 2) AS 销售额
FROM demo_orders o
JOIN demo_order_items i ON i.order_id = o.order_id
JOIN demo_customers c ON c.customer_id = o.customer_id
WHERE o.status = 'paid'
GROUP BY c.city
ORDER BY 销售额 DESC;

结果：北京 2,345.40、深圳 1,299.00、上海 685.80、广州 89.00。

3.4 找没有下单的客户

SELECT c.customer_id, c.customer_name
FROM demo_customers c
LEFT JOIN demo_orders o
       ON o.customer_id = c.customer_id AND o.status = 'paid'
WHERE o.order_id IS NULL;

只有客户 7。这个套路就是 LEFT JOIN + 右表主键 IS NULL，做数据质量监控时天天用。注意过滤条件 o.status = 'paid' 写在 ON 里，否则会把有取消单的客户也误判成"没下过单"。

3.5 修复 fan-out

不要在一对多关联后直接求支付金额。先把支付和明细分别聚合到订单粒度，再关联：

WITH pay AS (
    SELECT order_id, SUM(amount) AS pay_amount
    FROM demo_payments
    GROUP BY order_id
),
item AS (
    SELECT order_id, SUM(quantity * unit_price) AS item_amount
    FROM demo_order_items
    GROUP BY order_id
)
SELECT ROUND(SUM(p.pay_amount), 2) AS 支付金额,
       ROUND(SUM(i.item_amount), 2) AS 明细金额
FROM pay p
LEFT JOIN item i ON p.order_id = i.order_id;

两边都是"一个订单一行"，JOIN 后仍然是订单粒度。支付金额 5,007.10，不再被放大。

明细金额是 5,007.20，相差 0.10，来自订单 1002 的支付尾差。这是口径差异，不是 JOIN 写错。

4. 常见坑

1. 忘记写 ON 条件。会变成笛卡尔积，行数瞬间爆炸。
2. 关联键选错。用 order_id 关联商品、用 item_id 关联订单，都会得到错误结果。
3. 一对多 JOIN 后直接 SUM。金额按右表行数重复计算，先聚合到同一粒度。
4. LEFT JOIN 后把右表条件写在 WHERE。没匹配上的行被过滤，LEFT 退化为 INNER。
5. 以为 NULL 能关联 NULL。关联键为空时永远匹配不上，需要单独处理。
6. 多表 JOIN 顺序随意。驱动表、过滤条件和数据量都会影响性能；先过滤再关联通常更好。
7. 列名歧义。两张表都有 order_id，必须写 o.order_id 或 i.order_id。

5. 在线练习

自测：

• 能说出 INNER JOIN、LEFT JOIN、反连接各自保留哪些行。
• 能解释一对多 JOIN 导致的行数膨胀（fan-out）。
• 能在 JOIN 前先聚合，避免指标重复计算。

6. 面试延伸

面试题：LEFT JOIN 和 INNER JOIN 有什么区别？

是什么：INNER 只保留两边都匹配的行；LEFT 保留左表全部行，右表没有匹配时补 NULL。RIGHT 对称，FULL 两边都保留。

解决什么问题：要全量左表用 LEFT；只算发生过的关联用 INNER；找缺失数据用 LEFT + 右表主键 IS NULL。

我的实操边界：写 LEFT 后会检查右表过滤条件是否放错到 WHERE，并用行数验证有没有退化成 INNER。

面试题：什么是 fan-out，怎么发现和避免？

是什么：一对多 JOIN 后行数变多，同一事实被重复计算。比如订单连明细，一笔支付会被每条明细各算一次。

解决什么问题：不处理会让销售额、支付金额成倍偏大。发现方法是比较 JOIN 前后的行数和主键去重数；避免方法是先聚合到同一粒度，或用 COUNT(DISTINCT) 数实体。

我的实操边界：复杂查询先分别聚合各表，再按 order_id 关联；上线前做行数对比和金额对账。

面试题：什么是驱动表，JOIN 顺序怎么定？

是什么：驱动表是先被读取、用来逐行探测另一张表的表。小表驱动大表通常更划算；Hive / Spark 里还会把小表广播出去做 map-side join。

解决什么问题：JOIN 顺序和过滤条件会影响扫描量、shuffle 量和执行时间。先把数据过滤小再关联，通常更快。

我的实操边界：了解原理，未在生产集群做过系统的执行计划调优；目前会先过滤、少 SELECT 列，并用 EXPLAIN 观察 DuckDB 的本地计划。

7. 本章验收

• 能说出玩具库里的 1:N、N:1、N:N 关系，以及 demo_order_items 的复合主键。
• 能写出 INNER、LEFT、FULL、CROSS 和自连接的基本语法。
• 能说清 ON 与 WHERE 在 LEFT JOIN 中的区别，并知道 NULL 为什么关联不上。
• 能用 LEFT JOIN + IS NULL 找出没有下单的客户。
• 能解释 fan-out 的成因，指出 5,623.60 为什么是错的，并写出修复思路。
• 能独立算出 13 行有效明细、10 个有效订单，以及品类和城市销售额。
• 5 道在线练习全部提交通过。

下一章

高级查询：子查询、CASE WHEN、CTE、日期函数、行列转换和集合运算，把复杂业务拆成可维护的 SQL。