With FULL OUTER JOIN ... USING (product_id), what does the single product_id column hold for unmatched rows?

## The rule In an inner join, the two join columns are equal by construction, so merging them is uninteresting. In an outer join they can disagree, because one side may not exist for a given row. The standard resolves this by defining the merged `USING` column as the **coalesce** of the two sides: the left value if it exists, otherwise the right one. ```sql SELECT product_id, a.qty AS qty_a, b.qty AS qty_b FROM stock_a a FULL JOIN stock_b b USING (product_id); ``` For a product in both warehouses, `product_id` is the shared value. For a product only in `stock_a`, it is `stock_a`'s value. For a product only in `stock_b`, it is `stock_b`'s value. The column has a usable value on every output row. ## What the ON version costs Write the same join with `ON` and the result has two key columns: ```sql SELECT COALESCE(a.product_id, b.product_id) AS product_id, a.qty AS qty_a, b.qty AS qty_b FROM stock_a a FULL JOIN stock_b b ON a.product_id = b.product_id; ``` Without the `COALESCE`, selecting `a.product_id` alone quietly loses every product that exists only in `b` — it shows NULL for them, and any downstream `GROUP BY` or join on that column mis-handles them. This is a classic bug in full-outer reconciliation queries, and it is exactly the bug `USING` removes by construction. The convenience is real enough that many people reach for `USING` specifically in full outer joins. ## The same applies to LEFT and RIGHT `a LEFT JOIN b USING (k)` merges `k` too. Since the left side is preserved, the merged column simply always shows the left value — the coalesce never has to fall through. That is why a preserved-side row keeps a meaningful key even when no match exists. ## The trap: you cannot detect unmatched rows with the merged key The familiar anti-join idiom is "outer join, then keep the rows where the other side is NULL". With `USING`, the merged key is *not* the column to test — it is coalesced and stays non-NULL: ```sql -- WRONG: product_id is merged, so it is never NULL for a surviving row SELECT product_id FROM stock_a a FULL JOIN stock_b b USING (product_id) WHERE product_id IS NULL; -- RIGHT: test a column that only one side has SELECT product_id FROM stock_a a FULL JOIN stock_b b USING (product_id) WHERE b.qty IS NULL; -- products missing from warehouse B ``` Use a column that is `NOT NULL` in its own table for that test, so a NULL can only mean "no matching row" and not "matched, but the value happened to be NULL". ## Multiple merged columns `USING (region, product_id)` merges both, each coalesced independently. `NATURAL FULL JOIN` does the same over every shared column name — the merge behaviour is fine there; it is the implicit derivation of the column list that makes `NATURAL JOIN` unsafe in stored code. ## Ordering and grouping on the merged column Because the merged column is a real column of the join and never NULL for a surviving row, it is the natural thing to `GROUP BY` or `ORDER BY` in a reconciliation query — no `COALESCE` wrapper, no accidental NULL group collecting all of the one-sided rows. That is the practical payoff, and it is why a full outer reconciliation written with `USING` tends to be both shorter and less wrong than the `ON` version. ## Portability note `USING` in a `FULL JOIN` is standard and available in PostgreSQL, SQLite and Oracle. MySQL's join syntax supports `USING`, but MySQL has no `FULL OUTER JOIN`, so the combination cannot be written there; SQL Server has `FULL OUTER JOIN` but no `USING` in joins. On those engines you write the `ON` form with an explicit `COALESCE` — which is precisely the manual work `USING` was saving you.