Which columns does NATURAL JOIN match on, and what does it return if the tables share none?
answer
- the engine writes the condition, not you
- it reads the two tables' column names
- every shared name joins the key list
- status and created_at get pulled in too
- empty shared list still produces rows
basics
~20 sNATURAL JOIN implicitly equates every column name the two tables share and merges each matched pair into one output column. If they share no column names the join condition is empty, so the result is a Cartesian product.
solid answer
~50 s`a NATURAL JOIN b` is defined as `a JOIN b USING (<every column name that appears in both>)` — the engine derives the list from the two tables' current column names, ANDs the equalities, and merges each matched pair into one output column, listing them first. Nobody writes the list, which is the whole problem: it silently includes incidental columns such as `status`, `name`, `created_at` or `id` that happen to be spelled the same on both sides. The degenerate case is the one interviewers like: when the two tables share **no** column names, the derived condition is empty, so `NATURAL JOIN` behaves like `JOIN ... ON TRUE` and returns the full Cartesian product rather than an error or zero rows. `NATURAL LEFT JOIN` and `NATURAL FULL JOIN` are valid too, and inherit the same implicit matching.
code
sql · 7 lines-- orders(order_id, customer_id, status)
-- customers(customer_id, name, status)
SELECT * FROM orders NATURAL JOIN customers;
-- derived condition matches BOTH shared names:
-- orders.customer_id = customers.customer_id
-- AND orders.status = customers.status
-- almost certainly not what the author meantgo deeper
Know the definition: it joins on every column name the two tables have in common, and you never see that list in the query. Recognise it in code and be able to say what it expands to.
Explain that it is USING over a derived list, that incidental columns like status or created_at get included, and that an empty shared list gives a Cartesian product rather than an error.
Take a position on it in reviewed code and back it with the failure mode: a derived condition means a schema change silently rewrites the query, and nothing in the diff shows it.
Frame it as a schema-coupling decision: implicit joins turn every column rename into a semantic change across unrelated queries. Encode the rule in review standards or linting rather than relying on individual discipline.
## The definition `NATURAL JOIN` has no join condition of its own. The engine builds one by looking at the two sides' column names at the time the statement is compiled, taking every name that appears on **both** sides, and equating them pairwise with `AND`. It then merges each matched pair into a single output column, exactly as `USING` does. So, for `orders(order_id, customer_id, status)` and `customers(customer_id, name, status)`: ```sql SELECT * FROM orders NATURAL JOIN customers; -- equivalent to SELECT * FROM orders JOIN customers USING (customer_id, status); ``` Note what happened: `status` was silently pulled into the join key. Two independent columns, both innocently named `status` (one an order state, one an account state), now have to be equal for any row to appear. Almost no rows will qualify, and the query returns a nearly empty result with no error and no warning. ## The output shape Because `NATURAL JOIN` is `USING` over the derived list, the result columns are: the merged common columns first, then the left table's remaining columns, then the right table's remaining columns. `SELECT *` never shows a duplicated name, and the merged columns are referenced unqualified. Under an outer form (`NATURAL LEFT JOIN`, `NATURAL FULL JOIN`) each merged column is the coalesce of the two sides. ## The empty-common-list case The question interviewers most enjoy: what if the two tables share no column names at all? The derived condition is then the empty conjunction, which is true, so every left row pairs with every right row. `NATURAL JOIN` degenerates into a **Cartesian product** — the same result as `CROSS JOIN`. It is not a syntax error, and it is not an empty result. PostgreSQL documents exactly this behaviour, describing it as equivalent to `JOIN ... ON TRUE`. A `NATURAL JOIN` between two unrelated tables of a million rows each therefore attempts a trillion-row result rather than telling you the join makes no sense. ```sql -- countries(country_code, country_name), currencies(currency_code, currency_name) SELECT * FROM countries NATURAL JOIN currencies; -- no shared column names -> every country paired with every currency ``` ## Why the implicitness is the defect The join condition of a `NATURAL JOIN` is not written anywhere. It is a *derived* property of two schemas, so it changes when either schema changes — a new column, a rename, a column dropped from one side. Nothing in the query text records the author's intent, so a reviewer cannot tell whether the join was meant to be on `customer_id` alone, and a later migration can redefine the join without touching the query. Compare with `USING (customer_id)`, which reads almost as tersely but pins the condition. If someone later renames or drops `customer_id`, the query fails loudly at compile time instead of quietly returning a different answer. That is the trade that matters: `USING` fails closed, `NATURAL JOIN` fails silently. Additional consequences worth naming: - **Correctness is invisible in review.** You must have both `CREATE TABLE` statements in front of you to know what the query does. - **Views compound it.** A `NATURAL JOIN` inside a view is re-derived against whatever the underlying relations expose, so the view's meaning can shift with a base-table change. - **`SELECT *` interacts badly.** If a derived table feeding the natural join uses `*`, its column list — and hence the join key — depends on a third table's shape. ## Portability `NATURAL JOIN` is standard SQL and is available in PostgreSQL, MySQL, SQLite and Oracle. SQL Server's join syntax offers neither `NATURAL JOIN` nor `USING`. So `NATURAL JOIN` is also the least portable of the join spellings. ## What to say in an interview Define it precisely (implicit `USING` over all shared names, merged output columns), name the empty-list degenerate case (Cartesian product, not an error), and state the practical position: write the join condition down. `USING (key)` when the names match, `ON` when they do not. `NATURAL JOIN` is fine for a one-off query against a schema you can see on screen and wrong for anything that lives in a repository.
- Is NATURAL JOIN ever legitimately useful?In ad-hoc exploration against a schema you can see, and in textbook-style schemas where the shared name is exactly the key, it saves typing and gives one clean key column. It has no place in stored code: the condition is derived from the current schema rather than written down, so nobody reviewing the query can tell what it joins on, and a migration can change the answer.
- Can you combine NATURAL with an outer join?Yes — `NATURAL LEFT JOIN`, `NATURAL RIGHT JOIN` and `NATURAL FULL JOIN` are valid standard syntax. The derived column list is built the same way, unmatched rows are NULL-extended as usual, and each merged column is the coalesce of the two sides. The implicit-condition risk is identical, and an outer form hides it better because rows still come back.
- What does NATURAL JOIN do about column order in the result?The merged common columns come first, in the order they appear in the left table, followed by the left table's remaining columns and then the right table's. So `SELECT *` from a natural join has a different column order than the equivalent `ON` join — another reason positional column access over `*` is fragile.
saying these in an interview costs you the question
- Says NATURAL JOIN matches only on primary or foreign keys
- Expects an error when the tables share no column names
- Thinks no shared columns yields zero rows
- Believes the join key is fixed at the time the query was written
- Confuses NATURAL JOIN with an automatic foreign-key lookup