MySQL NATURAL JOIN Tutorial & Examples

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 JOINsRIGHT 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:

This entry was posted in Join Tutorials and tagged , , , , . Bookmark the permalink.

4 Responses to MySQL NATURAL JOIN Tutorial & Examples

  1. imran says:

    wonderful tutorial on join..
    very helpful…..

  2. Dennis says:

    Great post, I was looking for this for hours, because I have to tables with same name fields and I could only call a field from one table this query has solved that but I’m trying to see how I can optimize it

    • jbrinkmann says:

      Hi. Thanks for your comment! There are a lot of options regarding to MySQL statements and performance optimization. If you can tell me more about the tables, the statements, the indexes you created and what you want to achieve I’ll be glad to help you. without further information this is not possible.

      I can recommend the following book which is dedicated to SQL Statement optimization:

      Effective MySQL Optimizing SQL Statements

  3. Deepaa says:

    thanks for this post,
    it s very helpful to me while learning about joins

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>