USING and NATURAL Joins

## Two spellings of the same test A join needs a source of rows on each side and a condition saying which pairs survive. `ON` accepts any boolean expression over the two sides: `ON o.customer_id = c.customer_id`, `ON a.x = b.x AND a.y > b.y`, `ON o.placed_at BETWEEN c.valid_from AND c.valid_to`. `USING` is shorthand for one specific shape of that condition — equality between columns that have the same name in both tables. ```sql SELECT * FROM orders o JOIN customers c USING (customer_id); -- join condition is exactly: o.customer_id = c.customer_id ``` Which rows come out is identical between the two spellings. What differs is the shape of the result. ## The merged column With `ON`, both `o.customer_id` and `c.customer_id` remain columns of the join result. `SELECT *` therefore returns `customer_id` twice, and a bare `SELECT customer_id` fails with an ambiguous-column error, because two visible columns answer to that name. With `USING`, the standard merges each listed pair into **one** column that belongs to the join itself rather than to either table. `SELECT *` returns `customer_id` once; the merged columns are listed first, followed by the remaining columns of the left table and then the right; and `SELECT customer_id` is unambiguous. That single difference in result shape is what most interview questions about `USING` are really testing. ```sql -- 3 output columns: customer_id, order_total, customer_name SELECT * FROM orders o JOIN customers c USING (customer_id); -- 4 output columns: customer_id appears twice SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id; ``` That also means `SELECT *` over a chain of joins stays readable: joining five tables on `customer_id` with `USING` yields one `customer_id`, not five. ## Referring to the column afterwards Because the merged column belongs to the join, the portable way to reference it in `SELECT`, `WHERE`, `GROUP BY` and `ORDER BY` is unqualified — just `customer_id`. Engines differ on whether a qualified reference such as `o.customer_id` is still legal after `USING`: Oracle rejects a qualifier on a `USING` column, while PostgreSQL still resolves it to that table's own copy. Writing it unqualified is the habit that works everywhere. ## What USING cannot express `USING` accepts a list of columns — `USING (order_id, line_no)` merges and equates both, ANDed together. It cannot express: - **different names on the two sides** (`orders.customer_ref` versus `customers.customer_id`) — that needs `ON`, or a rename upstream; - **anything but equality** — no `>`, no `BETWEEN`, no function calls; - **extra predicates** you want evaluated as part of the join condition (which matters for outer joins, where a predicate in `ON` runs before NULL-extension and the same predicate in `WHERE` runs after). Nothing about `USING` changes NULL comparison: it is still `=`, so a row whose key is NULL matches nothing on the other side. The two columns must also be comparable types; the merge produces one value, so pairing a `varchar` key with an `integer` key is either an error or an implicit coercion depending on the engine. ## Under outer joins In an outer join the two sides can disagree about whether a value exists. The standard defines the merged column as the coalesce of the two: in `a LEFT JOIN b USING (id)` an unmatched left row shows the left `id`, and in a `FULL JOIN` a row present on only one side shows whichever `id` exists. With `ON` you would write `COALESCE(a.id, b.id)` yourself. ## Portability `USING` is standard SQL and is available in PostgreSQL, MySQL, SQLite and Oracle. SQL Server's join syntax has no `USING` clause at all (its `USING` keyword belongs to `MERGE`), so code that must run there is written with `ON`. ## Choosing between them Prefer `USING` when the key genuinely carries the same name on both sides: it is shorter, it removes the ambiguous-column trap, and it gives one clean key column — especially valuable in outer joins and long join chains. Prefer `ON` when the names differ, when the condition is not pure equality, or when portability to an engine without `USING` matters. What `USING` should *not* tempt you into is `NATURAL JOIN`, which throws away the explicit column list entirely and lets a later migration redefine the join for you.