SQL SELF JOIN: Join a Table to Itself (Employees and Managers)

A self join joins a table to itself. It sounds odd, but it is the natural way to answer questions about relationships between rows of the same table: employees and their managers, products and their related products, people who live in the same city.

There is no special SELF JOIN keyword. You write an ordinary join and give the same table two different aliases, so SQL treats them as two separate copies.

The example table#

employees

manager_id stores the id of another employee in the same table. Asha's manager is employee 5 (Priya).

Employees with their manager's name#

Think of it as two copies of the table: e for the employee and m for the manager:

SELECT e.name AS employee,
       m.name AS manager
FROM employees e
INNER JOIN employees m
  ON e.manager_id = m.id
ORDER BY e.id;

employee | manager
---------+--------
Asha     | Priya
Ravi     | Meera
John     | Priya

For each employee row e, the database finds the row m whose id equals e.manager_id.

Include people without a manager#

Meera and Priya have manager_id = NULL, so the inner join dropped them. A LEFT JOIN keeps everyone:

SELECT e.name AS employee,
       COALESCE(m.name, '(no manager)') AS manager
FROM employees e
LEFT JOIN employees m
  ON e.manager_id = m.id
ORDER BY e.id;

employee | manager
---------+-------------
Asha     | Priya
Ravi     | Meera
Meera    | (no manager)
John     | Priya
Priya    | (no manager)

Count each manager's direct reports#

Flip the perspective: start from the manager and join to the people who report to them:

SELECT m.name AS manager,
       COUNT(e.id) AS direct_reports
FROM employees m
JOIN employees e
  ON e.manager_id = m.id
GROUP BY m.id, m.name
ORDER BY direct_reports DESC;

manager | direct_reports
--------+---------------
Priya   | 2
Meera   | 1

Comparing rows: pairs in the same city#

A self join can also pair rows that share a value. Employees who live in the same city:

SELECT a.name AS employee_1,
       b.name AS employee_2,
       a.city
FROM employees a
JOIN employees b
  ON a.city = b.city
 AND a.id < b.id;

employee_1 | employee_2 | city
-----------+------------+-------
Asha       | John       | Mumbai

The extra condition a.id < b.id matters. Without it you would also get Asha paired with herself, and the same pair twice (Asha–John and John–Asha).

Beyond one level#

A self join climbs one level of a hierarchy (employee → manager). Going further (manager's manager, and so on) would need one more join per level. For hierarchies of any depth, use a recursive CTE.

Common mistakes#

Practice#

SELECT e.name,
       e.salary,
       m.name   AS manager,
       m.salary AS manager_salary,
       m.salary - e.salary AS difference
FROM employees e
JOIN employees m
  ON e.manager_id = m.id;

name | salary | manager | manager_salary | difference
-----+--------+---------+----------------+-----------
Asha | 52000  | Priya   | 61000          | 9000
Ravi | 78000  | Meera   | 81000          | 3000
John | 46000  | Priya   | 61000          | 15000

Key takeaway#