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
FILMtable, as theFILM_ACTORtable 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!