Equi Join in SQL
FREE
Overview
Equi join in SQL is a type of Inner Join in which two or more tables are joined by equating the common column values of the tables. The table participating in Equi join should have at least one common column on which equality operation can be performed.
What is Equi Join in SQL?
Equi join uses the equality operator(=) only to compare the values of the common column. Each common column value of one table is compared to each common column value of the other table and if the values of both the common columns of the table match, that row value of both the tables is retrieved in the result of Equi join in SQL.
The common column names in the tables performing Equi join need not be the same.
The names of the column which are equated in the Equi join can be of different names. Also, the resultant table of Equi join contains both columns of tables performing Equi join.
Syntax
The Equi join in SQL can be written with the WHERE clause and also the ON clause for equating the columns of the participating tables.
Example
- with WHERE clause
- with ON clause
Why Use Equi Join in SQL?
Equi join in SQL is used mostly to join two or more tables on the basis of equality of column values of the tables.
While working on web applications the data are stored in different tables in the database. So, if one needs to combine the data of two tables to get better data insights then the need for joining the table arises.
Equi join is one of the SQL Joins which can be used to join the tables for better data insights.
For example - Let's understand Equi join with the help of two tables.
- Products table
| id | product_name |
|---|---|
| 1 | Iwatch |
| 2 | IPhone |
| 3 | IPod |
| 4 | macbook |
- Discount table
| id | dicount_percentage |
|---|---|
| 2 | 20% |
| 4 | 17% |
Suppose we have a need to know the discounts available on the products then can we only know that by the Product table or the discount table?
No, we have to combine both the table to know what are the discounts available on the products.
With the help of inner join, we can join both the products table and the discount table to know on which products the discount exists.
The column on which we can perform Equi join is the id column in both tables.
The resultant table after performing Equi join on products and the discount table will be -
| id | product_name | id | discount_percentage |
|---|---|---|---|
| 2 | Iphone | 2 | 20% |
| 4 | macbook | 4 | 17% |
With the help of the resultant table of Equi join we are able to know that only products iPhone and MacBook have a discount of 20% and 17% respectively.
More Examples of Equi join in SQL
Let's see examples of Equi join on the following tables.
- Student table
| id | name | city |
|---|---|---|
| 1 | Rakesh | Delhi |
| 2 | Amit | Varanasi |
| 3 | Vipul | Gorakhpur |
| 4 | Tejan | Delhi |
| 5 | Shashank | Lucknow |
- Course table
| course_id | course_name | sid |
|---|---|---|
| 100 | AI | 3 |
| 101 | Machine learing | 4 |
| 102 | Java | 1 |
| 103 | Python | 5 |
| 100 | AI | 2 |
- College_Detail table
| college_name | Location | sid |
|---|---|---|
| UIET | Kanpur | 4 |
| IIT-D | Delhi | 1 |
| IIT-B | Mumbai | 5 |
| BBD | Lucknow | 3 |
| IIT-G | Guwahati | 2 |
Example 1
Find all the details of the student and the course they are enrolled in.
Result -
| id | name | city | course_id | course_name | sid |
|---|---|---|---|---|---|
| 1 | Rakesh | Delhi | 102 | java | 1 |
| 2 | Amit | Varanasi | 100 | AI | 2 |
| 3 | Vipul | Gorakhpur | 100 | AI | 3 |
| 4 | Tejan | Delhi | 101 | Machine learning | 4 |
| 5 | Shashank | Lucknow | 103 | Python | 5 |
Example 2
Find the all student name, city and the college name, and location of the college in which they studied.
Result -
| name | city | college_name | Location |
|---|---|---|---|
| Rakesh | Delhi | IIT-D | Delhi |
| Amit | Varanasi | IIT-G | Guwahati |
| Vipul | Gorakhpur | BBD | Lucknow |
| Tejan | Delhi | UIET | Kanpur |
| Shashank | Lucknow | IIT-B | Shashank |
Example 3
For all the students find the student name, course name and the coellge name in which they pursued the course.
Result -
| name | course_name | colege_name |
|---|---|---|
| Rakesh | Java | IIT-D |
| Amit | AI | IIT-G |
| Vipul | AI | BBD |
| Tejan | Machine learning | UIET |
| Shashank | Python | IIT-B |
How is Equi Join Different from Natural Join?
The difference between Equi join and natural join lies in column names which are equated for performing equi join or natural join.
In Natural join, the tables should have the same column names to perform equality operations on them. In Equi join, the common column name can be the same or different. Also in the resultant table of Equi join the common column of both the tables are present. But in the natural join, the common column is present only once in the resultant table.
If we would have performed natural join on the above products and discount table then the resultant table would be -
| id | product_name | discount_percentage |
|---|---|---|
| 2 | Iphone | 20% |
| 4 | macbook | 17% |
Conclusion
- Equi join in SQL combines two or more than two tables on the basis of equality or common column values.
- The common column name of the participating tables in Equi join need not be the same.
- Equi join in SQL can be written with WHERE as well as ON clause.
- Equi join has the column name of both the tables in the resulting table whereas natural join only has one of the column names in the resulting table.
- The common column name of participating tables in the natural join should be the same but the common colmn name me to be different in case if Equi joins.
Learn more about Equi-join and Non-Equi Join in SQL