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:
SELECT *FROM TableName1, TableName2WHERE TableName1.ColumnName = TableName2.ColumnName;-- ORSELECT *FROM TableName1JOIN TableName2ON TableName1.ColumnName = TableName2.ColumnName;
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
RELATED TAGS
