w3resource

SQL Equi Join

What is Equi Join in SQL?

SQL EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables. An equal sign (=) is used as comparison operator in the where clause to refer equality.

You may also perform EQUI JOIN by using JOIN keyword followed by ON keyword and then specifying names of the columns along with their associated tables to check equality.

Pictorial presentation of SQL Equi Join:

Pictorial representation of Sql equijoin

Syntax:

SELECT column_list 
FROM table1, table2....
WHERE table1.column_name =
table2.column_name; 

or

SELECT *
FROM table1 
JOIN table2
[ON (join_condition)]

Example:

Here is an example of Equi Join in SQL.

Sample table: agents


Sample table: customer


To get agent name column from agents table and cust name and cust city columns from customer table after joining said two tables with the following condition -

1. working area of agents and customer city of customer table must be same,

the following SQL statement can be used:

SQL Code:

SELECT agents.agent_name,customer.cust_name,
customer.cust_city
FROM agents,customer
WHERE agents.working_area=customer.cust_city;

Output:

AGENT_NAME                               CUST_NAME                                CUST_CITY
---------------------------------------- ---------------------------------------- ------------
Ravi Kumar                               Ravindran                                Bangalore
Ramasundar                               Ravindran                                Bangalore
Subbarao                                 Ravindran                                Bangalore
Ravi Kumar                               Srinivas                                 Bangalore
Ramasundar                               Srinivas                                 Bangalore
Subbarao                                 Srinivas                                 Bangalore
Ravi Kumar                               Rangarappa                               Bangalore
Ramasundar                               Rangarappa                               Bangalore
Subbarao                                 Rangarappa                               Bangalore
Ravi Kumar                               Venkatpati                               Bangalore
Ramasundar                               Venkatpati                               Bangalore
Subbarao                                 Venkatpati                               Bangalore
Anderson                                 Fleming                                  Brisban
Anderson                                 Jacks                                    Brisban
Anderson                                 Winston                                  Brisban
Santakumar                               Yearannaidu                              Chennai
...........
...........

What is the difference between Equi Join and Inner Join in SQL?

An equijoin is a join with a join condition containing an equality operator. An equijoin returns only the rows that have equivalent values for the specified columns.

An inner join is a join of two or more tables that returns only those rows (compared using a comparison operator) that satisfy the join condition.

Pictorial presentation : SQL Equi Join Vs. SQL Inner Join

Pictorial representation of Sql equijoin vs inner join

Key points to remember

Click on the following to get the slides presentation -

SQL JOINS, slide presentation

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: Introduction
Next: SQL NON EQUI JOIN



Follow us on Facebook and Twitter for latest update.