模組 2 · 多張資料表,以及驗證
第 4 節,共 6 節 · 7 分鐘 · 繁體中文 · 唯讀
聯結對每一組配對成功的列各回傳一列,所以結果的粒度是比較細那張表的粒度:訂單層級的欄位會在每筆明細上重複,加總時就算多了;沒有配對到的列在一般的 JOIN 裡會消失,除非改用 LEFT JOIN。
聯結(JOIN)把兩張資料表依某個欄位配對,併排在一起,這裡配對的欄位是 order_id。每一組配得上的列,結果就有一列。一筆訂單有好幾筆明細,所以把 orders 和 order_lines 聯結起來,會得到每筆明細一列,而每筆訂單自己的欄位(時間、狀態、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
---------- ------------
sum 180 35訂單 103 有三筆明細,所以它的運費 5 被加了三次。只看 orders 資料表,運費合計是 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 課只用 orders 資料表算出的相符。
材料包含 52 行和 8 則註解。
聯結誤導人的另一種方式,是讓列消失。一般的 JOIN,也叫內部聯結(inner join),只保留成對的列。一位從沒下過單的顧客,在 orders 裡找不到配對對象,所以他不會出現在結果裡,從結果算出的任何顧客數都會偏小。
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 則註解。