Module 2 · Several tables, and proof
Lesson 4 of 6 · 6 min · English · Read-only
A join returns one row per matching pair, so its grain is the finer table’s: an order-level column is repeated on every line and over-counts when added, and rows with no match vanish from a plain join unless you use a LEFT JOIN.
A join places two tables side by side by matching a column, here order_id. The result has one row for every pair of rows that match. An order has several lines, so joining orders to order_lines gives one row per line, and each order’s own columns (time, status, shipping_fee) are copied onto every one of its lines. When one row of one table matches many rows of the other, the grain of the result is the finer table’s grain.
The copying is harmless when you read the rows and harmful when you add them. A column is safe to add after a join only if its rows still appear once each. In this join that is the finer side, the lines. quantity and unit_price live on the lines, so revenue adds up correctly. shipping_fee lives on the order, so it is counted once for every line.
The diagram shows the five paid March orders after the join.
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 35Order 103 has three lines, so its shipping fee of 5 is added three times. The orders table alone adds to 15: the three orders with a fee of 5 count once each.
Inspect the artifact
sqlLanguage: sql8 annotationsWITH 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
The first row is wrong on purpose: 11 rows for 5 orders, and shipping of 35 instead of 15. Its revenue is right because it comes from the finer table. The second row has 5 rows, shipping of 15 and the same revenue of 180. The 15 matches what lesson 2 got from the orders table alone.
Artifact content has 52 lines and 8 annotations.
The other way a join misleads is by dropping rows. A plain JOIN, called an inner join, keeps only pairs. A customer who has never ordered has no partner in orders, so that customer is gone from the result, and any customer count made from it is too small.
A LEFT JOIN keeps every row of the left table. Where nothing matches, the columns from the right-hand table are NULL. The PostgreSQL documentation describes the same behaviour.
That brings lesson 2 back. After a LEFT JOIN, COUNT(*) counts a customer with no orders as one row, while counting a right-hand column such as order_id counts only real orders, because the NULL left by the missing match is skipped.
One more rule: when you want to keep every left-hand row, put a condition on the right-hand table in ON, not in WHERE. In WHERE it is not true for the NULL placeholder rows, so it also removes the customers with no match, and the LEFT JOIN quietly behaves like an inner join.
Think it over
Find a place in your own work where two tables meet, such as customers and orders. How many rows of the finer table does one row of the other match, and which column in your result belongs to the repeated side?
Inspect the artifact
sqlLanguage: sql5 annotationsWITH 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 has one row in the result, the placeholder made by the LEFT JOIN, and zero orders. With a plain JOIN she would not appear at all, and the result would have four rows.
Artifact content has 27 lines and 5 annotations.