This article provides an overview of the self join in SQL, as well as a basic example.

What is a Self Join?

The SQL SELF JOIN is joining a table to itself. It can be useful for querying hierarchical data within the same table, or for comparing rows within the same table.

Example 1 – Self Left Join

A classic example of a self join is in an Employees table. In such a table, one employee might report to another employee. Therefore, you could use a self join to join the table on its employee ID column and manager ID column.

Suppose we have the following table:

+--------------+-------------+------------+-------------+
| EmployeeId   | FirstName   | LastName   | ReportsTo   |
|--------------+-------------+------------+-------------|
| 1            | Homer       | Connery    | NULL        |
| 2            | Bart        | Pitt       | 1           |
| 3            | Maggie      | Griffin    | 1           |
| 4            | Peter       | Farnsworth | 2           |
| 5            | Marge       | Morrison   | NULL        |
| 6            | Lisa        | Batch      | 5           |
| 7            | Dave        | Zuckerberg | 6           |
| 8            | Vlad        | Cook       | 7           |
+--------------+-------------+------------+-------------+

We can do a self left join on this table to return all employees and their managers.

SELECT
    CONCAT(e1.FirstName, ' ', e1.LastName) AS Employee,
    CONCAT(e2.FirstName, ' ', e2.LastName) AS Manager
FROM Employees e1
LEFT JOIN Employees e2 
ON e1.ReportsTo = e2.EmployeeId;

Result:

+------------------+-----------------+
| Employee         | Manager         |
|------------------+-----------------|
| Homer Connery    |                 |
| Bart Pitt        | Homer Connery   |
| Maggie Griffin   | Homer Connery   |
| Peter Farnsworth | Bart Pitt       |
| Marge Morrison   |                 |
| Lisa Batch       | Marge Morrison  |
| Dave Zuckerberg  | Lisa Batch      |
| Vlad Cook        | Dave Zuckerberg |
+------------------+-----------------+

Homer Connery and Marge Morrison don’t report to anyone and so their Manager field is blank. Actually, it would be NULL if I hadn’t performed a string concatenation on the two columns.

Example 2 – Self Inner Join

If we didn’t want the two head honchos to be returned, then we could do an inner join on the table. This will eliminate any rows that don’t have a match in both tables (i.e. those employees that don’t have a corresponding manager and vice versa).

SELECT
    CONCAT(e1.FirstName, ' ', e1.LastName) AS Employee,
    CONCAT(e2.FirstName, ' ', e2.LastName) AS Manager
FROM Employees e1
INNER JOIN Employees e2 
ON e1.ReportsTo = e2.EmployeeId;

Result:

+------------------+-----------------+
| Employee         | Manager         |
|------------------+-----------------|
| Bart Pitt        | Homer Connery   |
| Maggie Griffin   | Homer Connery   |
| Peter Farnsworth | Bart Pitt       |
| Lisa Batch       | Marge Morrison  |
| Dave Zuckerberg  | Lisa Batch      |
| Vlad Cook        | Dave Zuckerberg |
+------------------+-----------------+

Example 3 – Self Right Join

If we wanted to do a self right join, we would need to shuffle a few columns around in the query.

SELECT
    CONCAT(e1.FirstName, ' ', e1.LastName) AS Manager,
    CONCAT(e2.FirstName, ' ', e2.LastName) AS Employee
FROM Employees e1
RIGHT JOIN Employees e2
ON e1.EmployeeId = e2.ReportsTo;

Result:

+-----------------+------------------+
| Manager         | Employee         |
|-----------------+------------------|
|                 | Homer Connery    |
| Homer Connery   | Bart Pitt        |
| Homer Connery   | Maggie Griffin   |
| Bart Pitt       | Peter Farnsworth |
|                 | Marge Morrison   |
| Marge Morrison  | Lisa Batch       |
| Lisa Batch      | Dave Zuckerberg  |
| Dave Zuckerberg | Vlad Cook        |
+-----------------+------------------+