Join two tables without breaking the number

The lesson that breaks the most numbers

A join combines rows from two tables that share a key. An INNER JOIN keeps only rows that match on both sides. A LEFT JOIN keeps every row from the left table, filling nulls where the right side has no match. Choosing the wrong one, or summing after the wrong join, produces a number that is confidently wrong. This is the lesson that breaks more totals than any other.

Fan-out inflates a total

orders is one row per order. order_items is one row per line, 2.40 per order on average. Join them and sum an order-level column, and each order's total is counted once per line.

First the correct total, at order grain:

bruin query --connection duckdb-default \
  --description "correct total order value at order grain" \
  --query "SELECT SUM(order_total) AS revenue FROM orders;"

That returns 604,065.00. Now the broken version, summing the same column after joining to the lines:

bruin query --connection duckdb-default \
  --description "inflated total order value after the lines join" \
  --query "SELECT SUM(o.order_total) AS revenue FROM orders o JOIN order_items oi ON o.order_id = oi.order_id;"

This runs without error and returns 1.44977182e+06 - scientific notation for 1,449,771.82, which is 2.4 times the correct figure. There is no warning. order_total belongs to the order, so it must be summed at order grain, not after a one-to-many join. The fix is to aggregate before joining, or to sum only line-level columns such as quantity * net_price.

You can make the database do the division for you:

bruin query --connection duckdb-default \
  --description "how inflated is the joined total" \
  --query "SELECT ROUND(SUM(o.order_total) / 604065.0, 2) AS inflation FROM orders o JOIN order_items oi ON o.order_id = oi.order_id;"

inflation comes back as exactly 2.4 - the average number of lines per order, which is no coincidence. Each order was counted once per line.

A LEFT JOIN that quietly becomes an INNER JOIN

order_items has 15 lines whose product_id is not in products. An INNER JOIN to products drops those 15 lines, and with them 3,637.89 of line revenue, without a word. A LEFT JOIN keeps them. See the dropped lines for yourself - this pattern, a LEFT JOIN filtered to the rows with no match, is how you find what an INNER JOIN would silently discard:

bruin query --connection duckdb-default \
  --description "order lines whose product does not exist" \
  --query "SELECT COUNT(*) AS orphan_lines, ROUND(SUM(oi.quantity * oi.net_price), 2) AS orphan_revenue FROM order_items oi LEFT JOIN products p ON oi.product_id = p.product_id WHERE p.product_id IS NULL;"

There is a second trap. A LEFT JOIN protects the left rows only if the filter on the right table lives in the ON clause. Move that filter to WHERE, and unmatched rows fail the condition and disappear, turning the LEFT JOIN back into an INNER JOIN. Put conditions on the right-hand table in ON, and keep WHERE for the left table.

Adding DISTINCT does not fix fan-out. It makes the count look plausible while the grain is still wrong. Treat any DISTINCT in a query as something to justify, not a repair.

Ask your coding agent

SELECT c.country, SUM(o.order_total) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.country;

Checkpoint

Confirm these three numbers, each with a command from this lesson:

• The correct total order value is 604,065.00 (the terminal prints 604065).
• Summing order_total after the order_items join returns 1,449,771.82, and the inflation query returns exactly 2.4.
• The orphan-lines query returns 15 lines and 3,637.89 of line revenue - what an INNER JOIN to products would silently drop and a LEFT JOIN keeps.