What is an equi join in SQL?
The join clause is used to combine tables based on a common column and a join condition. An equi join is a type of join that combines tables based on matching values in specified columns.
Please remember that:
- The column names do not need to be the same.
- The resultant table contains repeated columns.
- It is possible to perform an equi join on more than two tables.
Syntax
There are two ways to use equi join in SQL:
In the first method, after the SELECT keyword, the names of the columns that are to be included in the result of the query are specified. The * operator is used if all the columns need to be selected. After the FROM keyword, the tables which need to be joined are specified. In the WHERE clause, the table and column names are specified along with an = operator.
In the second method, the JOIN keyword is used to join the tables based on the condition provided after the ON keyword.
Example
The following tables have been created:
1. product_list
| ID | Pname |
|---|---|
| 1 | Apples |
| 2 | Oranges |
| 3 | Mangoes |
2. product_details
| ID | Brand | Origin |
|---|---|---|
| 1 | Fresh Foods | USA |
| 2 | Angro Ltd | Pakistan |
3. brand_details
| Brand | OfficeAddress |
|---|---|
| Fresh Foods | 123 Seattle USA |
| Angro Ltd | 124 Lahore |
/*Performing the equi join with two tables*/SELECT *FROM product_listJOIN product_detailsON product_list.ID = product_details.ID;/*Performing the equi join with three tables*/SELECT product_list.ID, product_list.Pname,product_details.Brand, product_details.Origin,brand_details.OfficeAddressFROM product_list, product_details, brand_detailsWHERE product_list.ID = product_details.IDand product_details.Brand = brand_details.Brand
Unlock your potential: SQL joins series, all in one place!
To continue your exploration of SQL joins, check out our series of Answers below:
Different types of SQL joins
Understand the different types of SQL joins used for combining data from multiple tables.What is inner join in SQL?
Learn how inner joins return matched rows from both tables in SQL.What is outer join in SQL?
Explore outer joins and how they return unmatched rows as well.What is a self join in SQL?
Discover how self joins allow a table to join with itself.What is a natural join in SQL?
Learn how natural joins match columns with the same name in SQL tables.What is a hash join in SQL?
Understand hash joins, which are used for large-scale data matching and combining.What is the SQL CROSS JOIN?
Explore the SQL CROSS JOIN to combine every row from one table with every row from another.What is an equi join in SQL?
Learn how equi joins match rows based on the equality of values between tables.How to join 3 or more tables in SQL
Master the technique of joining multiple tables in a SQL query for complex data retrieval.
Free Resources