This tutorial explains how you can use NATURAL JOINs and what a natural join is actually. Included are syntax details and example statements.

Generally speaking, the keyword NATURAL can be treated as a join condition which is added implicitly. If used, it replaces the keywords ON and USING altogether. In MySQL writing natural joins means adding the keyword NATURAL to either an INNER JOIN or an OUTER JOIN. Let’s take a look at how a natural join implies a join condition.

Syntax

First of all, some natural join syntax examples. As mentioned earlier, a natural join adds a implicit condition to inner and outer join statements:

-- natural inner join
SELECT *
FROM tableA
NATURAL JOIN tableB


-- natural left outer join
SELECT *
FROM tableA
NATURAL LEFT OUTER JOIN tableB


-- natural right outer join
SELECT *
FROM tableA
NATURAL RIGHT OUTER JOIN tableB

Now, let’s take a look at the join condition a natural join implies. See the documentation about INNER JOINs and LEFT JOINs / RIGHT JOINs for a general description of the used joins.

Natural joins

When you combine (join) two tables using a natural join, MySQL compares the columns of both tables to find columns with the same name. If the tables have nothing in common, MySQL builds the Cartesian product (see INNER JOIN for more information). If there is at least one common column, it’s used to build a join condition. To make things clear, take a look at the following statements. They generate the same result set. The only assumption we make is that they both have a column named ‘id’:

SELECT *
FROM tableA
NATURAL JOIN tableB


SELECT *
FROM tableA a, tableB b
WHERE a.id = b.id

This also works when there is more than one common column. MySQL simply implies another condition. In this example we assume there are two common columns: ‘id and ‘email’:

SELECT *
FROM tableA
NATURAL JOIN tableB


SELECT *
FROM tableA a, tableB b
WHERE a.id = b.id
AND a.email = b.email

It may look convenient to write shorter statements, but you should avoid using natural joins if possible. A natural join Let’s take a look at the drawbacks a natural join

Drawbacks

Using natural joins has some drawbacks which are not MySQL specific. A natural join is generally not the best way to solve a problem: First of all, it makes it harder to read statements. The join condition is based on the column names of the joined tables and therefor you have to compare two tables to know what’s going on.

Additionally, natural joins may cause problems when you rename or add columns. Imagine that you have to debug your code at some point in the future. Probably somebody changed the table structure without asking you and the application stopped working. It can be very time consuming to debug such errors. Remember that you have to use natural joins with caution!

More Information

For more information about NATURAL JOINs please also see: