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 (the terminal prints 604065). 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.

Your task

Reproduce all three numbers in queries/03-joins.sql. Write the correct order-grain total and confirm 604,065.00. Then sum order_total after joining to order_items and watch it inflate to 1,449,771.82, and run the inflation query to confirm the factor is exactly 2.4:

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;"

Finally, find the orphan lines an INNER JOIN would drop - 15 lines and 3,637.89 of revenue - with the LEFT JOIN pattern above. Do not use DISTINCT to fix any of it.

Check your understanding

• What is the correct total order value, and what does the join inflate it to?
• Why exactly 2.4x?
• How many orphan lines, and how much revenue, would an INNER JOIN drop?

Do it with your agent

Say next lesson and your agent teaches this, asks you these questions, then sets the task above. Do it by hand, then say review my work - it runs your query, checks it against a rubric, and tells you what to fix or marks the lesson done.