Skip to content
Tutorials Center
My learning

Topics

Lesson 39 of 125 ยท Joins

NATURAL JOIN and USING

Learn the SQL shorthand joins USING and NATURAL JOIN, how they merge columns, how they differ from ON and why NATURAL JOIN is risky.

So far you have written joins with ON, spelling out which column on the left matches which column on the right. SQL also has two shorter forms for the common case where the matching columns have the same name in both tables: JOIN ... USING (column) and NATURAL JOIN. They save typing and they change the shape of the result slightly. One of them is perfectly fine for everyday work; the other deserves a lot of suspicion.

This lesson shows both, explains what happens to the duplicated column, and lists the situations where each form helps or hurts. The examples run in SQLite, which supports both forms, as do PostgreSQL, MySQL and Oracle. SQL Server supports neither.

A schema where the names line up

The shorthand only works when the join column carries the same name on both sides. Here is a tiny schema built for that, with dept_id used in both tables:

SQL Two tables that share dept_id

Press Run (or Ctrl + Enter) to see the result here.

Dmitri has no department, so the inner join drops him, and the Support department has no staff, so it never appears. That is the normal inner join behavior. Now the same query in shorthand.

JOIN ... USING

USING (dept_id) says "join on equality of the column named dept_id, which exists in both tables". It is exactly the same as ON staff.dept_id = depts.dept_id, written once:

SQL USING replaces ON

Press Run (or Ctrl + Enter) to see the result here.

Look at the last select item: dept_id has no table prefix, and the query still works. With ON, writing a bare dept_id would be an "ambiguous column" error because both tables have one. With USING, the two columns are merged into a single join column, so the bare name is allowed and refers to the merged column.

You can list several columns when the key is composite: USING (store_id, product_id) means both pairs must be equal.

SELECT * with USING

The merge also shows up in SELECT *. The shared column appears once:

SQL SELECT * with USING versus ON

Press Run (or Ctrl + Enter) to see the result here.

The ON version returns a dept_id column twice, one from each table. The USING version returns one dept_id. The position of the merged column differs between databases (PostgreSQL and MySQL put it first, SQLite keeps it in its place among the left table's columns), which is one more reason to list columns explicitly in production queries.

USING with outer joins

Merged columns shine in outer joins. In a FULL or RIGHT join, the merged column holds whichever side has a value, so you never get a NULL key just because the left row is missing. In a LEFT JOIN, it is the left value:

SQL LEFT JOIN with USING

Press Run (or Ctrl + Enter) to see the result here.

Support appears with a NULL staff name, and Dmitri (no department) is not listed because depts is the preserved side. The key column dept_id is filled in for every row.

Limits of USING

  • The column must exist, under the same name, in both sides of that join. If your foreign key is department_id and the primary key is plain id, as in most schemas, you cannot use USING.
  • Qualifying the merged column varies by database: PostgreSQL and MySQL accept staff.dept_id, while Oracle rejects any prefix on a USING column. The bare name is the portable choice.
  • It only expresses equality. For >=, BETWEEN or extra conditions, use ON.
  • You can combine it with a WHERE clause as usual. Putting a filter on the preserved side of an outer join is fine in WHERE, but conditions on the optional side belong in an ON, as the LEFT JOIN lesson explained.

NATURAL JOIN

NATURAL JOIN goes one step further: you do not name any column at all. The database looks at both tables, finds every column name they share, and joins on equality of all of them:

SQL NATURAL JOIN

Press Run (or Ctrl + Enter) to see the result here.

The result is the same as the USING (dept_id) version, because dept_id is the only shared name. There are variants too: NATURAL LEFT JOIN, NATURAL RIGHT JOIN and NATURAL FULL JOIN, where the outer keyword goes after NATURAL.

It reads nicely in a textbook and badly in a production system, because the join condition is not written anywhere. It is implied by the current column names.

Why NATURAL JOIN bites

Suppose a teammate adds a created_at column to both staff and depts, which is a very ordinary change. Watch what happens to the same query:

SQL A new column silently changes the join

Press Run (or Ctrl + Enter) to see the result here.

Only Ben is returned. The join now requires both dept_id and created_at to match, and only his row happens to satisfy both. No error, no warning, just missing rows. The query text didn't change; the schema did.

The other classic trap is the reverse: a join you expected to match on a key matches on nothing because the key columns have different names, and NATURAL JOIN finds no shared columns. In that case it degrades to a CROSS JOIN and returns every combination, which can be enormous. Standard SQL treats "no common columns" as a cross join, and most engines follow that.

Because of these failure modes, most style guides ban NATURAL JOIN outright:

  • It breaks silently when columns are added or renamed.
  • Code reviewers cannot see the join condition without opening the schema.
  • It hides which columns you consider the key.
  • Surrogate-key schemas (id everywhere, customer_id as a foreign key) rarely have matching names anyway.

Never use NATURAL JOIN in application code or views. A harmless schema change can alter what the query returns without a single error message. It is acceptable in a quick one-off exploration on tables you control.

Choosing between ON, USING and NATURAL

Form You write Good for Watch out for
ON a.x = b.y Both columns Any condition, any names Duplicate columns in SELECT *
USING (x) One shared name Same-named key columns Cannot express non-equality
NATURAL JOIN Nothing Throwaway queries Silent changes, accidental cross joins

A practical rule: use USING when your naming convention gives foreign keys and primary keys the same name (customer_id in both customers and orders), because it is short and explicit. Use ON everywhere else. Skip NATURAL.

Also remember dialect support. SQL Server has neither USING nor NATURAL; a query written with them must be rewritten with ON to run there. SQL dialects lists more differences like this one.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does JOIN depts USING (dept_id) do with the dept_id column in SELECT *?

    USING merges the two same-named columns into a single join column, so it appears once and can be referenced without a table prefix.
  2. 2. Which situation makes USING impossible?

    USING needs identical column names on both sides. Different names require ON. Outer joins and composite keys work fine with USING.
  3. 3. A teammate adds a created_at column to both tables of a NATURAL JOIN query. What happens?

    NATURAL JOIN uses every shared column name, so the new column becomes part of the join condition without any warning.
  4. 4. What does NATURAL JOIN do when the two tables share no column names?

    With no common columns the implied condition is empty, so every row pairs with every row, exactly as in a cross join.

Summary

  • JOIN ... USING (col) joins on equality of a same-named column and merges it into one result column that you may reference without a prefix.
  • NATURAL JOIN joins on all same-named columns, with no condition written anywhere.
  • NATURAL JOIN breaks silently when columns are added or renamed and turns into a cross join when no names match, so avoid it in real code.
  • USING is a good shorthand when your key columns share names; ON handles every other case, including non-equality conditions.
  • SQL Server supports neither shorthand; always check dialect differences before porting queries.

Next: CROSS JOIN, which shows what the accidental case really produces.