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.
SELECT * FROM a JOIN b USING (customer_id); SELECT * FROM a NATURAL JOIN b;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
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
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 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_idwhile the primary key isid, soUSINGrarely applies unless you rename a column in a subquery, as the examples do. Naming a keycustomer_idin every table where it appears makesUSINGnatural. - Prefer
USINGoverNATURAL. You list the columns, so adding an unrelated column later cannot change the join. - Avoid
NATURAL JOINin real code. Common names likeid,name,created_atorupdated_atare the usual victims. - Other shared names stay ambiguous. Only the
USINGcolumns are merged. If both tables also have, say, anamecolumn, you still need qualifiers for it. NULLnever matches. LikeON,USINGuses equality, so rows withNULLin 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
ONclauses, but some databases limit qualification; test before relying on it.
Related
- INNER JOIN, LEFT JOIN, CROSS JOIN, self join
- Lessons: Joins, INNER JOIN