MySQL NATURAL JOIN

In SQL, the NATURAL JOIN statement allows making a natural join between two tables. This join is performed under the condition that both tables have columns with the same name and the same type. The result of a natural join is the creation of a table with as many rows as there are pairs corresponding to the association of columns with the same name.

Note: since the same column name is required in both tables, this prevents the use of specific naming rules for the column names. For example, it is not possible to prefix the column names otherwise you will, unfortunately, have two different column names.
 

 

Syntax :

The natural join of two tables can be done easily, as the following SQL query shows:

SELECT *
FROM table1
NATURAL JOIN table2

The advantage of a NATURAL JOIN is that there is no need to use the ON clause.
 

 

Example :

A typical use of such a join could be in an application that uses a user table and a country table. If the user table contains a column for the country ID, it will be possible to perform a natural join.
 
User table:

+-----------+-----------+-----------+------------+
|  user_id  | user_name | user_city | country_id |
+-----------+-----------+-----------+------------+
|    1      |   Alex    | New York  |  1         |
|    2      |   Emily   | Chicago   |  1         |
|    3      |   Jean    | Mumbai    |  NULL      |
|    4      |   Bob     | Sydney    |  1555      |
|    5      |   Xavier  | London    |  4         |
+-----------+-----------+-----------+------------+

 
Country table:

+------------+---------------+
| country_id |  country_name |
+------------+---------------+
|      1     | United States |
|      2     | India         |
|      3     | Australia     |
|      4     | England       |
+------------+---------------+

 

 
To get the list of all users with the corresponding country, it is possible to perform an SQL query similar to this one:

SELECT *
FROM User
NATURAL JOIN Country

This query will return the following result:
 
Output:

+------------+-----------+-----------+-----------+---------------+
| country_id |  user_id  | user_name | user_city | country_name  |
+------------+-----------+-----------+-----------+---------------+
|    1       |    1      |   Alex    | New York  | United States |
|    1       |    2      |   Emily   | Chicago   | United States |
|    NULL    |    3      |   Jean    | Mumbai    | NULL          |
|    1555    |    4      |   Bob     | Sydney    | NULL          |
|    4       |    5      |   Xavier  | London    | England       |
+------------+-----------+-----------+-----------+---------------+

This example shows that there was a join between the two tables thanks to the “country_id” column which is found in both tables.
MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More