Self Joins

A table can be joined to itself. Nothing special is required — you alias it twice and the database treats the two aliases as two tables.

The classic case is a hierarchy stored in one table. employees.manager_id points at another row of employees:

SQL▶ Run
SELECT e.first_name        AS employee,
       m.first_name        AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id
ORDER BY e.emp_id;

e is the employee, m is the same table playing the part of the manager. Without the aliases the query could not be written at all: employees.emp_id = employees.manager_id refers to one thing twice.

Why LEFT, not INNER

Two people here have no manager — they are the top of the tree. An INNER JOIN would drop them:

SQL▶ Run
SELECT COUNT(*) AS everyone
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id;

SQL▶ Run
SELECT COUNT(*) AS only_those_with_a_manager
FROM employees e
JOIN employees m ON m.emp_id = e.manager_id;

Ten against eight. Losing the chief executive from the org chart is exactly the kind of error that survives review, because the output looks entirely reasonable. Whenever the join key is nullable — and manager_id is nullable by design, as somebody has to be at the top — reach for LEFT.

Counting through the self join

SQL▶ Run
SELECT m.first_name        AS manager,
       COUNT(e.emp_id)     AS reports
FROM employees m
JOIN employees e ON e.manager_id = m.emp_id
GROUP BY m.emp_id, m.first_name
ORDER BY reports DESC, manager;

Read the ON carefully. Here m is the manager and e is the report, the reverse of the first query — the same table, the same column, the roles swapped. Getting this backwards produces a plausible, wrong answer, so name the aliases for the roles rather than the table.

Grouping by m.emp_id as well as m.first_name matters: two managers could share a first name, and grouping by the name alone would merge them. Group by the key.

Comparing rows to other rows

Self joins are not only for hierarchies. Any "find rows related to other rows in the same table" question is one:

SQL▶ Run
SELECT a.name AS customer, b.name AS duplicate_of, a.email
FROM customers a
JOIN customers b
  ON lower(a.name) = lower(b.name)
 AND a.customer_id < b.customer_id;

The duplicate customer the dataset contains, found by joining the table to itself on a normalised name. The a.customer_id < b.customer_id condition does two jobs: it stops every row matching itself, and it returns each pair once rather than twice. Without it you would get every row paired with itself plus both orderings of every real pair.

Same shape, different question — colleagues in the same department:

SQL▶ Run
SELECT a.first_name AS person, b.first_name AS colleague, a.dept_id
FROM employees a
JOIN employees b ON b.dept_id = a.dept_id AND a.emp_id < b.emp_id
ORDER BY a.dept_id, a.emp_id, b.emp_id;

Deeper hierarchies

A self join goes exactly one level. For an employee's manager's manager you join a third time; for "everyone below this person, however deep" a plain join cannot do it at all — that needs a recursive CTE (WITH RECURSIVE), which the advanced module covers. If you find yourself writing a fourth self join, that is the signal.

SQL▶ Run
SELECT e.first_name AS employee,
       m.first_name AS manager,
       g.first_name AS managers_manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id
LEFT JOIN employees g ON g.emp_id = m.manager_id
ORDER BY e.emp_id;

Common mistakes

• INNER JOIN on a nullable key — silently drops the top of the hierarchy.
• Reversing the ON — reports and managers swap, and the result still looks fine.
• No inequality condition — every row matches itself and pairs appear twice.
• Grouping by the name instead of the key — merges two people who share one.

Interview question

Print each employee alongside their manager's name, including employees who have no manager.

LEFT JOIN employees m ON m.emp_id = e.manager_id. The interviewer is checking two things: that you alias the table twice, and that you reach for LEFT because manager_id is nullable at the top of the tree.

Check yourself

1. Why must a self join use aliases?
2. What does a.id < b.id accomplish in a self join, and what happens without it?
3. When does a self join stop being enough for a hierarchy?