SQL Server Essentials: Using SQL Self Joins

Security

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. 

What is the SQL self join for?

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.

SQL self join example

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.

  • Here, we’re joining the Employees table to itself using the ‘ReportsTo’ column and the ‘EmployeeID’ column. As the supervisors are also employees, they are listed in the same Employees table.
  • In this case, the values in the ‘ReportsTo’ column must match the values in the ‘EmployeeID’ column.
  • In the result set the ‘FirstName’ and ‘LastName’ columns of the self-joined table will be renamed to ‘ManagerFirstName’ and ‘ManagerLastName’.
  • The EmployeeID table uses a table alias of ‘e1’ while the self-joined table uses a table alias of ‘e2’.
  • The actual SQL self join syntax is a LEFT OUTER JOIN. The ON clause specifies the columns that will be used in the self join.

You can see the outcome of the self join query in the following image:

SQL self join example
SQL self join example (Image credit: Petri/Michael Otey)

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.

When should you use an SQL self join?

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.