Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
SQL: NATURAL JOIN or JOIN USING
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
NATURAL JOIN or JOIN .. USING seem to be useful utilities at first when a normalised schema is very well designed in a way for PRIMARY KEY
and FOREIGN KEY
column names to always match (and in the case of NATURAL JOIN
, all other column names to never match). The Sakila database is an example for the latter, with tables like these:
CREATE TABLE actor ( actor_id BIGINT PRIMARY KEY, first_name TEXT, last_name TEXT, last_update TIMESTAMP ); CREATE TABLE film ( film_id BIGINT PRIMARY KEY, title TEXT, last_update TIMESTAMP ); CREATE TABLE film_actor ( actor_id BIGINT REFERENCES film, film_id BIGINT REFERENCES film, last_update TIMESTAMP )
And indeed, the fact that e.g. ACTOR_ID
is present in both ACTOR
and FILM_ACTOR
tables, and that it means the same thing in both tables is very convenient for queries like these:
SELECT * FROM actor JOIN film_actor USING (actor_id) JOIN film USING (film_id);
But already this simple schema shows that we cannot use NATURAL JOIN
due to the LAST_UPDATE
columns, which shouldn't be part of the JOIN
predicate.
SELECT * FROM actor NATURAL JOIN film_actor -- Wrong, because it joins on actor.last_update = film_actor.last_update NATURAL JOIN film -- Wrong, because it joins on film_actor.last_update = film.last_update
Even USING
can suffer from such ambiguity, e.g. when we happen to add an ACTOR_ID
column to the FILM
table:
-- Where actor_id means the "main" actor ID ALTER TABLE film ADD actor_id BIGINT REFERENCES actor;
This addition is debatable as:
- The naming seems to be insufficiently describing the fact that it is the "main" actor
- From a normalisation perspective, it seems to be redundant to repeat this information on the
FILM
table, as theFILM_ACTOR
table could just have an additional flag about an actor being the "main" actor in a film.
As can be seen, queries containing NATURAL JOIN
or JOIN .. USING
are easy to get (often subtly) wrong, and even easier to break when the schema evolves. As such, it is best to just avoid the language feature entirely, except for the occasional ad-hoc query.
Feedback
Do you have any feedback about this page? We'd love to hear it!