Joins Without Accidental Row Multiplication

A join produces combinations of matching rows. It does not know that a measure from one table should be counted only once. When an order has several lines and several payments, joining both detail tables can repeat each line for every matching payment. A later aggregate then adds a perfectly valid joined result with the wrong reporting grain.

Before writing the join, declare the grain of each input and the desired output. One row per order, one row per order line, and one row per payment are different objects. The desired result might be one row per order with separate line and payment totals. That requirement determines where aggregation belongs.

Inspect a distinguishing example

Suppose one order has two lines worth 10 and 20, and two payments worth 5 and 25. Joining lines to payments through the order creates four combinations. The line sum becomes 60 and the payment sum becomes 60, although each underlying total is 30.

The equal inflated totals can be especially misleading. A reconciliation of those two incorrect aggregates may report no difference. Agreement is useful only after the compared measures have been constructed at the intended grain.

Using DISTINCT on the amount is not a general repair. Two different lines can legitimately have the same amount, and DISTINCT would collapse them. Row identity, rather than numeric equality, determines whether two records should be counted separately.

Aggregate each side to the shared grain

This self-contained PostgreSQL example reduces each detail input to one row per order before joining:

WITH orders(order_id) AS (
  VALUES (1), (2)
), lines(order_id, amount) AS (
  VALUES (1, 10.00::numeric), (1, 20.00::numeric)
), payments(order_id, amount) AS (
  VALUES (1, 5.00::numeric), (1, 25.00::numeric)
), line_totals AS (
  SELECT order_id, sum(amount) AS line_amount
  FROM lines GROUP BY order_id
), payment_totals AS (
  SELECT order_id, sum(amount) AS paid_amount
  FROM payments GROUP BY order_id
)
SELECT o.order_id, l.line_amount, p.paid_amount
FROM orders AS o
LEFT JOIN line_totals AS l USING (order_id)
LEFT JOIN payment_totals AS p USING (order_id)
ORDER BY o.order_id;

Order 1 has two totals of 30.00. Order 2 remains present with missing totals. Those nulls mean that the example contains no matching detail rows; deciding whether to display zero requires a separate reporting rule.

The technique also needs units and filters aligned. Aggregating payments across currencies or including cancelled lines would create different errors without multiplying rows. Cardinality is one part of the measure's meaning, not the entire meaning.

Preserve the coverage question

Choose the join direction deliberately. An inner join omits a parent with no matching child. A left join can preserve it. That difference matters when the report needs to show incomplete orders or missing payments rather than only complete matches.

A filter on a child column in WHERE can remove the unmatched rows from a left join. Put a child eligibility condition in the matching logic when unmatched parents must remain, or filter the child input before joining. Check the intended result against a parent with no eligible children.

PostgreSQL's table expression documentation describes the join and filtering semantics. The report still needs its own decision about which unmatched records are meaningful and how they should be presented.

Use existence when existence is the question

If the question is whether an order has at least one successful payment, a boolean existence test expresses that directly. Joining every successful payment and deduplicating the orders afterward performs extra work and makes the intended grain less obvious.

Similarly, count the object named by the requirement. Counting joined rows measures combinations. Counting a non-null child identifier measures matched child records only if other joins have not multiplied them. For a report at parent grain, calculate the child count at parent grain before adding another many-sided input.

Check both uniqueness and completeness

Verify that each supposedly reduced input has at most one row per join key. A later change adding a dimension to GROUP BY can silently break that property. A total grouped by order and currency is not unique by order alone.

Also inspect unmatched key counts in both directions. A result can be unique while omitting every orphan payment. If the report starts from the order table, an independent coverage check is needed to reveal payments whose order is absent.

Keep a fixture with multiple details on both sides, a missing match and repeated equal amounts. It distinguishes the common wrong repairs from the intended result. A join is ready to aggregate when the input grains, output grain, matching rules and coverage behavior are all explicit enough to inspect row by row.