Adding a second LEFT JOIN doubled the revenue figure on a report. Explain what happened and write the correct query.

The multiplication, seen on three rows

The join type is a red herring. What has happened is that orders has two independent one-to-many children, and joining both in one query produces every combination of them.

-- Wrong. Order 1 has 3 lines and 2 payments, so the join emits 6 rows:
-- each payment amount is summed 3 times and each line total twice.
SELECT o.id,
       SUM(i.line_total) AS goods,
       SUM(p.amount)     AS paid
FROM   orders o
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN payments    p ON p.order_id = o.id
GROUP BY o.id;

Three lines of £10 and two payments of £15 should report goods of £30 and paid of £30. The query reports £60 and £90. Both numbers are wrong, they are wrong by different factors, and neither is wrong by a constant you could divide out, because the factor for each column is the row count of the other child and that varies per order. A spot-check against a single-child order looks perfectly correct, which is why this survives review.

The rule to state is that you may join at most one one-to-many relationship per query and still aggregate safely. Beyond that, every additional collection multiplies the row count and every aggregate over the result is measuring the wrong set.

Aggregate first, then join

The fix is to collapse each child to one row per parent before it meets the other. Do the aggregation in a derived table, and the join becomes one-to-one.

SELECT o.id,
       COALESCE(i.goods, 0) AS goods,
       COALESCE(p.paid,  0) AS paid
FROM   orders o
LEFT JOIN (SELECT order_id, SUM(line_total) AS goods
           FROM   order_items
           GROUP  BY order_id) i ON i.order_id = o.id
LEFT JOIN (SELECT order_id, SUM(amount) AS paid
           FROM   payments
           GROUP  BY order_id) p ON p.order_id = o.id;

COALESCE is not decoration. An order with no payments produces a NULL from the outer join, and paid of NULL propagates into every arithmetic expression downstream, so goods - paid silently becomes NULL rather than the full outstanding balance. This is the same reason SUM over an empty set returns NULL rather than 0, which surprises people: SUM ignores NULLs and is defined as NULL when it has nothing to add, while COUNT of the same empty set is 0.

Why DISTINCT is not the fix

The reflex fix is SUM(DISTINCT p.amount), and it appears to work on the example above because the two payments happen to be for different amounts. Give the order two payments of £15 each and the answer becomes £15. DISTINCT deduplicates values, and money legitimately repeats, so this converts a systematic error into an intermittent one that depends on your data. It is strictly worse, because the systematic version gets noticed.

COUNT(DISTINCT i.id) is a different matter and is genuinely correct, because a primary key does not repeat. That is the whole distinction: DISTINCT inside an aggregate is safe only over something unique by construction. Counting items and summing money in the same fanned-out query means one column can be rescued and the other cannot, which is a good sign you should not be in that query shape at all.

One neighbouring point belongs here because it appears in the same reports. COUNT(*) counts rows and COUNT(p.id) counts non-NULL values, and under an outer join they differ: an order with no payments contributes one row carrying a NULL p.id, so COUNT(*) says 1 and COUNT(p.id) says 0. The second is the one you want, and it is the idiomatic way to count matched children.

The outer join that quietly becomes an inner one

A related failure lands in the same reports. Filtering the right-hand table of a LEFT JOIN in the WHERE clause discards the very rows the outer join was written to preserve, because the unmatched rows carry NULL in that column and the predicate is not true for NULL.

-- Behaves as an INNER JOIN: orders with no settled payment vanish entirely.
FROM orders o
LEFT JOIN payments p ON p.order_id = o.id
WHERE p.status = 'settled'

-- Correct: the condition restricts which payments match, and orders with
-- no matching payment survive with NULLs.
FROM orders o
LEFT JOIN payments p ON p.order_id = o.id AND p.status = 'settled'

The exception is WHERE p.id IS NULL, which is deliberate and is the anti-join idiom for "orders with no settled payment". Recognising that IS NULL is the one predicate that works in the WHERE clause here, and understanding why, is a small thing that reads as fluency.

Two engine differences worth naming

Grouping strictness is the first. PostgreSQL requires every selected column to appear in GROUP BY or be functionally dependent on the grouped primary key, and rejects the query otherwise. MySQL enforces the same rule only when ONLY_FULL_GROUP_BY is enabled, which has been the default since 5.7.5; with it disabled it returns an arbitrary row's value for the ungrouped column, with no warning and no determinism. A query developed against a permissive MySQL and deployed against PostgreSQL fails loudly, which is the better outcome; the reverse direction is how a report ends up reporting one line's price as the order's price.

The second is NOT IN with NULLs, and it is not a difference so much as standard behaviour nobody expects. id NOT IN (SELECT order_id FROM payments) returns no rows at all if any order_id in that subquery is NULL, because id <> NULL evaluates to unknown rather than true and the conjunction can never be satisfied. NOT EXISTS has no such trap and usually plans as an anti-join, so prefer it unconditionally for this shape.