Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Joins and Subqueries

SQL NATURAL JOIN and USING

Reference for SQL NATURAL JOIN and the USING clause: join on same-named columns, see how the shared column is merged, and why NATURAL JOIN is risky.

USING and NATURAL JOIN are shortcuts for the common case where the two tables name their join column the same way. JOIN ... USING (col) means "join where col is equal on both sides". NATURAL JOIN goes one step further and uses every column name the two tables have in common, without you listing them.

Both also change the output: the joined column is returned once, not twice, when you select *. USING is explicit and reasonably safe. NATURAL JOIN is convenient but fragile, because adding a column to a table can silently change what the query joins on.

SQL
SELECT * FROM a JOIN b USING (customer_id);
SELECT * FROM a NATURAL JOIN b;

Try it

SQL USING with a shared column name ยท database: school

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

SQL NATURAL JOIN finds the common column itself ยท database: company

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

SQL The danger: NATURAL JOIN on the wrong column ยท database: bookstore

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

The third example joins books.id to authors.id, which has nothing to do with who wrote the book. The query runs without an error and returns wrong data, which is exactly why many style guides ban NATURAL JOIN.

Syntax

SQL
SELECT ...
FROM table_a
JOIN table_b USING (column [, column ...]);

SELECT ...
FROM table_a
NATURAL [INNER | LEFT | RIGHT | FULL] JOIN table_b;
Part Description
USING (col, ...) Join on equality of the listed columns, which must exist with the same name in both tables. Can be combined with INNER, LEFT, RIGHT and FULL.
NATURAL Implicitly uses all columns that have the same name in both tables. If there are none, the result is a cross join.

Output columns: with SELECT *, each merged column appears once, first, followed by the remaining columns of the left table and then the right table.

Dialect differences

Database USING NATURAL JOIN
SQLite Supported Supported
PostgreSQL Supported Supported
MySQL Supported Supported
Oracle Supported; the USING column must not be qualified with a table alias in the query Supported; same restriction
SQL Server Not supported Not supported
SQL
-- SQL Server: write the condition out
SELECT *
FROM enrollments AS e
JOIN courses AS c ON c.id = e.course_id;

For outer joins, the merged column holds the value from whichever side has a row, so a LEFT JOIN ... USING still shows the key for unmatched rows from the left table.

Usage notes

  • Rename to match. Foreign keys are often named author_id while the primary key is id, so USING rarely applies unless you rename a column in a subquery, as the examples do. Naming a key customer_id in every table where it appears makes USING natural.
  • Prefer USING over NATURAL. You list the columns, so adding an unrelated column later cannot change the join.
  • Avoid NATURAL JOIN in real code. Common names like id, name, created_at or updated_at are the usual victims.
  • Other shared names stay ambiguous. Only the USING columns are merged. If both tables also have, say, a name column, you still need qualifiers for it.
  • NULL never matches. Like ON, USING uses equality, so rows with NULL in the key do not join (unless the join is an outer join keeping them).
  • Combining with other joins: the merged column behaves as a single column in later ON clauses, but some databases limit qualification; test before relying on it.