SQL / JOINS
Natural joins and why to avoid them
Read a NATURAL JOIN, work out the condition it infers from column names, see how a schema change silently breaks it, and rewrite it with USING or ON.
What you will learn
- Work out a NATURAL JOIN's real condition by intersecting the two tables' column names
- Predict that a NATURAL JOIN with no shared column names behaves as a CROSS JOIN
- Rewrite a natural join as USING (col) or as an explicit ON condition
- Explain why adding a shared audit column can silently reduce a join to zero rows
Understanding Natural joins and why to avoid them
A natural join has no join condition in the query text. The database builds one for you: it looks at both tables, collects every column name that appears in both, and requires equality on all of them at once. Those paired columns are then merged, so each shared name appears once in the result instead of twice. FROM employee NATURAL JOIN department is therefore shorthand for a condition you cannot see by reading the query.
That is the whole problem. The predicate is a function of the current schema, not of the SQL you wrote, so anything that changes column names changes the query's meaning without touching the query. Add a created_on, status, or notes column to one table when the other already has one, and every natural join between them quietly gains an extra AND. Nothing fails to parse and no error is raised; you just get fewer rows, often none, and the query looks exactly as correct as it did yesterday.
Two degenerate cases are worth holding in mind. If the tables share no column names at all, the inferred condition is empty, and a join with no condition is a cross join, so renaming one key column turns a two-row result into a full cross product. The mechanism also never consults foreign keys, so employee.name and department.name get equated with the same enthusiasm as two ends of a real key. USING (dept_id) keeps the brevity and the merged column while naming the join columns explicitly, and ON e.dept_id = d.dept_id states the condition outright; both survive schema changes that would rewrite a natural join.
-- Two tables that share more column names than you might notice
CREATE TABLE department (
dept_id INTEGER PRIMARY KEY,
dept_name TEXT,
created_on TEXT
);
CREATE TABLE employee (
emp_id INTEGER PRIMARY KEY,
emp_name TEXT,
dept_id INTEGER,
created_on TEXT
);
INSERT INTO department VALUES
(1, 'Sales', '2024-01-05'),
(2, 'Support', '2024-02-11');
INSERT INTO employee VALUES
(10, 'Ada', 1, '2024-03-01'),
(11, 'Bo', 2, '2024-02-11');
-- What you meant: match on dept_id
SELECT e.emp_name, d.dept_name
FROM employee e
JOIN department d ON e.dept_id = d.dept_id
ORDER BY e.emp_name;
-- What NATURAL JOIN does: match on dept_id AND created_on
SELECT emp_name, dept_name
FROM employee
NATURAL JOIN department
ORDER BY emp_name;A natural join's condition is inferred from whatever column names the two tables happen to share, so the schema, not the query, decides what the query means.
Worked examples
No shared names means no condition
A natural join between tables with nothing in common silently produces a cross product.
CREATE TABLE color (color_name TEXT);
CREATE TABLE shirt_size (size_code TEXT);
INSERT INTO color VALUES ('red'), ('blue');
INSERT INTO shirt_size VALUES ('S'), ('M'), ('L');
SELECT color_name, size_code
FROM color
NATURAL JOIN shirt_size
ORDER BY color_name, size_code;Example explained
Line 1color and shirt_size share no column name, so the intersection used to build the condition is empty.
Line 2An empty condition is no condition, so every color pairs with every size: 2 x 3 = 6 rows.
Line 3ORDER BY sorts on text, which is why L, M, S appear in that order rather than by size.
Line 4The same thing happens to a real key join the moment someone renames product_id to product_ref on one side only.
A migration rewrites the condition
Adding a column whose name already exists in the other table empties the result without changing the query.
CREATE TABLE product (
product_id INTEGER PRIMARY KEY,
label TEXT
);
CREATE TABLE sale (
sale_id INTEGER PRIMARY KEY,
product_id INTEGER,
qty INTEGER
);
INSERT INTO product VALUES (1, 'Bolt'), (2, 'Nut');
INSERT INTO sale VALUES (100, 1, 3), (101, 2, 7);
SELECT COUNT(*) AS rows_before
FROM sale NATURAL JOIN product;
ALTER TABLE sale ADD COLUMN label TEXT;
SELECT COUNT(*) AS rows_after
FROM sale NATURAL JOIN product;Example explained
Line 1rows_before is 2 because product_id is the only shared name, which happens to be the join you wanted.
Line 2ALTER TABLE sale ADD COLUMN label TEXT gives existing rows NULL and makes label a shared name.
Line 3The condition is now product_id equality AND label equality, and NULL = 'Bolt' evaluates to unknown, so no row qualifies.
Line 4The query text never changed and no error was raised, which is what makes this class of bug expensive to find.
Rewriting with USING
Naming the join column explicitly keeps a coincidentally shared column out of the condition.
CREATE TABLE author (
author_id INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE book (
book_id INTEGER PRIMARY KEY,
author_id INTEGER,
name TEXT
);
INSERT INTO author VALUES (1, 'Le Guin'), (2, 'Borges');
INSERT INTO book VALUES (10, 1, 'The Dispossessed'), (11, 2, 'Ficciones');
SELECT book_id
FROM book NATURAL JOIN author;
SELECT b.book_id, b.name AS title, a.name AS author
FROM book b
JOIN author a USING (author_id)
ORDER BY b.book_id;Example explained
Line 1book and author share author_id and name, so the natural join demands that the book title equal the author's name.
Line 2No title equals its author's name, so the first query returns zero rows rather than an error.
Line 3USING (author_id) names the one column to match on, leaving name as two separate columns.
Line 4b.name AS title and a.name AS author are only possible because name was not merged into the join.
Important notes
SQL Server has no NATURAL JOIN at all; PostgreSQL, MySQL, MariaDB, SQLite and Oracle do, so the syntax is not portable.
Shared columns are merged, so SELECT * returns one copy of each and their position in the output differs from a plain JOIN ... ON; standard SQL and Oracle also forbid qualifying a merged column with a table alias.
Common mistakes
Assuming NATURAL JOIN uses the foreign key. It uses every shared column name, so a common name or created_at column adds extra equality tests and drops rows that should have matched.
Leaving natural joins in place across migrations. ALTER TABLE ... ADD COLUMN can introduce a shared name whose existing values are NULL, and because NULL = anything is unknown, the result set silently becomes empty.
Reading an empty result as 'no matching data'. With a natural join it usually means the inferred condition is stricter than intended, so you spend the afternoon debugging the rows instead of the join.
Try it yourself
Change, predict, then run
Create orders(order_id, customer_id, status) and customers(customer_id, name, status) and insert three orders whose status text differs from their customer's status. Run the join once as NATURAL JOIN and once with USING (customer_id), and compare the row counts.
Open the SQL workspaceCheck your understanding
Tables orders(order_id, customer_id, status) and customers(customer_id, name, status) are joined with SELECT * FROM orders NATURAL JOIN customers, and every order has a valid customer_id. What is the most likely result?
- Rows appear only where the order's status text also equals the customer's status text
- One row per order, matched on customer_id, because that is the foreign key
- An error, because status exists in both tables and is therefore ambiguous
- The full cross product, because NATURAL JOIN discards the join condition
Show answer
The condition is built from every name the tables share, so it requires customer_id equality AND status equality; only orders whose status happens to match their customer's status survive. Option 2 is tempting because a natural join usually looks like a key join, but nothing in the mechanism reads foreign keys, only names. No error is raised either: duplicate names are the input to a natural join, not an ambiguity problem, and the condition is empty only when the tables share no names.