Self-Joins

A self-join joins a table to itself. It's needed when rows in a table refer to other rows in the same table — like employees and their managers.

In our employees table, manager_id holds the id of another employee:

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

+----------+---------+
| employee | manager |
+----------+---------+
| Sofia    | NULL    |
| Omar     | Sofia   |
| Priya    | Sofia   |
| Chen     | Sofia   |
| Lucia    | Chen    |
| Kofi     | Sofia   |
+----------+---------+

• The same table appears twice with different aliases: e for the employee and m for the manager.
• A LEFT JOIN keeps Sofia, who has no manager (her manager_id is NULL).

Comparing rows within a table

Self-joins also compare rows with each other — for example, employees who earn more than their manager:

SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary * 0.9;

+-------+--------+---------+----------------+
| name  | salary | manager | manager_salary |
+-------+--------+---------+----------------+
| Lucia |  64000 | Chen    |          70000 |
+-------+--------+---------+----------------+

(That's "more than 90% of their manager's salary" — nobody here earns more than their manager outright.)

For hierarchies of unknown depth — a manager's manager's manager — self-joins aren't enough. Level 3's recursive queries handle those.

Practice

Count how many people report directly to each manager.

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 |
+---------+----------------+
| Sofia   |              4 |
| Chen    |              1 |
+---------+----------------+