Self Joins

A self join is a special type of join, in which a certain table is joined to itself. Basically, in a self join, two copies of the same table are merged, generating a result set based on information stored in this table.

Generally, self joins are used to represent hierarchies in a table. For example, the Employees table has a column called reportsto, which has a foreign key pointing to the employeeid column in this table. Therefore, if you want to retrieve the manager of any employee, the Employees table must be joined to itself.

Listing 5.20 demonstrates how to extract information from this hierarchy represented in the Employees table, using a self join. Specifically, the query performs a self join to retrieve the name of Anne Dodsworth's manager (her manager is also an employee).

Code Listing 5.20. Using a Self Join to Retrieve Hierarchy Information

					
USE Northwind

SELECT E1.employeeid, E1.firstname, E1.lastname,
       E2.firstname as managerfirstname, E2.lastname as managerlastname
FROM Employees E1 JOIN Employees E2
ON E1.reportsto = E2.employeeid
WHERE E1.lastname = 'Dodsworth'
AND E1.firstname = 'Anne'
GO

employeeid  firstname  lastname             managerfirstname managerlastname
----------- ---------- -------------------- ---------------- ----------------
9           Anne       Dodsworth            Steven           Buchanan

(1 row(s) affected)