SQL / JOINS
USING and the shorthand that hides too much
Use USING as equality-join shorthand, predict the single merged column it produces, and still detect unmatched rows in outer joins.
What you will learn
- Rewrite ON a.k = b.k as USING (k), and read USING (k1, k2) as an AND of equalities
- Predict that SELECT * returns one merged join column instead of two after USING
- Know the merged column is a COALESCE of both sides, so it hides unmatched rows
- Find unmatched rows with a qualified or optional-side column, never the merged key
Understanding USING and the shorthand that hides too much
USING (dept_id) is compiled into the same equality test you would otherwise type by hand: employees.dept_id = departments.dept_id. Listing several columns, as in USING (year, month), ANDs the comparisons, so every listed column must be equal for a row to survive. The requirement is strict: the column must carry the identical name on both sides and the types must be comparable, which is why USING only fits schemas that name foreign keys after the column they reference.
The real difference from ON is what the result exposes. With ON, two dept_id columns stay in scope and an unqualified dept_id in the select list is an ambiguity error; USING replaces them with one output column whose value is COALESCE(left.dept_id, right.dept_id), so plain dept_id becomes legal and SELECT * shows it once. In an inner join the two inputs are equal by definition, so nothing is lost by merging them. In an outer join they are not equal: an employee with no matching department still shows a dept_id, because the coalesce falls back to the employee's own value.
That fallback is the part that hides too much. The merged column can no longer tell you whether a row matched, so the usual anti-join test WHERE dept_id IS NULL stops meaning "no department found" and starts meaning "this employee's dept_id was already NULL". To recover the information you must reach past the shorthand, either by testing a column that only the optional side supplies or by qualifying departments.dept_id explicitly. The second cost is shape: SELECT * now returns one fewer column and, in PostgreSQL and MySQL, puts the merged key first, which quietly breaks anything reading results by position such as INSERT INTO t SELECT *.
WITH employees(emp_id, name, dept_id) AS (
VALUES (1, 'Ada', 10),
(2, 'Grace', 20),
(3, 'Linus', NULL)
),
departments(dept_id, dept_name) AS (
VALUES (10, 'Research'),
(30, 'Sales')
)
SELECT *
FROM employees
LEFT JOIN departments USING (dept_id)
ORDER BY emp_id;USING is an equality join that also collapses the named columns into one coalesced output column, which is harmless for inner joins and misleading for outer ones.
Worked examples
Which side is missing in a FULL JOIN
Shows that the merged USING column coalesces both keys, so only the qualified columns reveal which table had no partner row.
WITH employees(dept_id, name) AS (
VALUES (10, 'Ada'), (20, 'Grace')
),
departments(dept_id, dept_name) AS (
VALUES (10, 'Research'), (30, 'Sales')
)
SELECT dept_id AS merged,
employees.dept_id AS left_side,
departments.dept_id AS right_side,
CASE
WHEN employees.dept_id IS NULL THEN 'employees missing'
WHEN departments.dept_id IS NULL THEN 'departments missing'
ELSE 'matched'
END AS diagnosis
FROM employees
FULL JOIN departments USING (dept_id)
ORDER BY merged;Example explained
Line 1merged is the column USING created; its value is effectively COALESCE(employees.dept_id, departments.dept_id) and is never NULL here.
Line 2employees.dept_id and departments.dept_id still name the original input columns, which is the only way to see which side was empty.
Line 3Department 20 exists only in employees and 30 only in departments, yet merged prints an ordinary key value for both rows.
Line 4The CASE expression has to test the qualified columns; a test on merged would classify every row as matched.
NATURAL JOIN takes the shorthand one step too far
Compares USING with NATURAL JOIN on the same data, where an unrelated shared column name silently changes the result.
WITH employees(dept_id, name) AS (
VALUES (10, 'Ada'), (20, 'Grace')
),
departments(dept_id, name) AS (
VALUES (10, 'Research'), (20, 'Sales')
)
SELECT
(SELECT count(*) FROM employees JOIN departments USING (dept_id)) AS using_dept_id,
(SELECT count(*) FROM employees NATURAL JOIN departments) AS natural_join;Example explained
Line 1Both CTEs happen to define a column called name, which USING (dept_id) deliberately ignores.
Line 2NATURAL JOIN builds its condition from every shared column name, so it compares name as well as dept_id.
Line 3'Ada' never equals 'Research', so the natural join matches nothing and counts 0 while the explicit USING counts 2.
Line 4Adding a column named name, created_at, or id to either table would change the natural join's result with no error and no warning.
Important notes
USING is not available for joins in SQL Server, and Oracle accepts USING but rejects any qualified reference to the merged column (ORA-25154), so the departments.dept_id diagnostic must be replaced there by a test on a non-key department column.
Where the merged column lands in SELECT * is engine-specific: PostgreSQL and MySQL move it to the front, SQLite leaves it in the left table's position, so list columns explicitly instead of relying on order.
Common mistakes
Testing WHERE dept_id IS NULL after LEFT JOIN departments USING (dept_id): the merged column falls back to the left value, so unmatched employees are silently skipped and only employees whose own dept_id was already NULL come back.
Reading USING (order_id, line_no) as "match on either column": the two equalities are ANDed, so a difference in either one drops the row and the result is often empty.
Switching to NATURAL JOIN because it is even shorter: any incidentally shared column name joins in as well, and a later ALTER TABLE that adds such a column changes the query's result without raising an error.
Try it yourself
Change, predict, then run
Define orders(order_id, customer_id) with three rows, one pointing at a customer_id that customers(customer_id, city) does not contain, and LEFT JOIN them with USING (customer_id). Write the single WHERE clause that returns exactly that orphan order, then confirm that WHERE customer_id IS NULL returns something different.
Open the SQL workspaceCheck your understanding
After FROM employees LEFT JOIN departments USING (dept_id), which condition reliably returns the employees whose dept_id has no matching department row?
- WHERE departments.dept_id IS NULL
- WHERE dept_id IS NULL
- WHERE employees.dept_id IS NULL
- WHERE dept_id NOT IN (SELECT dept_id FROM departments)
Show answer
The unqualified dept_id is the merged column, defined as COALESCE(employees.dept_id, departments.dept_id), so for an unmatched employee it still holds the employee's own value; option 1 therefore returns only employees whose dept_id was already NULL, and option 2 returns exactly the same rows. departments.dept_id still refers to the original right-hand column, which is NULL precisely on rows that found no partner. Option 3 abandons the join result and additionally returns nothing at all if departments contains a NULL dept_id.