NATURAL JOIN

Overview

NATURAL JOIN joins two tables based on every column they have in common by name, without needing an explicit ON or USING clause -- DuckDB infers the join condition automatically from matching column names.

Copy code
SELECT *
FROM orders
NATURAL JOIN customers;
-- equivalent to: JOIN customers USING (customer_id), if customer_id is the only shared column

This is equivalent to writing orders JOIN customers USING (customer_id) when customer_id is the only column name shared between both tables. If the tables share multiple column names, all of them are used as join keys.

Why NATURAL JOIN is risky

The convenience comes with a real hazard: NATURAL JOIN's behavior depends entirely on the current column names of both tables, which can change over time. If someone later adds a new column to either table that happens to share a name with a column in the other table (e.g., both tables gain a created_at or updated_at column), the join condition silently changes to include that column too -- potentially breaking the query's results without any error, and without the change being visible at the query site itself.

Because of this fragility, NATURAL JOIN is generally discouraged in production code in favor of being explicit:

Copy code
-- safer and more explicit than NATURAL JOIN
SELECT *
FROM orders
JOIN customers USING (customer_id);

NATURAL JOIN vs USING vs ON

• NATURAL JOIN: implicit join columns, inferred from shared column names across both tables.
• JOIN ... USING (col): explicit join columns, but still requires the column to share the same name on both sides; only one copy of each USING column appears in the output.
• JOIN ... ON condition: fully explicit condition, with no naming constraints; both tables' versions of any equated column remain in the output as usual.

DuckDB notes

DuckDB supports NATURAL JOIN per the standard, along with NATURAL LEFT JOIN and other join type variants, but USING is generally the recommended middle ground -- explicit about which columns are join keys, while still avoiding duplicate output columns.