Difference between inner join and equi join and natural join

SQL join clause is used to retrieve data from two or more database tables. In the previous article, I have explained the Different Types of SQL Joins. In this article, I would explain the difference between inner join, equi join, and natural join.

By using joins in SQL Server, we can retrieve the data from two or more tables based on specific logical conditions or the connection between the tables of the database. The joins are used to indicate how the SQL Server should use the data from one table to select the rows in another table using the different types of join and the same type of join we are going to discuss in this article.

Inner Join

This is the most used join in the SQL. this join returns only those records/rows that match/exist in both the database tables.

The INNER JOIN in SQL server option is considered one of the most common types of joins needed for the applications and/or queries. But sometimes that is the case in some of the environments, it is completely dependent on the type of database design, internal referential integrity, and data source needed for the application to be shown in the end results. As such, we need to take some time to understand the data being requested then select the proper join option accordingly.

Below is a simple example showing the use of joins where the table "tblDept" and "tblEmp" are being joined based on the DeptId column.

Inner Join Example

SELECT * FROM tblEmp JOIN tblDept
ON tblEmp.DeptID = tblDept.DeptID;

In the join condition, you can also use other operators like <,>,<>.

Equi Join

Equi join is a special type of join in which we use only an equality operator. Hence, when you make a query for join using equality operator then that join query comes under Equi join.

The equi join creates a JOIN for equality or matching of the single or multiple column values of the relative tables. Apart from that, the equi join also create the JOIN by using JOIN along with the ON clause and then providing the names of the columns with their relative tables to check equality using the equal operator.

Below is a simple example of equi having the use of  ON condition.

Equi Join Example

SELECT * FROM tblEmp JOIN tblDept
ON tblEmp.DeptID = tblDept.DeptID;
--Using Clause is not supported by SQL Server
--Oracle and MySQL Query
SELECT * FROM tblEmp INNER JOIN tblDept USING(DeptID)

Note

1. Inner join can have equality (=) and other operators (like <,>,<>) in the join condition.

2. Equi join only have an equality (=) operator in the join condition.

3. Equi join can be an Inner join, Left Outer join, Right Outer join

4. The USING clause is not supported by SQL Server and Sybase. This clause is supported by Oracle and MySQL.

Natural Join

A natural join is a type of equi join which occurs implicitly by comparing all the same names columns in both tables. The join result has only one column for each pair of equally named columns.

In other words, the Natural Join helps to join the two tables based on the same attribute name and the datatypes. The result of the JOIN table will contain all the attributes of both tables but keep only one copy of each common column accordingly.

Below is an example of a natural join. After using the Natual join, the result set will contain only one column for each pair of equally named columns. If there are no columns with the same names are found, then the result will be a "cross join". 

Natural Join Example

--Run in Oracle and MySQL 
SELECT * FROM tblEmp NATURAL JOIN tblDept

In the above join result, we have only one column "DeptID" for each pair of equally named columns.

Note

1. In Natural join, you can't see what columns from both the tables will be used in the join. In Natural join, you might not get the desired result that you are expecting.

2. The natural join clause is not supported by SQL Server, it is supported by Oracle and MySQL.

Read More Articles Related to SQL Server

What do you think?

I hope you will enjoy the tips while playing with SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

Take our free skill tests to evaluate your skill!

In less than 5 minutes, with our skill test, you can identify your knowledge gaps and strengths.

GET TEST NOW