What Is Equi Join and Why Do We Use Equi Join in SQL?

Firstly, SQL is a Structured Query Language that is used for storing, updating, manipulating, and retrieving data from databases or tables.

Basically, Join is an operation used in SQL for combining two or more tables based on some join conditions. Equi Join is also a type of join that is used for joining multiple tables using the equality operator using where and on clauses.

Equal Symbol (=) is used to refer to equality in the where clause and on clause. The joining operation returns the same results as when we use the join keyword with the on clause.

What Is Equi Join in SQL?

• Data can be retrieved from the table using three methods Selection, Projection, and Joins.
• Selection is the method that is used for retrieving data using any conditions, like where the condition is used in a single table.
• Projection is the method that is used for retrieving data from a single table without using any condition.
• Joins are used to retrieve data; if we want to select data from multiple tables and retrieve data that includes related data from multiple tables.
• Equi joins and Non-Equi Joins are the types of Joins used to retrieve data from multiple tables with some specified conditions.
• Equi Join in SQL (Structured Query Language) is used to retrieve data from multiple tables (two or more tables) with the equality condition.
• Non-Equi Join in SQL (Structured Query Language) is used to retrieve data from multiple tables (two or more tables) without the equality condition, that is, with some other conditions except the equal condition.
• Equi Join is a type of inner join which gives the output by performing a join operation between multiple tables on a common column between them.
• Each common column value of one table is compared to each common column value of other tables.
• Common Column names in the tables while performing Equi Joins are not needed to be the same.

Syntax: (using where Clause)

Select table_1.column_1, table_2.column_2, ..

From table_1, table_2

where table_1.common_field = table_2.common_field;

Syntax: (Using on Clause)

Select table_1.column_1, table_2.column_2, ..
From table_1
Inner join table_2
On table_1.common_field = table_2.common_field;

Example 1:

Let us consider two tables; students and marks:

Table 1: (Students Table)

Id	Name	RollNo.	Email id
1	A	511	a511@gmail.com
2	B	512	b512@gmail.com
3	C	513	c513@gmail.com
4	D	514	d514@gmail.com
5	E	515	e515@gmail.com

Table 2: (Marks Table)

Id	Roll	Marks
1	511	98
2	512	99
3	513	97
4	514	96
5	515	95

Now, Let us execute the Equi Join Operation using the statement:

Select s1.Name, m1.Marks
From students as s1, marks as m1
Where s1.RollNo. = m1.Roll;

Our obtained result is: 

We can also execute the query in another way to get the same results as previous results.

Now, Let us execute that statement which gives the same result:

Select s1.Name, m1.Marks
From students as s1 join  marks as m1
Where s1.RollNo. = m1.Roll;

Our obtained result is: 

Now, Let us know Why to use this Equi Join in Structured Query Language (SQL).

Why Use Equi Join in SQL?

As we discussed, Equi Join in SQL is a type of SQL Join that is used to combine two or more tables using the equality of column values of the table condition.

Data is kept in several database tables while working on web apps. Therefore, joining the table becomes necessary whenever it's necessary to merge the data from two tables in order to gain better data insights. One of the SQL joins that may be used to join tables for deeper data analysis is the Equi join.

For Example,

If we execute the following query, we get all columns from both tables:

Select *
From students as s1 join  marks as m1
Where s1.RollNo. = m1.Roll;

The column name which we used to combine or join both tables is id.

How Is Equi Join Different From Non-Equi Join and Natural Join?

• The difference between Equi Join and Non-Equi Join is in the condition that we apply to retrieve data.
• Whereas the difference between the Equi Join and the Natural join is in the output that we obtain, column names are equated for performing both join operations (Equi Join and Natural Join).
• For doing the Natural Join operation, the tables should definitely have the same column name to perform equality operations on them.
• But in Equi Join, the tables need not have the same column name.
• The result obtained in the natural join has a common column only once, whereas in Equi join common column is present from both tables as result.
• Non-Equi Join in SQL (Structured Query Language) is used to retrieve data from multiple tables (2 or more tables) without the equality condition, that is with some other conditions except equal conditions like <, >, <=, >=, != and more.
• Non-Equi Join is used in retrieving data matching in a range of values, for checking for duplicate data between tables, and for calculating the totals.

Syntax:

Select *  
From table1, table2 
Where table1.columnName [> |  < |  >= | <= | != | BETWEEN ] table2 .column;

For example, as per Natural join,

Consider the previous tables students and marks:

Table 1: (Students Table)

Table 2: (Marks Table)

Applying Natural Join, we get the following:

Select *
From students
Natural join marks;

Applying Equi Join, we get the following:

Select *
From students as s1 join  marks as m1
Where s1.id. = m1.id;

Till now, we saw examples of joining two tables; similarly, we can also join three or more tables using Equi join or Natural Join.

Let us see an Example for three tables named products_list, products_details, and brand_details.

Table: products_list

Table: products_details

Table: brand_details

Performing Equi join on three tables:

Select products_list.id, products_list.product_name,
products_details.Brand, products_details.Made_in,
brand_details.Address
From products_list, products_details, brand_details
Where products_list.id = products_details.id 
and products_details.Brand = brand_details.Brand;

The results we obtain are:

Conclusion

• Firstly, We had seen what Equi Join is; Equi Join is used to retrieve data from multiple tables using equality conditions with where and also on clauses.
• Syntax of Equi Join and some examples.
• Next, we had seen why to use this Equi Join in SQL.
• Discussed the difference between Equi join and Non-Equi Join and also discussed the difference between Equi Join and Natural Join.
• Non-Equi Join is the reverse of Equi Join that is used to retrieve data from multiple tables using other than operator instead of equality conditions with where and also on clauses.
• In Natural join, the tables should definitely have the same column name to perform equality operations on them, and the result obtained in the natural join has a common column only once. 
• Discussed some examples to understand more about natural join and Equi join.
• We can also use Equi Join for three tables and more tables also.

I hope you gained some knowledge from this Blog :)