SQL NATURAL

What is the SQL NATURAL keyword?

Adds an implicit equality condition between every column that has the same name in the joined tables.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL NATURAL: PostgreSQL, MySQL, MariaDB, Oracle, SQLite, DuckDB support NATURAL. SQL Server, Snowflake, BigQuery do not.

SQL NATURAL Full Explanation

The SQL NATURAL keyword is a prefix that can be placed in front of JOIN types (INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, CROSS) to create an implicit equijoin. When NATURAL is present, the database engine automatically discovers all columns that share the same unqualified name in both tables and builds an equality predicate for each of them, eliminating the need for explicit ON or USING clauses. The result set keeps only one copy of each matched column, preventing duplicate name conflicts.Because every shared column becomes part of the join condition, NATURAL can be concise but also dangerous: a new column with a reused name or a schema refactor can silently change query semantics or produce zero-row results. NATURAL is defined in the SQL standard, but some vendors (notably SQL Server) do not implement it. When supported, it behaves identically inside explicit transactions and respects the chosen join type (e.g., NATURAL LEFT JOIN will still return unmatched left rows).

SQL NATURAL Syntax

SELECT select_list
FROM table1
NATURAL [INNER] JOIN table2;

SELECT select_list
FROM table1
NATURAL {LEFT | RIGHT | FULL} [OUTER] JOIN table2;

SELECT select_list
FROM table1
NATURAL CROSS JOIN table2;

SQL NATURAL Parameters

Example Queries Using SQL NATURAL

-- Simple natural inner join
SELECT *
FROM customers
NATURAL JOIN orders;

-- Natural left outer join keeps all customers
SELECT c.customer_id, c.name, o.order_id
FROM customers AS c
NATURAL LEFT OUTER JOIN orders AS o;

-- Using natural join inside a CTE
WITH recent AS (
  SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '30 day'
)
SELECT *
FROM recent NATURAL JOIN order_items;

Expected Output Using SQL NATURAL

For every example, the database produces a joined result set where rows are matched on all columns having identical names (e
g
, customer_id)
Duplicate join columns appear only once in the projection unless explicitly re-selected

Use Cases with SQL NATURAL

Quickly join dimension and fact tables that share standardized key names
Ad-hoc data exploration when you are certain column names align
Writing concise demo or teaching queries without lengthy ON conditions
Building views that rely on stable, controlled schemas

Common Mistakes with SQL NATURAL

Forgetting that a new column with the same name in both tables will automatically join, unexpectedly filtering rows
Assuming NATURAL JOIN keeps both copies of the matching columns
Expecting NATURAL JOIN to work in SQL Server, Snowflake, or other non-supporting databases
Mixing NATURAL with an explicit ON clause (not allowed)

First Introduced In

SQL-92 standard (widespread support added in later vendor versions)

Frequently Asked Questions

What is the difference between NATURAL JOIN and USING?

USING requires you to enumerate the columns that make up the join predicate, giving explicit control. NATURAL JOIN deduces the list automatically, which is shorter to write but easier to break when schemas change.

Does NATURAL JOIN automatically drop one copy of the join column?

Yes. After the join, only a single instance of each matched column remains in the result set, avoiding duplicate names like customer_id and customer_id.

Can I combine NATURAL JOIN with an ON clause?

No. NATURAL JOIN already defines its own join condition. Combining it with ON or USING will raise a syntax error.

Which databases do not support NATURAL JOIN?

SQL Server and Snowflake are notable databases that omit NATURAL from their SQL dialects. PostgreSQL, MySQL, Oracle, and SQLite do support it.