What Is a Self Join and When Is It Useful?

A self join is a join in which a table appears more than once, with a different alias for each appearance. The join condition relates the two copies to each other rather than to another table. It is the standard technique for three families of problem.

First, hierarchies. When a row points at another row in the same table through a parent key — employees and their managers, categories and their parents, accounts and their owners — a self join walks one level of the hierarchy, and a recursive CTE walks as many as you need. Second, duplicate detection: joining a table to itself on the business key and filtering for id inequality finds the pairs of rows that share a value. Third, comparison within a table: finding the previous or next row in a sequence, or finding every pair of rows where one is more expensive than another, is a self join on the same table with a self-referencing condition.

The one mechanical rule is that aliases are mandatory. When the same table appears twice, an unqualified column reference is ambiguous and the engine rejects the query. The alias is not a convenience; it is the only way to say which copy you mean. e.manager_id = m.id says "this copy's manager is that copy's id", which is the entire meaning of the query.

There is also a performance note. A self join is a join like any other, so the same rules apply about indexing the join key and about the row count multiplying: a parent with three children produces three rows per parent, so an aggregate over the result can be inflated unless you think about which copy you are counting.

Join the staff table to itself: one copy for the employee, one copy for the manager, matched on the manager id.

CREATE TABLE staff (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  title VARCHAR(80) NOT NULL,
  manager_id INTEGER
);

INSERT INTO staff (id, name, title, manager_id) VALUES
  (1, 'Ada Lovelace',   'Head of Research', NULL),
  (2, 'Grace Hopper',   'Director',        1),
  (3, 'Alan Turing',    'Researcher',      1),
  (4, 'Katherine Johnson', 'Mathematician', 2);

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

Four rows come back: Alan Turing managed by Ada Lovelace, Grace Hopper managed by Ada Lovelace, Katherine Johnson managed by Grace Hopper, and Ada Lovelace herself with a NULL manager, because her manager_id is NULL and the LEFT JOIN preserves her row.

Two decisions worth naming. The join is a LEFT JOIN rather than an INNER JOIN precisely so the top of the hierarchy survives — with an inner join Ada Lovelace would disappear, and the output would misleadingly suggest she reports to someone. And the ON clause is the only place the two copies are related; everything else refers to one alias or the other.

For a deeper hierarchy, a self join only gives you one level. To walk several levels you need a recursive CTE: an anchor query that selects the people with no manager, and a recursive part that joins the next level back to the previous one. That is the natural extension, and it is why the recursive CTE is the standard tool for organisation charts, category trees and bill-of-materials queries.

The other thing to watch with self joins is row multiplication. If a manager has three reports, that manager appears three times in the output. That is correct for a flat listing, but if you aggregate over the result you must be deliberate about which copy you are counting — counting DISTINCT manager ids gives you a headcount, while counting rows gives you a report count.