SELF JOIN: Join a Table with Itself

A self join is a join where a table is joined with itself.
It is used when rows in the same table are related to each other, like an employee and their manager.

To tell the two copies of the table apart, you give each one a table alias (a short name).


Syntax

SELECT a.column, b.column
FROM table_name AS a
JOIN table_name AS b
ON a.common_column = b.related_column;

Example Scenario

For this lesson, we use a table called staff. Each person has a manager_id, which is the emp_id of their manager:

SELECT * FROM staff;

Output:

 emp_id |  name   | manager_id 
--------+---------+------------
      1 | Alice   |           
      2 | Bob     |          1
      3 | Charlie |          1
      4 | David   |          2
      5 | Emma    |          2
(5 rows)

Here:

  • Alice is the top manager. Nobody manages her, so her manager_id is NULL.
  • Bob and Charlie report to Alice (manager_id 1).
  • David and Emma report to Bob (manager_id 2).

Example Query

List every person with their manager's name:

SELECT e.name AS employee, m.name AS manager
FROM staff AS e
LEFT JOIN staff AS m
ON e.manager_id = m.emp_id
ORDER BY e.emp_id;

Output:

 employee | manager 
----------+---------
 Alice    | 
 Bob      | Alice
 Charlie  | Alice
 David    | Bob
 Emma     | Bob
(5 rows)
  • The staff table is used twice: once as e (the employee) and once as m (the manager).
  • We used LEFT JOIN so that Alice is still in the result. She has no manager, so manager is NULL.

When to Use a Self Join?

When a table has rows that point to other rows in the same table, for example:

  • Employees and their managers
  • Categories and their parent categories
  • People and the people who invited them

In the exercise, join the staff table with itself to pair managers with their team.