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! 👍