How to Querying Data using Joins in Sql Programming: Part 4

January 31, 2014 , , , 0 Comments

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.

Equi join sql programming

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.

Self join sql programming

Rhett Butler

Some say he’s half man half fish, others say he’s more of a seventy/thirty split. Either way he’s a fishy bastard. Google