What does NATURAL JOIN do automatically, and why is it risky to use in production?
A natural join joins the two tables on every column that has the same name in both of them. You do not write the condition at all.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL,
region_id INTEGER NOT NULL
);
CREATE TABLE regions (
id INTEGER PRIMARY KEY,
name VARCHAR(60) NOT NULL
);
INSERT INTO customers (id, name, region_id) VALUES
(1, 'Ada Lovelace', 1),
(2, 'Grace Hopper', 2);
INSERT INTO regions (id, name) VALUES
(1, 'Europe'),
(2, 'North America');
-- The natural join. It joins on the id column on its own.
SELECT *
FROM customers
NATURAL JOIN regions;
So why is it risky to use in production? Because it changes when the shape of the table changes. The join condition is worked out from the columns that happen to have the same name, so if somebody adds a column called name to the regions table, the join suddenly has two conditions and the answer changes. Nothing in the code says that the join changed, and nothing fails.
There is a second problem, and it is about the output. The natural join gives you one copy of the common column. So you get one id and one name for two tables that both had a name, and the name in the answer is the one from the first table. You cannot tell which one you are looking at.
So the rule to remember is this. Use a natural join when you are writing a query for yourself and you want it short. In the code that your team maintains, write the condition out, so that the query still means the same thing in six months.
The next question in this topic shows the other spelling.