Equi Join
In SQL programming, an Equi join is the same as an inner join and joins tables with the help of a foreign key. However, an equi join is used to display all the columns from both the tables. The common column from all the joining tables is displayed.Consider an example where you apply an equi join between the EmployeeDepartmentHistory, Employee, and Department tables by using a common column, BusinessEntityID. To perform this task, you can use the following query:
SELECT * FROM HumanResources.EmployeeDepartmentHistory d
JOIN HumanResources.Employee e ON d.BusinessEntityID = e.BusinessEntityID
JOIN HumanResources.Department p ON p.DepartmentID = d.DepartmentID
The output of this query displays the EmployeeID column from all the tables, as shown in the following figure.
Self Join
In a self-join, a table is joined with itself. As a result, one row in a table correlates with other rows in the same table. In a self-join, a table name is used twice in the query. Therefore, to differentiate the two instances of a single table, the table is given two alias names.The following example performs a self-join of the Sales.SalesPerson table to produce a list of all the territories and the sales people working in them.
SELECT st.Name AS TerritoryName, sp.BusinessEntityID, sp.SalesQuota, sp.SalesYTD
FROM Sales.SalesPerson AS sp JOIN Sales.SalesTerritory AS st
ON sp.TerritoryID = st.TerritoryID
ORDER BY st.Name, sp.BusinessEntityID
The output of the self-join is shown in the following figure.
