Natural Join in SQL

What is Natural Join in SQL?

Suppose we have two tables - one which stores the personal information of employees along with their department ID and the other stores the departments' details. How do we bring the data from two different tables into a single table? The answer is Joins.

Joins in SQL are used to combine data from more than one table based on a join condition and view them together as a single table. We have various types of joins in SQL and this article will focus on Natural Join.

Natural Join in SQL combines records from two or more tables based on the common column between them. The common column must have the same name and data type in both the tables. SQL joins the tables based on this common column and hence, we do not need to explicitly specify the join condition.

Syntax

Example of Natural Join in SQL

Let us create two tables employee and department and insert some data into it. We will then implement natural join on these two tables.

Create Employee Table

Insert Records into Employee Table

We can view the employee table using SELECT query.

Output

Create department table

Insert records into department table

We can view the department table using SELECT query.

Output

Natural Join on Employee and Department Tables

Code

Output

Explanation

We look for common column(s) in the employee and the department tables. There is one column in both the tables which has the same name DeptID and the same data type varchar. Hence we use DeptID for joining our tables. SQL fetches the department ID of each employee from the employee table, maps the department ID to the corresponding record in the department table and displays the output as a new table with records merged from both the input tables.

Types of Natural Join in SQL

Natural inner join only displays records with a common department ID. What if I want to include the departments which do not have an employee yet? Let us look at some examples which combine natural join with left, right, and full outer joins in SQL.

Natural Left Join in SQL

Natural left join displays every single record from the left table, irrespective of whether the common column value (here, DeptID) occurs in the right table or not.

Syntax

Code

Output

Explanation

The department ID D3000 is not present in department table. Hence the details of Shreya whose department ID is D3000 were not available in the natural join output. In the above example though, Shreya’s records are available since natural left join displays all the records from the left (employee) table.

Natural Right Join in SQL

Natural right join is similar to natural left join but here, every record from the right table will be present in the output. Suppose there is a new Finance department but it does not have any employees yet. We can still see its details in the joined table using natural right join!

Syntax

Code

Output

Explanation

We have an extra row for the Finance department with ID D4002, when compared to the natural join example. Since no employee has joined this department yet, the employee details are all null.

Natural Full Join in SQL

Natural full join is like a union of both input tables. If a given DeptID is not available in the other table, the missing data will be filled with null values in the output.

Syntax

Code:

Output:

Natural Join with WHERE Clause

Syntax

Code

Output

Explanation We first create a natural join of the employee and department tables. Then we filter out those records from the resultant table where the department name is Technology.

Natural Join using Three Tables

We can join more than two tables using natural join in SQL. We already have two tables employee and department. Let us create another table address which will store the location city and state of each department.

Create address table

Insert records into the address table

We can view the address table using the SELECT query.

Output:

Let us see natural join-in action on three tables.

Syntax

Code

Output

Explanation

First, we join employee and department using their common column DeptID. Then the resultant table is joined with the address table using the Location column which is common between the new pair of tables. Only those records are returned in the final output which has a common department ID as well as location city.

Natural Join with No Common Column

We have seen that natural join combines two tables based on common column(s) between them. What if there is no common column between our input tables, for example, the employee and address tables? Let us find out!

Code

Output

Explanation

Since there is no common column between the tables, each record of the employee table is combined with each record of the location table. This type of join is known as Cross Join or Cartesian Product. We had 6 records in the employee table and 4 records in the location table which resulted in 6x4 = 24 records in our output table.

Difference between Natural Join and Inner Join

We mentioned natural join does an inner join by default. Then what is the difference between natural and inner join in SQL? Let’s see!

Inner Join Syntax

Inner Join in SQL

We will use the same example as we did in Natural Join - we will join employee and department tables using the common column DeptID.

Code

Output

Explanation

While the internal functioning and the output of both queries seem quite similar, there are some major differences.

In the inner join query, we use the ON clause to explicitly mention the common column for joining our tables. This is different from natural join which automatically fetches the common columns based on the name and data type.

Also, the output table contains the DeptID column twice. Since both input tables have the column DeptID, inner join fetches this column twice whereas in natural join, the output always contains unique columns.

Finally, natural join can be used with left, right or full join but the inner join cannot be combined with other joins.

Difference Between Natural and Inner Join in SQL