What is Equi Join and why do we use Equi Join in SQL?

Firstly, SQL is a structured query language used to store, update, manipulate and retrieve data from databases or tables.

Basically, JOIN is an operation which is used in SQL to join 2 or more tables based on certain join conditions. Equi join is also a type of join that is used to join multiple tables using the where and on clauses using the equivalence operator.

The equal symbol (=) is used to refer to equality in the where clause and the on clause. Joining operation gives the same result when we use JOIN keyword with ON clause.

What is Equi Join in SQL?

  • The data can be retrieved from the table using 3 methods SELECT, PROJECTION and JOIN.
  • SELECT is the method which is used to retrieve data using any condition such as if condition is used in a table.
  • Projection is the method which is used to get the data from a single table without using any condition.
  • JOIN is used to retrieve data, if we want to select data from multiple tables, then retrieve data which contains related data from multiple tables.
  • Equi join and non-equi join are types of joins used to retrieve data from multiple tables with certain specified conditions.
  • Equi join (Structured Query Language) in SQL is used to retrieve data from multiple tables (2 or more tables) with equality condition.
  • NON-EQUI JOIN (Structured Query Language) in SQL is used to retrieve data from multiple tables (2 or more tables) without equality condition except for the similar condition with few other conditions.
  • Equi join is a type of inner join that returns the output by performing a join operation between multiple tables on a common column between them.
  • Each common column value in one table is compared with each common column value in other tables.
  • Common column names across tables are not required to be the same when doing equi joins.

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’s consider 2 tables; students and marks,

Table 1: (Students Table)

identity

Name

Serial Number.

E mail 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

I

515

e515@gmail.com

Table 2: (Marks Table)

identity

To wander

scar

1

511

98

2

512

99

3

513

97

4

514

96

5

515

95

Now, let us perform 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 result is

Name

scar

a

98

b

99

C

97

D

96

I

95

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

Now, we execute the 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 result is

Name

scar

a

98

b

99

C

97

D

96

I

95

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

Why Use Equi Join in SQL?

As we discussed that Equi join in SQL is a type of SQL join which is used to join 2 or more tables using equality of column value of table condition.

When working on web apps the data is kept in multiple database tables. Hence, table join becomes necessary whenever it is necessary to merge data from two tables to get better data insights. One of the SQL joins that can be used to join tables for deep data analysis is Equi Join.

For example,

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

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

identity

Name

Serial Number.

E mail ID

identity

To wander

scar

1

a

511

a511@gmail.com

1

511

98

2

b

512

b512@gmail.com

2

512

99

3

C

513

c513@gmail.com

3

513

97

4

D

514

d514@gmail.com

4

514

96

5

I

515

e515@gmail.com

5

515

95

The column name we use to join or join both the tables is ID.

How is equi join different from non-equi join and natural join?

  • The difference between equi join and non-equi join lies in the condition that we apply to retrieve the data.
  • While the difference between Equi Join and Natural Join is in the output we get, the column names are the same for performing both the join operations (Equi Join and Natural Join).
  • In order to perform natural join operations, tables must have the same column names in order to perform equality operations on them.
  • But in equi join, the column names of the tables should not be same.
  • The result obtained in natural join has common column only once whereas in equi join common column is present as result from both the tables.
  • In SQL (Structured Query Language) non-equi join is used to get data from multiple tables (2 or more tables) without equality condition, that is, with some other conditions, such as EXCLUSING EQUAL conditions <,>, <=,> =, != and more.
  • NON-EQUI JOINs are used to retrieve data matching across a range of values, check for duplicate data between tables, and calculate totals.

syntax:

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

For example according to natural join,

Consider the students and marks of the previous tables,

Table 1: (Students Table)

identity

Name

Serial Number.

E mail 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

I

515

e515@gmail.com

Table 2: (Marks Table)

identity

To wander

scar

1

511

98

2

512

99

3

513

97

4

514

96

5

515

95

By applying natural join we get,

Select *
From students
Natural join marks;

identity

Name

Serial Number.

E mail ID

To wander

scar

1

a

511

a511@gmail.com

511

98

2

b

512

b512@gmail.com

512

99

3

C

513

c513@gmail.com

513

97

4

D

514

d514@gmail.com

514

96

5

I

515

e515@gmail.com

515

95

By applying equi join we get,

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

identity

Name

Serial Number.

E mail ID

identity

To wander

scar

1

a

511

a511@gmail.com

1

511

98

2

b

512

b512@gmail.com

2

512

99

3

C

513

c513@gmail.com

3

513

97

4

D

514

d514@gmail.com

4

514

96

5

I

515

e515@gmail.com

5

515

95

So far we have seen examples of joining 2 tables, similarly we can also join 3 or more tables using equi join or natural join.

Let’s look at an example for 3 tables named products_list, products_details, and brands_details.

table: product_list

identity

Product Name

1

mobile

2

laptop

3

watch

table: product_details

identity

brand

made in

1

samsung

India

2

Ditch

America

table: brand_details

brand

Know

samsung

XYZ

Ditch

a B C D

Equi join on 3 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 get are,

identity

Product Name

brand

made in

Know

1

mobile

samsung

India

XYZ

2

laptop

Ditch

America

a B C D

conclusion

  • First of all, we had seen what is Equi join, Equi join is used to retrieve data from multiple tables using equality condition with where and as well as clause.
  • Equi join syntax and some examples.
  • Next, we saw why this equi join is used 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, which is used to retrieve data from multiple tables using the other than operator, rather than an equality condition with where else clauses.
  • In natural join, table must have same column name to perform equality operation and result obtained in natural join must have common column only once.
  • To understand more about Natural Join and Equi Join, some examples have been discussed.
  • We can also use equi join for 3 tables and more tables.

Hope you got some knowledge from this blog 🙂

Leave a Comment