How does SUM(base + bonus) differ from SUM(base) + SUM(bonus) when bonus is NULL?

## Two different places NULL is handled SQL handles NULL in two distinct regimes, and this question sits exactly on the seam between them. - **Row-level expressions propagate NULL.** Arithmetic, concatenation and most scalar functions return NULL if any operand is NULL. `200 + NULL` is NULL; it is not 200 and not an error. - **Aggregates eliminate NULL.** `SUM`, `AVG`, `MIN`, `MAX` and `COUNT(col)` discard NULL inputs and compute over what remains. When you write `SUM(base + bonus)`, the expression `base + bonus` is a row-level computation that runs **before** the aggregate sees anything. Propagation happens first; elimination happens second. A row with a NULL bonus therefore contributes a NULL to the aggregate's input, is eliminated, and takes its `base` value out of the world with it. ## The worked example ```sql -- pay: (base, bonus) = (100, 10), (200, NULL), (300, 20) SELECT SUM(base + bonus) AS sum_of_expr, -- 430 SUM(base) + SUM(bonus) AS sum_of_columns, -- 630 SUM(base + COALESCE(bonus, 0)) AS fixed -- 630 FROM pay; ``` Trace `sum_of_expr`: the rows evaluate to 110, NULL and 320. NULL elimination leaves {110, 320}, summing to 430. The 200 of base pay simply vanished. Trace `sum_of_columns`: `SUM(base)` sees {100, 200, 300} = 600; `SUM(bonus)` sees {10, 20} = 30; the final addition of two non-NULL scalars gives 630. The gap — 200 — is exactly the base of the row that had no bonus. That is what makes this bug so nasty in reporting: the total is plausible, off by an amount nobody can attribute, and it shrinks as the data becomes more complete. ## Which one is right? Almost always the 630. "Total compensation" means every employee's base plus whatever bonus they got, and an employee with no bonus still draws a salary. `SUM(base + bonus)` answers a narrower question — the total compensation *of the people who have a bonus recorded* — which is rarely what the requirement said. The cleanest expression of intent is `SUM(base + COALESCE(bonus, 0))`: it states "a missing bonus counts as zero" once, at the point where it matters, and keeps the row in the aggregate. Prefer it to `SUM(base) + SUM(bonus)` when you also need the per-row value for anything else (a `MAX`, a filter, a `CASE`), and because it does not have the all-NULL asymmetry described next. ## The asymmetry to mention `SUM(base) + SUM(bonus)` is itself vulnerable at the outer addition. If **no** row has a bonus, `SUM(bonus)` returns NULL over its empty input, and `600 + NULL` is NULL — the whole total collapses. The defensive form is `SUM(base) + COALESCE(SUM(bonus), 0)`, which is precisely the outer-guard idiom. So the two rewrites are not simply equivalent-with-a-caveat: each needs its own NULL discipline, one inside the aggregate and one outside it. ## The same trap in other shapes The pattern generalises to any aggregate whose argument is a compound expression: - `AVG(price * quantity)` drops rows where either factor is NULL, changing the denominator as well as the numerator. - `SUM(revenue - discount)` loses the whole revenue of any row with an unrecorded discount. - `MAX(start_at - end_at)` silently ignores unfinished rows, which may be the ones you cared about. - String concatenation is worse in the standard, where `'a' || NULL` is NULL, so a concatenated grouping expression can quietly drop rows too. A good habit: whenever an aggregate's argument is more than a bare column, ask which operands are nullable and decide explicitly whether a NULL there should zero out, drop the row, or be filtered upstream. ## Interview framing Say the rule in one line — "row-level arithmetic propagates NULL, aggregates eliminate it, and the arithmetic happens first" — then show the number that goes missing. If the interviewer pushes, mention `COALESCE` inside for the row-level fix, the outer `COALESCE` for the all-NULL column case, and the fact that `COUNT(base + bonus)` would drop the same rows, so any ratio built from the two is doubly wrong.