SQL Joins: Why Your Revenue Doubled After Joining Tables
A join can double revenue without any error. Learn how cardinality and join types multiply rows, how to check row counts, and how to sum at the right level.
7 min readBranislav Mateas
Copy post as Markdown
On this page
You join two tables, the query runs, and revenue is suddenly twice what finance reports. Nothing failed. SQL did exactly what you asked.
A join does not know that a column means money. It builds rows, and any value on those rows can repeat. So before you trust a total, you need to know how many rows each key produces on each side.
This article uses one hypothetical shop. The tables and numbers are invented for illustration, and the SQL has not been run on real data. The arithmetic was worked out by hand, so you can follow every step.
How €400 becomes €800 in a hypothetical shop
The shop has two tables. orders holds one row per order. order_lines holds one row per product line.
True revenue is €400. Both tables agree: order totals add up to €400, and so do the line amounts.
Now you want revenue next to line data, so you write this:
SELECT SUM(o.order_total) AS revenue
FROM shop.orders AS o
JOIN shop.order_lines AS l
ON o.order_id = l.order_id;
The join returns 8 rows, one per line, and each row carries the full order_total of its order. Order 1002 now shows €50 three times.
Order 1001: €150 × 2 rows = €300
Order 1002: €50 × 3 rows = €150
Order 1003: €50 × 1 row = €50
Order 1004: €150 × 2 rows = €300
The query returns €800. Exactly double is a feature of these numbers. Real data gives whatever factor the mix of orders produces, and that factor can move when basket sizes change. So a multiplied metric can show a trend that is not there.
Cardinality decides how many rows come out
Cardinality describes how many rows on one side match a row on the other. There are three shapes that matter:
One-to-one: each key appears at most once on both sides. The row count cannot grow.
One-to-many: a key is unique on one side and repeats on the other. Rows grow to the size of the "many" side, and values from the "one" side repeat. orders to order_lines works this way.
Many-to-many: a key repeats on both sides. Rows multiply.
The rule behind all three is short. For each key, rows out = matching rows on the left × matching rows on the right.
Many-to-many is where the damage gets large. Suppose order 1002 was also paid in two instalments, and you join a payments table as well. That order now produces 3 lines × 2 payments = 6 rows. Its €50 total appears six times, and even line_amount is wrong: its three values appear twice each, so €50 of line value reads as €100.
Do not rely on a table's name or a schema diagram to tell you the cardinality. BigQuery lets you declare primary and foreign keys, but its documentation says plainly that it doesn't enforce them and that you are responsible for keeping your data consistent with them. A declared key is a promise, not a check.
Join type decides which rows survive
Cardinality explains extra rows. The join type explains missing ones. The BigQuery query syntax reference defines the main types:
INNER JOIN keeps only rows that meet the join condition. Plain JOIN means the same thing.
LEFT JOIN keeps every row from the left table. Where nothing matches, the right table's columns are NULL.
RIGHT JOIN is the same idea with the sides swapped.
FULL JOIN keeps matched rows and unmatched rows from both sides.
CROSS JOIN pairs every row of one table with every row of the other. No key is involved.
A NULL key never matches either. The reference treats a NULL join condition as FALSE, so rows with empty keys drop out of an inner join without any warning.
Add one more order to the example. Order 1005 is worth €100, but its lines have not arrived yet. True revenue is now €500.
Join type
Rows
SUM(order_total)
SUM(line_amount)
INNER JOIN
8
€800
€400
LEFT JOIN
9
€900
€400
Neither column shows €500. The inner join drops order 1005 entirely. The left join keeps it, but SUM skips the NULL line amount, so the line total stays at €400.
The two errors can also offset each other. A join that drops some rows and multiplies others can land close to the expected row count, or the expected total, and still be wrong. One matching number is not proof.
Four checks that catch row multiplication
Run these before you trust a joined total. Each one is cheap to write and answers one question.
1. Is the key unique where you assume it is?
SELECT
COUNT(*) AS row_count,
COUNT(DISTINCT order_id) AS distinct_orders
FROM shop.orders;
Here you expect 5 and 5. On order_lines the same query gives 8 and 4, which confirms that the key repeats on that side. If the two numbers differ on a table you believed was unique, you have duplicates or NULL keys. COUNT(*) counts every row, while COUNT(DISTINCT ...) ignores NULL. The first SQL query article covers how DISTINCT behaves.
. Do you know the row count before you run the join?
Work it out first. Eight lines plus one order with no lines means a left join should return 9 rows. Then compare. If you get 14, you have found a repeated key on the side you thought was unique.
3. Does the total survive the join?
Run SUM(order_total) on orders alone, then on the joined result. Here that is €500 against €900. A metric that changes when you add a table has been multiplied or filtered.
4. How many rows failed to match?
SELECT COUNT(*) AS orders_without_lines
FROM shop.orders AS o
LEFT JOIN shop.order_lines AS l
ON o.order_id = l.order_id
WHERE l.order_id IS NULL;
This returns 1, for order 1005. Run it before you pick an inner join, so you know what that join would remove.
Sum each metric at its own level
The fix is not a cleverer aggregate. It is matching each metric to the level where it lives. order_total belongs to orders. line_amount belongs to lines.
The tempting shortcut is SUM(DISTINCT o.order_total). BigQuery accepts it, and it can look correct on a quick test. It removes repeated values, not repeated orders. In this shop the distinct totals are €150, €50 and €100, so it returns €300 against a true €500. Two orders worth the same amount are not one order.
Instead, reduce the "many" side to one row per key, then join:
WITH line_totals AS (
SELECT
order_id,
SUM(line_amount) AS items_total,
COUNT(*) AS line_count
FROM shop.order_lines
GROUP BY order_id
)
SELECT
COUNT(*) AS order_count,
SUM(o.order_total) AS revenue,
SUM(l.items_total) AS items_revenue,
SUM(l.line_count) AS lines
FROM shop.orders AS o
LEFT JOIN line_totals AS l
ON o.order_id = l.order_id;
Now the relationship is one-to-one, so no row can multiply. The query returns 5 orders, €500 in revenue, €400 in item revenue and 8 lines. The €100 gap is real information: it is order 1005, and now you can see it instead of hiding it.
The same trap sits inside the GA4 export
You can hit this without writing the word JOIN against a second table. In the GA4 BigQuery export schema, the items record is repeated for each item, while ecommerce.purchase_revenue belongs to the event. Flattening items with UNNEST is a join between an event and its own array.
The likely result follows from those definitions, though it is not a documented warning. If a hypothetical purchase worth €90 contains 3 items, flattening gives 3 rows, and summing purchase_revenue afterwards reads €270. The query syntax reference also says NULL and empty arrays produce zero rows, so a purchase with no items can vanish from an inner flatten.
Use items.item_revenue for item-level questions and an unflattened query for event-level revenue. Do not assume the two totals match. Compare them first, then report which one you used.
Before you share a joined number, say what one row in the result represents. If you cannot say it in a short sentence, do not sum anything yet.
