Using Self-Joins
A table can be joined to itself in a self-join. Use a self-join when you want to create a result set that joins records in a table with other records in the same table. To list a table two times in the same query, you must provide a table alias for at least one of instance of the table name. This table alias helps the query processor determine whether columns should present data from the right or left version of the table.
Examples
A. Using a self-join to find the products supplied by multiple vendors
The following example uses a self-join to find the products that are supplied by more than one vendor.
Because this query involves a join of the ProductVendor table with itself, the ProductVendor table appears in two roles. To distinguish these roles, you must give the ProductVendor table two different aliases (pv1 and pv2) in the FROM clause. These aliases are used to qualify the column names in the rest of the query. This is an example of the self-join Transact-SQL statement:
USE AdventureWorks;
GO
SELECT DISTINCT pv1.ProductID, pv1.VendorID
FROM Purchasing.ProductVendor pv1
INNER JOIN Purchasing.ProductVendor pv2
ON pv1.ProductID = pv2.ProductID
AND pv1.VendorID <> pv2.VendorID
ORDER BY pv1.ProductID
B. Using a self-join to match employees and their managers
The following example performs a self-join of the HumanResources.Employee table to produce a list of all the managers and the employees that report to them.
SELECT MgrTable.LoginID AS ManagerName,EmplTable.ManagerID,
EmplTable.LoginID, EmplTable.EmployeeID
FROM HumanResources.Employee AS EmplTable
JOIN HumanResources.Employee AS MgrTable
ON EmplTable.ManagerID = MgrTable.EmployeeID
ORDER BY MgrTable.LoginID, EmplTable.LoginID