Last Update: May 15, 2023 | Published: May 12, 2023
A self join in SQL Server is a useful statement to use for demonstrating relationships within a single SQL Server table database. In this tutorial, I’ll show you when and how to use an SQL self join. To do that, I’ll be detailing an sql self join example where I’ll list the supervisors for all employees in the Northwind Employees sample table.
The self join is a special and somewhat uncommon type of SQL join. While most joins like the INNER JOIN link two or more different tables by their common columns, a self join links a table to itself. Unlike regular join operations, the same table is used as both the left and right tables in the join operation.
For example, you might use a self join to find all employees who have the same manager. Here you might match a manager’s ID with an employee id. Alternatively, you could use a self join to find all of customers who have orders for the same products.
The following query shows an example of the SQL code for a self join using the Northwind sample database. The table name is ‘Employees’ and as you might guess, it contains employee data.
USE Northwind SELECT e1.EmployeeID, e1.FirstName, e1.LastName, e1.ReportsTo, e2.FirstName as ManagerFirstName, e2.LastName as ManagerLastName FROM Employees e1 LEFT OUTER JOIN Employees e2 on e1.ReportsTo = e2.EmployeeID
This SQL self join example will show the names of the supervisors for each employee.
You can see the outcome of the self join query in the following image:
This query will return a result set that includes the EmployeeID, the FirstName, Last Name, ReportsTo (EmployeeID), the ManagerFirstName, and ManagerLastName details. If you examine the result set, you can see that Andrew Fuller has an EmployeeID of 2 and that he is also the supervisor for EmployeeID 1, 3, 4, 5 and 8. Andrew Fuller does not report to anyone else, therefore his ReportTo and ManagerFirstName and ManagerLastName are all NULL.
You should really use a self join when a SQL table references data in itself. As I explained in this article, a great use case for an SQL self join can be a table with employee data where each row contains information about employees and their managers. In that case, a self join lets you easily surface relevant information about how an organization works.