Why does a FULL OUTER JOIN result need COALESCE(l.customer_id, r.customer_id) for its key column?

## Two key columns, not one It is tempting to think of a join as merging rows on a shared key, leaving one key column behind. That is not what a join on `ON l.customer_id = r.customer_id` does. The output row has the left table's columns followed by the right table's columns, and the key appears **twice**. With an inner join that duplication is harmless: the predicate guaranteed the two copies are equal, so either one will do. With a `FULL OUTER JOIN` it stops being harmless, because either copy may be NULL: | bucket | l.customer_id | r.customer_id | |---|---|---| | matched | value | same value | | left-only | value | NULL | | right-only | NULL | value | Selecting `l.customer_id` alone reports NULL for every right-only row; selecting `r.customer_id` alone reports NULL for every left-only row. Neither column can serve as the result's identifier. ## The COALESCE idiom `COALESCE(a, b)` returns the first of its arguments that is not NULL, so: ```sql SELECT COALESCE(l.customer_id, r.customer_id) AS customer_id, l.orders_2023, r.orders_2024 FROM orders_2023 l FULL OUTER JOIN orders_2024 r ON l.customer_id = r.customer_id; ``` gives one always-populated `customer_id`. For matched rows both arguments are equal, so the choice does not matter; for left-only rows it yields the left value; for right-only rows the left argument is NULL and the right value is returned. The alias matters as much as the expression: without `AS customer_id` the output column has an implementation-defined name that downstream code cannot rely on. A closely related form is `JOIN ... USING (customer_id)`, which produces a single merged key column instead of two; that clause has its own semantics and restrictions, and the explicit `COALESCE` is the form you write when the key columns have different names or when you also want the raw per-side columns. ## Why grouping and ordering are the sharp edge Selecting the wrong key column produces a visibly odd result — a column of NULLs. Grouping by it produces a *plausible* but wrong result, which is far more dangerous: ```sql -- WRONG: every right-only customer collapses into one NULL group SELECT l.customer_id, SUM(COALESCE(r.amount, 0)) FROM a l FULL OUTER JOIN b r ON l.customer_id = r.customer_id GROUP BY l.customer_id; -- RIGHT SELECT COALESCE(l.customer_id, r.customer_id) AS customer_id, SUM(COALESCE(r.amount, 0)) FROM a l FULL OUTER JOIN b r ON l.customer_id = r.customer_id GROUP BY COALESCE(l.customer_id, r.customer_id); ``` Grouping treats all NULLs as one group, so in the wrong version every customer who appears only in the second table is merged into a single row keyed by NULL, and their measures are summed together. Ordering has a milder version of the same defect: all the right-only rows sort together at whichever end the engine places NULLs, rather than interleaving by key. Note that the grouping expression is repeated in the `GROUP BY`. Whether you may instead write `GROUP BY customer_id` referring to the select-list alias depends on the engine, so repeating the expression is the portable form. ## Beyond the key The same reasoning extends to any column that both sides carry. If both tables have `region` and you want the region regardless of which side supplied it, `COALESCE(l.region, r.region)` is the answer. For measures the pattern is usually different — `COALESCE(l.amount, 0)` turns a missing side into a zero so that arithmetic like `COALESCE(r.amount, 0) - COALESCE(l.amount, 0)` produces a number instead of NULL, which is what a delta column needs. ## The caveat: manufactured NULLs versus stored NULLs `COALESCE` treats every NULL the same, but a NULL in the output has two possible origins: the join manufactured it because there was no partner row, or the source row genuinely stored NULL there. For the join key under an equality predicate the ambiguity is limited — a stored NULL key never matches anything, so such a row lands in the unmatched bucket anyway — but for ordinary columns it is real. When your query must report *which side* a row came from, do not infer it from a coalesced value; test the key columns explicitly, for example `CASE WHEN l.customer_id IS NULL THEN 'right only' WHEN r.customer_id IS NULL THEN 'left only' ELSE 'both' END`, and prefer a column the source guarantees is not nullable for that test. ## Summary A full outer join hands you two key columns, each NULL for one third of the result. `COALESCE(l.key, r.key)` rebuilds the single identifier you actually wanted; use it in the select list, the `GROUP BY` and the `ORDER BY`, alias it, and keep the raw columns around when you still need to know which side a row came from.