Updated May 15, 2023
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 must ensure the common column has the same data type in both 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 Table 1 and Table 2. Both tables have at least one column of the same data type and 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 you can use NATURAL JOIN in tables with more than one common column and 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, EMP, details the employee number as E_No, employee name as E_name, and employee location as Location.
Code:
SELECT * from EMP;Output:
The second table, Dept, now 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 below: Print the names of employees with a department assigned to them.
If we do a basic analysis of the two tables EMP and Dept, we can learn that there is a common column in these two tables, E_No. But the required output says employee names, where the employee names field is taken from the EMP table. So it is clear that we need to join the two tables to get the output.
From the second table, it is also clear that only three employees have a department assigned to them, whereas there are five employees in the company. So, two employees are not assigned to any department. The two employees without departments 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 the EMP table is the same as E_No from the Dept table. This is the same output required for our query as well.
Output:
Although the above SELECT query may seem simple and easy to write, it can become more complicated and time-consuming when dealing with multiple common columns or a larger amount of data required in the output. 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 should be specified like above. Let us also look into using INNER JOIN before we get detailed into NATURAL JOIN. The query will look like this,
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, with 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 fewer 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 more straightforward, also.
We discussed the scenario with a normal SELECT query using where clause and 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 will be performed with the 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 the title E_No in common. Thus, the query will select only those rows with a value in the EMP and Dept table. Looking at the tables, we can identify three rows with data in both tables. So the output will be like the below:
Output:
This explains the basic working of NATURAL JOIN in a SELECT query. You can use NATURAL JOIN in combination with the WHERE clause. Suppose, from the above tables, we need to select only Alan’s data, 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:
You can use NATURAL JOIN 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 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 names and similar data types. It is mainly used 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
We hope that this EDUCBA information on “Natural Join in MySQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
