In JOIN ... USING (dept_id), how must you reference dept_id, and is d.dept_id portable?

## Where the column lives The key idea behind `USING` is a scoping rule, not a filtering rule. In `emp e JOIN dept d ON e.dept_id = d.dept_id`, the join result contains two columns named `dept_id`, each still owned by its table. In `emp e JOIN dept d USING (dept_id)`, the standard says the pair is **coalesced into a single column of the join itself**. It is no longer `e`'s column or `d`'s column; it is the join's column. Everything else follows from that. ## The portable reference is unqualified Because the merged column belongs to the join, you name it plainly: ```sql SELECT dept_id, COUNT(*) AS headcount FROM emp e JOIN dept d USING (dept_id) WHERE dept_id <> 0 GROUP BY dept_id ORDER BY dept_id; ``` No qualifier, no ambiguity error. This is a genuine ergonomic win over `ON`, where the identical `SELECT dept_id` is rejected because two visible columns answer to the name. ## Qualified references are where engines disagree Candidates often assume `e.dept_id` is simply illegal after `USING`. The honest answer is that it is **not portable**: - Oracle rejects a qualifier on a column that appears in the `USING` clause and raises an error. - PostgreSQL keeps the underlying table columns addressable, so `e.dept_id` and `d.dept_id` both resolve — to the individual source columns, not to the merged one. So the rule to carry into an interview and into a codebase is: reference a `USING` column unqualified; if you find yourself needing to name one side specifically, that is a signal to write `ON` instead, where both columns are plainly addressable everywhere. ## Why it matters more in an outer join Under an inner join the merged column and both source columns always hold the same value, so the distinction is invisible. Under an outer join it is load-bearing. The standard defines the merged column as the coalesce of the two sides, which means: ```sql SELECT dept_id -- merged: never NULL for a surviving emp row FROM emp e LEFT JOIN dept d USING (dept_id); ``` The merged `dept_id` shows the employee's department id even when no matching `dept` row exists. If you wanted to *detect* the unmatched rows, the merged column cannot help you — it is never NULL there. You test some other column of the preserved-side-opposite table instead: ```sql SELECT dept_id FROM emp e LEFT JOIN dept d USING (dept_id) WHERE d.dept_name IS NULL; -- employees whose department row is missing ``` This is the single most common `USING` bug in real code: someone writes `WHERE d.dept_id IS NULL` expecting the classic anti-join idiom, and on an engine that resolves `d.dept_id` to the underlying column it works, while on one that rejects the qualifier the statement will not even parse. Testing a non-key column of the right table is unambiguous and works either way. ## SELECT * and column order The merged column also changes what `SELECT *` produces. The standard orders the join's output as: the merged columns (in the order listed in `USING`), then the remaining columns of the left table, then the remaining columns of the right. So `SELECT *` after `USING (dept_id)` starts with `dept_id` and never repeats it. That is why long chains of joins on a shared key stay readable with `USING` and become noisy with `ON`. ## NATURAL JOIN inherits all of this `NATURAL JOIN` is defined as `USING` over every column name the two tables share, so its matched columns are merged and scoped in exactly the same way — one output column each, referenced unqualified, coalesced under an outer join. The scoping is the good part of `NATURAL JOIN`; the implicit column list is the dangerous part. ## Practical habits 1. Reference `USING` columns unqualified, always. 2. Do not use the merged key column to detect unmatched rows in an outer join — it is coalesced. Test a non-key column from the other side. 3. If a query needs to distinguish the two sides' copies of the key, write `ON` and qualify both explicitly; that is what `ON` is for. 4. Remember SQL Server's join syntax has no `USING` at all, so a codebase that must run there uses `ON` throughout.