单表查询和聚合能回答的问题,到“每个渠道卖了多少”就结束了。再往下问一层——“北京的金卡用户买什么品类”——
订单表里没有会员等级,商品表里没有订单号,答案散在三张表里。JOIN 就是按某个键把这些表拼起来。
但 JOIN 也是 SQL 里最容易把数字算错的地方。两张表一拼,行数可能从 20,000 变成 39,765;
再 SUM 一次订单金额,GMV 会从 1.57 亿变成 3.66 亿。本章的核心不是语法,是粒度:
拼之前先预测行数,拼之后先核对行数。13.3 的实验里,你会亲手制造一次 2.32 倍的错误。
JOIN 的语法只有几行,难的是判断“拼完之后一行代表什么”。这一节先认键,再认五种 JOIN, 最后把最容易翻车的粒度陷阱拆开。
关系数据库把数据拆成多张表,每张表只负责一件事:用户、商品、订单、商品行。表与表之间靠键建立联系。
主键是表中唯一标识一行的字段。用户表的主键是 user_id,订单表的主键是
order_id,商品表的主键是 product_id。主键必须唯一、不能为 NULL。
外键是另一张表的主键在这个表里的“引用”。orders.user_id 指向
users.user_id,order_items.order_id 指向 orders.order_id。
外键带来三种关系。用户 → 订单是一对多:一个用户可以下多笔订单,一笔订单只属于一个用户。
订单 → 商品行也是一对多:一笔订单可以有三行商品。商品 ↔ 订单是多对多:
一个商品出现在很多订单里,一笔订单也买很多商品,中间必须有 order_items 搭桥。
| 表 | 主键 | 外键 | 一行代表什么 | 行数 |
|---|---|---|---|---|
users | user_id | — | 一个注册用户 | 2,000 |
products | product_id | — | 一个 SKU | 120 |
orders | order_id | user_id → users | 一笔订单 | 20,000 |
order_items | item_id | order_id → orders;product_id → products | 一笔订单里的一个商品行 | 39,765 |
events | event_id | user_id → users | 一次用户行为 | 16,932 |
每张表都有一个粒度:用户表一行是一个人,订单表一行是一笔交易,商品行表一行是一个商品行。 JOIN 之前,你必须知道左右两边各自一行是什么,否则拼出来的行你根本解释不了。
如果不这样会怎样:把 order_items 当成订单表,一句
SELECT COUNT(*) FROM order_items 得到 39,765,你会报告“本月有 39,765 笔订单”,
真实只有 20,000 笔,虚高 98.8%。多出来的 19,765 行全是同一笔订单里的第二、第三个商品行。
JOIN 的类型决定了“匹配不上的行怎么办”。INNER 取交集,LEFT 保留左表全部, RIGHT 保留右表全部,FULL 取并集,CROSS 取笛卡尔积。但只看集合图不够,你还得知道行数会变成多少。
| 写法 | 保留什么 | 本项目实测 | 典型用途 |
|---|---|---|---|
orders INNER JOIN users | 两边都匹配上的行 | 19,435 | 只要有关联用户的数据 |
orders LEFT JOIN users | 左表订单全保留,匹配不上补 NULL | 20,000 | 补维度、保留游客订单 |
users LEFT JOIN orders | 左表用户全保留,一个用户对多笔订单 | 19,435 | 统计每个用户的订单 |
users RIGHT JOIN orders | 右表订单全保留 | 20,000 | 少用,可改写成 LEFT |
users FULL JOIN orders | 两边都保留 | 20,000 | 对账,找双边差异 |
users CROSS JOIN products | 笛卡尔积,每行配每行 | 240,000 | 生成组合骨架、日期维表 |
注意第三行:users LEFT JOIN orders 的结果是 19,435,不是 2,000。
一个用户对多笔订单,左表的 2,000 行每一行都被展开成了约 10 行。
LEFT JOIN 里左表就是驱动表,它保证的是“左表每一行至少出现一次”,不是“左表行数不变”。
只有右表的连接键唯一(N:1)时,LEFT JOIN 才不改变行数。
只写 JOIN 等于 INNER JOIN,匹配不上的行会静默消失。
想保留左表全部,写 LEFT JOIN;想保证行数不变,还要加一条:右表的连接键必须唯一。
如果不这样会怎样:用 INNER JOIN 给订单补会员等级,565 笔游客订单会直接消失;
其中 439 笔是已支付订单,GMV 合计 4,274,401.92,占 2.7%。
你的月报会凭空少掉 427 万,而且不会报任何错。
RIGHT JOIN 和 FULL JOIN,本实验环境是 3.45.1。
更老的版本会报语法错误,用 LEFT JOIN 调换左右表即可等价改写。
ON 回答的是“两张表怎么配对”,WHERE 回答的是“配完之后留下哪些行”。
对 INNER JOIN 来说,这两个位置常常可以互换,结果一样;但对 LEFT JOIN,它们完全不是一回事。
假设你想“保留所有订单,同时看看哪些订单来自北京用户”。条件 u.city = '北京'
应该放在 ON 里:匹配时只认北京用户,匹配不上的订单仍然保留,只是用户字段为 NULL。
如果放到 WHERE 里,数据库会先补全所有订单,再筛掉“用户城市不是北京”的行,包括匹配不上的游客订单。
LEFT JOIN 的结果被 WHERE 一过滤,就退化成 INNER JOIN 了。
| 写法 | 行数 | 含义 |
|---|---|---|
LEFT JOIN ... ON u.city = '北京' | 20,000 | 只匹配北京用户,其他订单保留,用户字段为 NULL |
LEFT JOIN ... WHERE u.city = '北京' | 2,582 | 补全后再过滤,NULL 行被删掉,等价于 INNER JOIN |
INNER JOIN ... WHERE u.city = '北京' | 2,582 | 只留匹配上的北京用户订单 |
判断方法:这个条件是在描述“怎么匹配”,还是在描述“最终要哪些行”? 描述匹配的放 ON,描述最终结果的放 WHERE。LEFT JOIN 的右表条件,绝大多数属于前者。
如果不这样会怎样:你想保留 20,000 笔订单,只在能匹配时标出北京用户,
结果把 u.city = '北京' 写进了 WHERE,只剩 2,582 行,87.1% 的订单凭空消失。
更麻烦的是,SQL 不报错,结果看起来还挺合理。
这是全章最重要的一条,也是 GMV 翻倍的头号原因。
订单表一行是一笔订单,金额字段 pay_amount 是整笔订单的实付;
商品行表一行是一条商品行记录,一笔订单有几条就有几行。两张表按 order_id 一拼,
订单的金额会被复制到每一行商品上。
看一笔真实的订单 O23010104230:原价 23,167.23,优惠 200.67,实付 22,966.56,
里面有三个商品行(服饰 12,900.63、手机 7,459.96、手机 2,806.64)。JOIN 之后,这笔订单变成三行,
每行的 pay_amount 都是 22,966.56。你把这列一加,得到 68,899.68,正好是真实值的 3 倍。
| 订单号 | 商品 | 品类 | 行金额 | 订单实付 | |
|---|---|---|---|---|---|
| 1 | O23010104230 | P0120 | 服饰 | 12,900.63 | 22,966.56 |
| 2 | O23010104230 | P0023 | 手机 | 7,459.96 | 22,966.56 |
| 3 | O23010104230 | P0010 | 手机 | 2,806.64 | 22,966.56 |
| SUM(行金额) = 23,167.23(正确) | SUM(订单实付) = | 68,899.68(3 倍) | |||
放到全量数据上,错误被放大到 2.32 倍:已支付订单 15,615 笔,JOIN 商品行后变成 31,071 行,
SUM(o.pay_amount) 得到 365,790,418.68,而真实 GMV 是 157,363,380.14,
多算了 208,427,038.54 元。这不是小数误差,是 2 亿。
订单是“一”,商品行是“多”。JOIN 之后,订单字段被复制到每一行商品上。
要算金额,只能 SUM 商品行自己的 i.amount;要算订单数,只能
COUNT(DISTINCT o.order_id)。直接 SUM(o.pay_amount) 或
COUNT(*),都会按商品行数放大。
如果不这样会怎样:月报写“GMV 3.66 亿”,财务对账只有 1.57 亿,差 2.08 亿。 你会花一整天排查 ETL、汇率、退款,最后发现只是 SQL 里多了一个 JOIN。
商品和订单是多对多:一个商品可以出现在很多订单里,一笔订单也可以买多个商品。
关系数据库不能直接在两张表之间画多对多,必须加一张中间表,把多对多拆成两个一对多。
在青柠优选里,这张中间表就是 order_items。
它不是一张“多余的关联表”,本身就是事实表:qty 记录买了几件,
unit_price 记录成交单价,amount 记录这一行的金额。
120 个商品、20,000 笔订单,靠 39,765 行商品行连接;每个商品平均出现在约 330 笔订单里,
每笔订单有 1 到 3 行商品。
看到“商品”和“订单”直接相连的需求,先找中间表。中间表既是关系的桥,也是度量的载体。 没有它,两张表只能做笛卡尔积,结果没有任何业务含义。
如果不这样会怎样:products JOIN orders ON 1=1 会生成
120 × 20,000 = 2,400,000 行,每个商品都被配上了全部 20,000 笔订单。
你按商品分组算“订单数”,每个商品都是 20,000 单,看起来整齐得可疑。
有经验的分析师写 JOIN 之前,会先在纸上写下“预测行数”。执行完再对一次,不等就说明关系判断错了。 这个动作叫行数沙盘。
沙盘只要回答三个问题:左表一行是什么?右表的连接键唯一吗?匹配不上的行要不要保留? 三个答案组合起来,行数只有四种可能:
| 关系 | 行数公式 | 例子 | 结果 |
|---|---|---|---|
| 1:1 | 等于左表行数 | 订单 ↔ 订单扩展信息 | 不变 |
| N:1 | 等于左表行数(右表键唯一) | 订单 → 用户、商品行 → 商品 | 不变 |
| 1:N | 左表行数 × 平均子行数 | 订单 → 商品行、用户 → 订单 | 变多 |
| M:N | 等于匹配对总数,可能爆炸 | 商品 ↔ 订单(必须有中间表) | 不可直接预测 |
把本章的 JOIN 写进沙盘,预测和实测并排。
| 连接 | 关系 | 预测行数 | 实测行数 |
|---|---|---|---|
orders LEFT JOIN users | N:1,右表键唯一 | 20,000 | 20,000 |
orders INNER JOIN users | N:1,565 行不匹配 | 19,435 | 19,435 |
users LEFT JOIN orders | 1:N,平均 9.7 单/人 | 19,435 | 19,435 |
orders JOIN order_items | 1:N,平均 1.99 行/单 | 39,765 | 39,765 |
orders JOIN items JOIN products | N:1,商品键唯一 | 39,765 | 39,765 |
users CROSS JOIN products | M:N,无连接条件 | 240,000 | 240,000 |
沙盘里最容易被忽略的是第三行:users LEFT JOIN orders。左表只有 2,000 个用户,
结果却是 19,435 行。LEFT JOIN 不等于“左表行数不变”,它只保证左表每一行至少出现一次;
右表一对多时,左表的行会被展开。
要算“每个用户的订单数”,不要先 JOIN 再 COUNT(*):先把订单按 user_id 聚合,
再让用户表 LEFT JOIN 这个结果。这就是“先聚合,再 JOIN”,行数在 JOIN 前就固定了。
COUNT(*) 验过吗?
键有重复,N:1 就变成 N:N,行数成倍增长。
打开 qingning.db,按顺序执行。每一步先写下预测行数,再运行核对。
预测错了不要紧,弄清楚为什么错更有价值。
SELECT 'users' AS 表名, COUNT(*) AS 行数 FROM users UNION ALL SELECT 'products', COUNT(*) FROM products UNION ALL SELECT 'orders', COUNT(*) FROM orders UNION ALL SELECT 'order_items', COUNT(*) FROM order_items UNION ALL SELECT 'events', COUNT(*) FROM events;
SELECT COUNT(*) AS 订单行数,
COUNT(DISTINCT order_id) AS 唯一订单号
FROM orders;
表名 行数 users 2000 products 120 orders 20000 order_items 39765 events 16932 订单行数 唯一订单号 20000 20000
UNION ALL 要求每列的列名以第一个 SELECT 为准;如果表名写错,SQLite 会报 “no such table”。
SELECT COUNT(*) AS 行数,
COUNT(u.user_id) AS 匹配上的行,
COUNT(*) - COUNT(u.user_id) AS 游客订单
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id;
SELECT o.order_id,
o.city AS 收货城市,
u.city AS 注册城市,
u.member_level,
o.channel AS 下单渠道,
u.channel AS 注册渠道
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
LIMIT 5;
行数 匹配上的行 游客订单 20000 19435 565 order_id 收货城市 注册城市 会员等级 下单渠道 注册渠道 O23101400001 上海 武汉 普通 信息流 自然搜索 O24032900002 西安 西安 普通 自然搜索 信息流 O23070100003 武汉 广州 普通 自然搜索 自然搜索 O23030400004 武汉 北京 银卡 线下 自然搜索 O23092800005 成都 上海 银卡 线下 信息流
行数变成 19,435,565 笔游客订单消失;其中 439 笔已支付,GMV 4,274,401.92,占 2.7%。
SELECT COUNT(*) AS 行数,
COUNT(DISTINCT o.order_id) AS 订单数
FROM orders o
JOIN order_items i ON o.order_id = i.order_id;
SELECT 商品行数, COUNT(*) AS 订单数 FROM ( SELECT order_id, COUNT(*) AS 商品行数 FROM order_items GROUP BY order_id ) AS t GROUP BY 商品行数 ORDER BY 商品行数;
行数 订单数 39765 20000 商品行数 订单数 1 6789 2 6657 3 6554
如果行数也是 20,000,说明你连错了键,可能用了 o.user_id = i.order_id 之类的条件;检查 ON 两边是不是同一业务含义。
SELECT COUNT(*) AS 行数,
ROUND(SUM(o.pay_amount),2) AS 错误GMV
FROM orders o
JOIN order_items i ON o.order_id = i.order_id
WHERE o.status = '已支付';
SELECT o.order_id,
o.pay_amount,
COUNT(*) AS 商品行数,
ROUND(o.pay_amount * COUNT(*),2) AS 重复后的金额
FROM orders o
JOIN order_items i ON o.order_id = i.order_id
WHERE o.order_id = 'O23010104230'
GROUP BY o.order_id;
行数 错误GMV 31071 365790418.68 order_id pay_amount 商品行数 重复后的金额 O23010104230 22966.56 3 68899.68
不是每笔订单都有 3 行商品。全部订单平均 1.99 行;已支付订单按金额加权的平均行数是 2.3245:3 行订单平均实付 15,140.83,远高于 1 行的 5,042.43,权重更大。单笔 3 行仍放大 3 倍。
i.amount 一行只出现一次,不会被复制。规则 5:品类在商品表,订单状态在订单表,必须通过中间表把它们连起来。SELECT p.category AS 品类,
COUNT(*) AS 商品行数,
ROUND(SUM(i.amount),2) AS 品类金额
FROM order_items i
JOIN products p ON i.product_id = p.product_id
JOIN orders o ON i.order_id = o.order_id
WHERE o.status = '已支付'
GROUP BY p.category
ORDER BY 品类金额 DESC;
品类 商品行数 品类金额 手机 7785 47570925.64 电脑 7761 33756338.97 家电 5192 25939444.18 配件 5202 25503426.77 服饰 5131 25105176.08
因为这里加的是商品行原价 i.amount,还没有扣优惠。第 6 步会对账,差额正好是全站优惠 511,931.50。
SELECT ROUND(SUM(amount),2) AS 订单原价,
ROUND(SUM(discount),2) AS 优惠,
ROUND(SUM(pay_amount),2) AS 实付
FROM orders
WHERE status = '已支付';
订单原价 优惠 实付 157875311.64 511931.50 157363380.14
需要把每笔订单的优惠按商品行金额比例分摊到行上,再按品类汇总。这要用到子查询或窗口函数,第 14、15 章会讲。
IS NULL 一筛,就是“缺失清单”。SELECT COUNT(*) AS 游客订单 FROM orders o LEFT JOIN users u ON o.user_id = u.user_id WHERE u.user_id IS NULL;
SELECT COUNT(*) AS 零订单用户 FROM users u LEFT JOIN orders o ON u.user_id = o.user_id WHERE o.order_id IS NULL;
游客订单 565 零订单用户 0
LEFT JOIN + IS NULL 的语义就是“左边有、右边没有”。返回 0 行,证明右表对左表是全覆盖的,这本身就是一条结论。
COUNT(DISTINCT),不能 COUNT(*)。SELECT COALESCE(u.member_level,'游客') AS 会员等级,
COUNT(*) AS 订单数,
COUNT(DISTINCT o.user_id) AS 下单用户数,
ROUND(SUM(o.pay_amount),2) AS GMV,
ROUND(AVG(o.pay_amount),2) AS 客单价
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE o.status = '已支付'
GROUP BY COALESCE(u.member_level,'游客')
ORDER BY GMV DESC;
会员等级 订单数 下单用户数 GMV 客单价 普通 8786 1169 87934726.93 10008.51 银卡 4199 552 42595219.32 10144.13 金卡 1553 196 16183870.28 10421.04 黑卡 638 83 6375161.69 9992.42 游客 439 0 4274401.92 9736.68
只能说明金卡的平均订单金额最高(10,421.04),不能推导“升级会员会提高客单价”。黑卡反而只有 9,992.42,样本 638 单,噪声很大。
两个数字:JOIN 后的行数,和错误算法下的 GMV。第一个考粒度,第二个考后果。
COUNT(*),不加状态过滤。status='已支付',保留两位小数。orders LEFT JOIN users ON orders.user_id = users.user_id 返回多少行?orders JOIN order_items ON orders.order_id = order_items.order_id 返回多少行?SUM(orders.pay_amount)(只筛已支付),得到 365,790,418.68。正确 GMV 是 157,363,380.14。原因是什么?SUM(i.amount),要算订单金额就先聚合再 JOIN。orders LEFT JOIN users ON ... WHERE users.city = '北京' 的结果,等价于下面哪个写法?= '北京' 过滤掉。LEFT JOIN 的“保留左表”被 WHERE 抵消,结果退化成 INNER JOIN。JOIN 的错,八成体现在行数上。先看行数,再看金额。
| 现象 | 原因 | 解决 |
|---|---|---|
| JOIN 后行数变多 | 右表一对多,左表行被展开(规则 4) | 先聚合右表,或改用 COUNT(DISTINCT) |
| LEFT JOIN 后行数反而变少 | WHERE 里过滤了右表字段(规则 3) | 把右表条件移到 ON,或确认是否本来就想要 INNER |
| SUM 金额正好翻倍 / 三倍 | 订单金额被商品行复制(规则 4) | 改 SUM i.amount,或先按订单聚合再 JOIN |
| JOIN 后结果为空 | 键的格式不一致:空格、大小写、类型不同 | 先 SELECT 两边的键各看 10 行,再 TRIM / 转类型 |
| 游客订单整批消失 | 用了 INNER JOIN(规则 2) | 改成 LEFT JOIN,接受右表字段为 NULL |
| 多表 JOIN 后行数爆炸 | 多对多没走中间表,或漏写连接条件 | 补中间表;检查每个 ON 是否都用上了主外键 |
| 品类合计与总 GMV 对不上 | 原价口径 vs 实付口径(第 6 步) | 统一口径;差额应等于优惠 511,931.50 |
1. INNER 19,435 行,LEFT 20,000 行。差的 565 行是 user_id 为 NULL 的游客订单,INNER 匹配不上就丢掉了。
2. 放大 3 倍。当前实测 2.3245 倍,不是简单的 1.99 行,而是按订单金额加权的平均行数:3 行订单的平均实付远高于 1 行订单,权重更大。
3. 共 565 笔;其中已支付 439 笔,GMV 4,274,401.92 元。SQL 是 orders LEFT JOIN users ... WHERE users.user_id IS NULL,再按状态筛。
4. FROM order_items i JOIN products p ON i.product_id=p.product_id JOIN orders o ON i.order_id=o.order_id WHERE o.status='已支付' GROUP BY o.channel,p.category,金额用 SUM(i.amount)。最高一行是自然搜索 / 手机:19,450,458.74。
NOT IN 与 NULL 的经典陷阱——565 笔游客订单会凭空消失。