Natural Joins

Contact Us or call 1-877-932-8228
Natural Joins

Natural Joins

Code Sample:

Joining-Tables/Demos/natural_join.sql
--Query using INNER JOIN syntax
SELECT * 
FROM regions r 
INNER JOIN countries c ON c.region_id = r.region_id;

--Same rows return using NATURAL JOIN syntax (***NOT RECOMENDED***)
SELECT * 
FROM regions 
NATURAL JOIN countries;

A natural join involves tables joined by columns with the same name. The NATURAL JOIN clause implicitly creates a join on columns with the same column name between the joined tables. The returned results (in the case of a SELECT *) contain only one column for each pair of equally-named columns.

Although it is useful to be aware of NATURAL JOIN syntax, it is best to avoid its use in practice. At best, the columns being used to make the join are not explicitly identified, and this ambiguity can lead to confusion when attempting to debug a query. If a column is renamed, a query referencing the column in a NATURAL JOIN will remain valid, however the results of the query will change.

Next