SQL: Self Joins, Unary Relationships, and Aliases

If you look at the Employees table in the Northwind database diagram you will see that there's a relationship that links to itself


And if you look the at the Employees create script you will see that the foreign key to is the ReportTo field referencing the Primary Key EmployeeID. This kind of self referencing is called a unary relationship.

ALTER TABLE [dbo].[Employees]  WITH NOCHECK ADD  CONSTRAINT [FK_Employees_Employees] FOREIGN KEY([ReportsTo])
REFERENCES [dbo].[Employees] ([EmployeeID])

So how do you query the employees who is manage by another employee? You can assign aliases to the same table so that you can query the same table as if it were two different tables.

SELECT e.EmployeeID,(e.FirstName + ' ' + e.LastName) AS Name, 
       (et.FirstName + ' ' + et.LastName) AS Supervisor
FROM Employees e, Employees et  
WHERE e.ReportsTo = et.EmployeeID

Here are the results:

As you can see from the select list, (e.FirstName + ' ' + e.LastName) AS Name displays the Employee's name, but (et.FirstName + ' ' + et.LastName) AS Supervisor displays the supervisor's name even though we are retrieving the same fields on the same table. This behavior is possible because we gave the same table two different aliases. As a general rule, you should replace subqueries with self joins, if you can because it performs better.