Natural Join in MySQL

Introduction to Natural Join in MySQL

Natural Join in MYSQL is a Join operation used in the SELECT query, to retrieve rows from two or more tables with a common column name. We need to make sure the common column has the same data type, in both the tables. We have understood that JOIN is a cross product with a condition, which means the output is derived from multiple tables based on the condition specified. NATURAL JOIN has this ‘condition’ pre-defined that, the output is a product of common columns in all the tables under study.

Syntax

The NATURAL JOIN syntax is as follows:

SELECT row_name from table_1_name NATURAL JOIN table_2_name;

Explanation: Here we have two tables with names table 1 and table 2. Both these tables have at least one column which is of the same data type and has the same column name. Now, the output is ‘row_name’ which can be anyone or more rows with similar values in both tables 1 and table 2. This is the simplest way of explaining the NATURAL JOIN. We can discuss this further in detail with examples. Also, note that NATURAL JOIN can be used in tables having more than one common column and also in scenarios with more than two columns.

How Natural Join in MySQL Work?

Let’s discuss the working of NATURAL JOIN in detail. For that, we can consider two tables as below:

Our first table, named EMP, is having details regarding the employee number asE_No, employee name as E_name, and employee location as Location.

Code:

SELECT * from  EMP;

Output:

Now, the second table, named Dept, has data regarding the employee number as E_No, employee department as Dept and department name as Dept_name.

Code:

SELECT * from Dept;

Output:

Now, we need the output as below: Print the names of employees who have a department assigned to them.

If we do a basic analysis of the two tables EMP and Dept, we can get to know that there is a common column in these two tables, which is E_No. But the required output says, employee names, where employee names field is taken from EMP table. So it is clear that we need to join the two tables to get the output.

Now from the second table, it is also clear that, only three employees have a department assigned to them, whereas there are 5 employees totally in the company. So, two employees are not assigned to any department. The two employees without department are employees with IDs 2 and 4.

Let’s consider writing a query for the question, with normal SELECT and a where clause as below.

Code:

select EMP.E_No, EMP.E_Name from EMP, Dept where EMP.E_NO = Dept.E_No;

This SELECT query does the below function: select those employee names from the tables EMP and Dept, where the E_No from EMP table is the same as E_No from the Dept table. This is exactly the same output required for our query as well.

Output:

The above SELECT query might look simple and easy to write, but imagine if there is more common columns and the data needed in output are also more in number. In that case, the simple SELECT query will become more complicated.

As another option, INNER JOIN can also replace the NATURAL JOIN, but the condition is to be specified like above. Let us have a quick look into using INNER JOIN also before we get detailed into NATURAL JOIN. The query will look like,

Code:

SELECT EMP.E_No, EMP.E_Name from EMP INNER JOIN Dept on EMP.E_No = Dept.E_No;

This query will pick rows from the EMP table, which has the field E_No the same in both EMP and Dept tables. As said above, this query will be simpler and easy to understand only on small tables, with a lesser number of columns, common columns, and data.

Output:

That is where the NATURAL JOIN is used. The operation will select the required rows based on all the common columns available. The SELECT query will look much simpler also.

We have discussed the scenario with a normal SELECT query using where clause, and with INNER JOIN. Now, let’s write the same query with a NATURAL JOIN.

Code:

SELECT E_No, E_Name from EMP NATURAL JOIN Dept;

To explain the above query, consider the two tables we discussed above. The query asks to select the employee names from the table EMP, where a NATURAL JOIN is to be performed with table Dept. We know NATURAL JOIN considers the common columns from the two tables understudy and here Dept table and EMP table have one column with title E_No in common. Thus the query will select only those rows, which has value in both the EMP and Dept table. Looking at the tables we can identify there are three rows with data in both the tables. So the output will be like below:

Output:

This explains the basic working of NATURAL JOIN in a SELECT query. NATURAL JOIN can be used along with the WHERE clause also. Suppose, from the above tables, we need to select only Alan’s data, which is an employee with data in both Emp and Dept tables, and E_No as 1. The query will be as follows:

Code:

SELECT E_No, E_Name from EMP NATURAL JOIN Dept WHERE E_No = ‘1’;

Output:

NATURAL JOIN can be used on more than two tables as well. The third table can have the common column or it can have any other columns from either of the tables.

Conclusion

We have discussed in detail about the working of NATURAL JOIN. To recollect, below are the main point, NATURAL JOIN gives the output as rows from two or more tables with common column name and similar data types. It is an operation used mainly with SELECT queries. NATURAL JOIN makes the SELECT query simpler with minimal use of conditions. Make sure the common columns have the same column name and data types.

Recommended Articles

This is a guide to Natural Join in MySQL. Here we discuss introduction to Natural Join in MySQL, syntax and how does it work in detail. You can also go through our other related articles to learn more –

1. Natural Join SQL
2. Joins in MySQL
3. MySQL Self Join
4. MySQL Trigger