MySQL NATURAL JOIN clause explained (with code examples)

Learn how MySQL NATURAL JOIN works with practical code examples

Posted on November 04, 2021


The MySQL NATURAL JOIN clause allows you to join two or more tables without explicitly specifying the ON clause.

With the NATURAL JOIN CLAUSE, MySQL will perform a smart deduction and combine your tables based on the columns that have the same name and type.

The NATURAL JOIN syntax is as shown below:

SELECT [column_names | *]
FROM table_one   
NATURAL JOIN table_two;  

Let’s see the NATURAL JOIN clause in action.

Suppose you have two tables in your database named scores and subjects with the following data:

# scores table
+----+-------+
| id | score |
+----+-------+
|  1 |     4 |
|  2 |     7 |
|  3 |     8 |
|  4 |     5 |
+----+-------+

# subjects table
+----+-----------+
| id | subject   |
+----+-----------+
|  1 | Math      |
|  2 | English   |
|  3 | Chemistry |
|  4 | Physics   |
+----+-----------+

When you query the tables above using the NATURAL JOIN clause, MySQL will use the id column in the two tables to combine the tables.

The following query:

SELECT *
  FROM scores   
  NATURAL JOIN subjects;

Will produce the following output:

+----+-------+-----------+
| id | score | subject   |
+----+-------+-----------+
|  1 |     4 | Math      |
|  2 |     7 | English   |
|  3 |     8 | Chemistry |
|  4 |     5 | Physics   |
+----+-------+-----------+

The NATURAL JOIN script above is equal to the following JOIN script:

SELECT scores.id, score, subject
  FROM scores   
  JOIN subjects
  ON scores.id = subjects.id;  

The advantage of NATURAL JOIN over the regular JOIN clause is that NATURAL JOIN will automatically combine columns that have the same name and data type.

Suppose you add a new column named student_name to both tables as follows:

# scores table
+----+--------------+-------+
| id | student_name | score |
+----+--------------+-------+
|  1 | Jack         |     4 |
|  2 | Mike         |     7 |
|  3 | John         |     8 |
|  4 | Ash          |     5 |
+----+--------------+-------+

# subjects table
+----+--------------+-----------+
| id | student_name | subject   |
+----+--------------+-----------+
|  1 | Jack         | Math      |
|  2 | Mike         | English   |
|  3 | John         | Chemistry |
|  4 | Ash          | Physics   |
+----+--------------+-----------+

Then the NATURAL JOIN clause will automatically combine both the id and student_name columns in the returned output.

The result set will be as follows:

+----+--------------+-------+-----------+
| id | student_name | score | subject   |
+----+--------------+-------+-----------+
|  1 | Jack         |     4 | Math      |
|  2 | Mike         |     7 | English   |
|  3 | John         |     8 | Chemistry |
|  4 | Ash          |     5 | Physics   |
+----+--------------+-------+-----------+

When the value of the identical columns are different, then MySQL will exclude the row(s) with different values from the output.

Suppose you have a different student_name value at id=4 as shown below:

# scores table
+----+--------------+-------+
| id | student_name | score |
+----+--------------+-------+
|  1 | Jack         |     4 |
|  2 | Mike         |     7 |
|  3 | John         |     8 |
|  4 | Ash          |     5 |
+----+--------------+-------+

# subjects table
+----+--------------+-----------+
| id | student_name | subject   |
+----+--------------+-----------+
|  1 | Jack         | Math      |
|  2 | Mike         | English   |
|  3 | John         | Chemistry |
|  4 | Vale         | Physics   |
+----+--------------+-----------+

Then the same NATURAL JOIN clause will produce the following result set:

+----+--------------+-------+-----------+
| id | student_name | score | subject   |
+----+--------------+-------+-----------+
|  1 | Jack         |     4 | Math      |
|  2 | Mike         |     7 | English   |
|  3 | John         |     8 | Chemistry |
+----+--------------+-------+-----------+

The fourth row was excluded because it has different student_name values between the two tables.

And that’s how the NATURAL JOIN clause works. Just like the regular JOIN clause, you can still narrow down the result set by adding a WHERE clause.

The following example query:

SELECT *
  FROM scores   
  NATURAL JOIN subjects
  WHERE score > 4;

Will return the following output:

+----+--------------+-------+-----------+
| id | student_name | score | subject   |
+----+--------------+-------+-----------+
|  2 | Mike         |     7 | English   |
|  3 | John         |     8 | Chemistry |
+----+--------------+-------+-----------+

Now you’ve learned how the NATURAL JOIN clause works in the MySQL database server. Nice work! 👍

Related articles:

Level up your programming skills

I'm sending out an occasional email with the latest programming tutorials. Drop your email in the box below and I'll send new stuff straight into your inbox!

No spam. Unsubscribe anytime.