Microsoft® SQL Server™ 2000 Programming by Example

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 ...