模块 2 · 多张表,与核对
第 4 节,共 6 节 · 6 分钟 · 简体中文 · 只读
连接为每一对匹配的行返回一行,所以它的粒度是更细那张表的粒度:订单级的列会在每条明细上重复,相加时就会多算;没有匹配的行在普通连接里会消失,除非用 LEFT JOIN。
连接按一列把两张表并排放在一起,这里是 order_id。结果里每一对匹配的行各占一行。一个订单有好几条明细,所以把订单表连到明细表,结果是每条明细一行,订单自己的列(时间、状态、shipping_fee)会复制到它的每条明细上。一张表的一行匹配另一张表的多行时,结果的粒度就是更细那张表的粒度。
复制在你读行的时候无害,在你把它们相加的时候有害。只有行仍然各出现一次的列,连接之后相加才安全。在这次连接里,那是更细的一侧,也就是明细。quantity 和 unit_price 在明细上,所以销售额加得对。shipping_fee 在订单上,所以每条明细都会把它算一次。
下面的图显示了连接之后的五个已付款 3 月订单。
order_id product line_total shipping_fee
102 Kettle 30 0
102 Tea tin 24 0
103 Mug 8 5
103 Tea tin 12 5
103 Filter 12 5
106 Mug 32 0
106 Filter 8 0
107 Tea tin 12 5
107 Mug 8 5
108 Kettle 30 5
108 Filter 4 5
---------- ------------
合计 180 35订单 103 有三条明细,所以它的运费 5 被加了三次。单看订单表,合计是 15:运费为 5 的三个订单各算一次。
检查这个材料
sql语言: sql8 条注释WITH orders(order_id, customer_id, order_ts, status, coupon, shipping_fee) AS ( VALUES (101, 1, '2025-02-28 21:15', 'paid', NULL, 5), (102, 1, '2025-03-02 10:05', 'paid', 'SPRING', 0), (103, 2, '2025-03-09 14:30', 'paid', NULL, 5), (104, 4, '2025-03-12 11:00', 'refunded', NULL, 5), (105, 3, '2025-03-15 09:40', 'cancelled', NULL, 5), (106, 3, '2025-03-21 16:10', 'paid', 'SPRING', 0), (107, 2, '2025-03-28 12:00', 'paid', NULL, 5), (108, 4, '2025-03-31 18:20', 'paid', NULL, 5), (109, 1, '2025-04-01 08:00', 'paid', NULL, 5)),order_lines(line_id, order_id, product, quantity, unit_price) AS ( VALUES ( 1, 101, 'Mug', 2, 8), ( 2, 102, 'Kettle', 1, 30), ( 3, 102, 'Tea tin', 2, 12), ( 4, 103, 'Mug', 1, 8), ( 5, 103, 'Tea tin', 1, 12), ( 6, 103, 'Filter', 3, 4), ( 7, 104, 'Kettle', 1, 30), ( 8, 105, 'Tea tin', 3, 12), ( 9, 106, 'Mug', 4, 8), (10, 106, 'Filter', 2, 4), (11, 107, 'Tea tin', 1, 12), (12, 107, 'Mug', 1, 8), (13, 108, 'Kettle', 1, 30), (14, 108, 'Filter', 1, 4), (15, 109, 'Tea tin', 2, 12)),paid_march AS ( SELECT order_id, shipping_fee FROM orders WHERE order_ts >= '2025-03-01' AND order_ts < '2025-04-01' AND status = 'paid')SELECT 'join, then add' AS method, COUNT(*) AS rows_in_result, SUM(o.shipping_fee) AS shipping, SUM(l.quantity * l.unit_price) AS revenueFROM paid_march AS oJOIN order_lines AS l ON l.order_id = o.order_idUNION ALLSELECT 'one row per order first', COUNT(*), SUM(o.shipping_fee), SUM(t.revenue)FROM paid_march AS oJOIN (SELECT order_id, SUM(quantity * unit_price) AS revenue FROM order_lines GROUP BY order_id) AS t ON t.order_id = o.order_id;method rows_in_result shipping revenue ----------------------- -------------- -------- ------- join, then add 11 35 180 one row per order first 5 15 180
第一行是故意写错的:5 个订单变成了 11 行,运费是 35 而不是 15。它的销售额是对的,因为销售额来自更细的那张表。第二行是 5 行,运费 15,销售额同样是 180。这个 15 和第 2 课只用订单表得到的结果一致。
材料包含 52 行和 8 条注释。
连接误导人的另一种方式,是让行消失。普通的 JOIN,也叫内连接,只保留配上对的行。一个从没下过单的客户,在订单表里没有对应的行,所以这个客户会从结果里消失,用这个结果数出来的客户数也就偏少。
LEFT JOIN(左连接)保留左表的每一行。没有匹配的地方,右表的列全是 NULL。PostgreSQL 的文档描述的是同样的行为。
这又和第 2 课接上了。LEFT JOIN 之后,COUNT(*) 会把没有订单的客户也算成一行,而对 order_id 这样的右表的列计数,只数真正的订单,因为缺少匹配而留下的 NULL 被跳过了。
还有一条规则:要保留左表的每一行,就把右表的条件放在 ON 里,不要放在 WHERE 里。放在 WHERE 里,这个条件对 NULL 占位行不为真,会把没有匹配的客户也一并去掉,LEFT JOIN 就悄悄变成了内连接。
想一想
你自己的工作里,哪里有两张表相遇,比如客户和订单?其中一张表的一行,会匹配另一张表的几行?你的结果里,哪一列属于被重复的那一侧?
检查这个材料
sql语言: sql5 条注释WITH customers(customer_id, name) AS ( VALUES (1, 'Ana'), (2, 'Ben'), (3, 'Chloe'), (4, 'Dev'), (5, 'Elin')),orders(order_id, customer_id) AS ( VALUES (101, 1), (102, 1), (103, 2), (104, 4), (105, 3), (106, 3), (107, 2), (108, 4), (109, 1))SELECT c.name, COUNT(*) AS rows_in_result, COUNT(o.order_id) AS ordersFROM customers AS cLEFT JOIN orders AS o ON o.customer_id = c.customer_idGROUP BY c.nameORDER BY c.name;name rows_in_result orders ----- -------------- ------ Ana 3 3 Ben 2 2 Chloe 2 2 Dev 2 2 Elin 1 0
Elin 在结果里有一行,那是 LEFT JOIN 造出的占位行,她的订单数是 0。如果用普通的 JOIN,她根本不会出现,结果只有四行。
材料包含 27 行和 5 条注释。