Difference Between Equi Join and
Natural Join
Introduction
Hey Ninjas! As we all know, the Database is used to store information in the form of
tables. And in MNCs, there are n numbers of tables in their databases. So have you
ever thought about how these tables are being handled? If not, you are at the right
place. Let's dive deep into this topic.
This blog will discuss the difference between equi join and natural join. But before
moving to the main topic, let's first understand equi and natural join.
Equi Join
A join that is formed as a result of the equality condition between the two same columns
of multiple tables is called an Equi join. This is also known as simple join. The
comparison operator to denote the equi join is "=".
Syntax
The syntax of Equi join is as follows:
SELECT columnName (s)
FROM tableName1, tableName2, ...., tableNameN
WHERE tableName1.columnName = tableName2.columnName;
OR
SELECT columnName (s)
FROM tableName1
JOIN tableName2
ON tableName1.columnName = tableName2.columnName;
Example
Let's take an example of this for better understanding. Suppose we have two tables
named Student and Marks, as shown below.
Student Table:
Marks Table:
As you can see, the RollNo column is the same in both tables, so by following the
syntax of equi join, we have
SELECT * FROM Student, Marks WHERE Student.RollNo = Marks.RollNo;
Output:
The output remains the same even if we use the other syntax. Let's have a look.
SELECT * FROM Student JOIN Marks ON Student.RollNo = Marks.RollNo;
Output:
If you notice in this example, the RollNo column is rendering twice because the equi join
just merged both tables on the basis of the RollNo. To overcome this, we have the
Natural Join. Let's have a look.
Natural Join
A natural join is a kind of equi join that occurs when a common column with the same
name in a different table gets compared and appears only once in the output.
Syntax
The syntax of Natural join is as follows:
SELECT columnName (s)
FROM tableName1
NATURAL JOIN tableName2;
Example
Let's take an example of this for better understanding. Suppose we have two tables that
we used earlier, named Student and Marks, as shown below.
Student Table:
Marks Table:
The above tables have a common column, i.e., RollNo. So, we will use the natural join
using this column (RollNo). Let's have a look.
SELECT * FROM Student NATURAL JOIN Marks;
Output:
Explanation:
As you can see in the output, the column with the name RollNo. is the same in both the
Student and Marks table but still, it occurred only once in the output after using the
Natural Join.
Equi Join Vs Natural Join
As we are done with the definitions and working examples, it's time for the 1v1
comparison between equi and natural join. Let's check this in a tabular form.
Equi Join Natural Join
Equi Join is a join having a condition where we While performing a natural join, we
use an equal operator. do not use an equal operator.
We use Equi Join if we need both tables in the We use Natural Join if we need only
output without missing any column. the unique columns without
repetition.
We can use the WHERE clause or the ON Just use the keyword NATURAL
clause in Equi join. JOIN to perform the task.
The common column comes twice if we select The common column comes only
all columns in the query. once, even if we select all columns in
the query.
The syntax of Equi Join is The syntax of Natural Join is
SELECT columnName (s) SELECT columnName (s)
FROM tableName (s) WHERE FROM tableName1
tableName1.columnName = NATURAL JOIN tableName2;
tableName2.columnName;
