SQL Self Joins: Employee-Manager and Hierarchical Data Explained

What Is a Self Join?

A self join is a JOIN where a table is joined with itself, treated as two separate tables through table aliases. It looks unusual the first time you see it, but it's the standard way to answer any question that involves a relationship within a single table — the most common example being an employees table where each row has a manager_id that points to another row in the same table.

Self joins show up constantly in interviews because they test whether you actually understand how JOIN mechanics work, rather than just pattern-matching a memorized query shape.

The Sample Data

-- employees: each row optionally points to its manager via manager_id
| id | name    | manager_id |
|----|---------|------------|
| 1  | Sarah   | NULL       |  -- CEO, no manager
| 2  | James   | 1          |
| 3  | Priya   | 1          |
| 4  | Wei     | 2          |
| 5  | Fatima  | 2          |
| 6  | Diego   | 3          |

Basic Self Join: Employee and Their Manager

Alias the same table twice — once representing the employee, once representing the manager — and join them on manager_id = id.

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

-- Result:
-- | employee | manager |
-- |----------|---------|
-- | Sarah    | NULL    |
-- | James    | Sarah   |
-- | Priya    | Sarah   |
-- | Wei      | James   |
-- | Fatima   | James   |
-- | Diego    | Priya   |

Use LEFT JOIN, not INNER JOIN. The CEO (Sarah) has no manager — an INNER JOIN would silently exclude her from the result, which is almost never what you want. This is one of the most common self-join mistakes in interviews.

Counting Direct Reports

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

-- Result:
-- | manager | direct_reports |
-- |---------|----------------|
-- | Sarah   | 2              |
-- | James   | 2              |
-- | Priya   | 1              |
-- | Wei     | 0              |
-- | Fatima  | 0               |
-- | Diego   | 0              |

Notice the join direction flips compared to the previous query — here m (managers) is the anchor table and we LEFT JOIN e (their reports), which is why individual contributors like Wei show 0 rather than disappearing from the result.

Walking the Full Hierarchy: Recursive Self Join

A plain self join only gets you one level up or down the hierarchy. To find every level — an employee's manager, their manager's manager, and so on — you need a recursive CTE, which is effectively a self join that repeats until it runs out of rows to match.

WITH RECURSIVE org_chart AS (
  -- anchor: top of the hierarchy (no manager)
  SELECT id, name, manager_id, 1 AS level, name::text AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- recursive step: join each new level back to the growing result
  SELECT e.id, e.name, e.manager_id, oc.level + 1, oc.path || ' > ' || e.name
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;

-- Result includes level depth and a readable path:
-- | id | name   | level | path                    |
-- |----|--------|-------|-------------------------|
-- | 1  | Sarah  | 1     | Sarah                   |
-- | 2  | James  | 2     | Sarah > James           |
-- | 3  | Priya  | 2     | Sarah > Priya           |
-- | 4  | Wei    | 3     | Sarah > James > Wei     |
-- | 5  | Fatima | 3     | Sarah > James > Fatima  |
-- | 6  | Diego  | 3     | Sarah > Priya > Diego   |

The path column is a common "make the output presentable" addition — building a readable breadcrumb trail is a good way to show an interviewer you're thinking about the consumer of the query, not just the mechanics.

Finding All Reports Under a Manager (Direct + Indirect)

A frequent follow-up: "find every employee who ultimately reports up to James, at any depth." This is the same recursive pattern, just seeded from a specific person instead of the top of the tree.

WITH RECURSIVE reports AS (
  SELECT id, name, manager_id
  FROM employees
  WHERE id = 2  -- James
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN reports r ON e.manager_id = r.id
)
SELECT * FROM reports WHERE id != 2;  -- exclude James himself

Self Joins Beyond Org Charts

Employee-manager hierarchies are the textbook example, but the same technique applies anywhere a table references itself:

• Comparing rows within the same table — e.g. finding pairs of products in the same category with different prices
• Finding duplicate or near-duplicate rows — self-joining on a shared attribute (see our guide to finding duplicates for dedicated patterns)
• Sequential comparisons — comparing each row to the row before/after it, though LAG()/LEAD() window functions are usually a cleaner modern alternative to a self join for this specific case
• Category trees / bill-of-materials data — the same parent-child pattern as an org chart, just modeling products or categories instead of people

-- Find pairs of products in the same category with a price difference over $50
SELECT p1.name AS product_a, p2.name AS product_b, p1.category, ABS(p1.price - p2.price) AS price_diff
FROM products p1
JOIN products p2 ON p1.category = p2.category AND p1.id < p2.id
WHERE ABS(p1.price - p2.price) > 50;

The p1.id < p2.id condition is a standard trick to avoid comparing a row to itself and to avoid returning each pair twice (A-B and B-A) — a detail interviewers specifically look for.

Common Mistakes

• Using INNER JOIN when the top-level row has a NULL parent — silently drops the root node (the CEO, the top-level category, etc.)
• Forgetting distinct aliases — since it's the same physical table, every reference needs a clear alias, or the query won't compile or will be ambiguous.
• Missing the self-comparison guard (p1.id != p2.id or p1.id < p2.id) when comparing rows within the same table — without it, every row matches itself.
• Trying to get full hierarchy depth from a single (non-recursive) self join — a plain self join only reaches one level; anything deeper needs a recursive CTE.

Common Interview Questions on This Pattern

1. Write a query to list each employee with their manager's name. The basic self join shown above — remember LEFT JOIN to keep the top of the hierarchy.
2. How would you find employees with no manager? WHERE manager_id IS NULL — no join needed at all.
3. How would you find the total headcount under each manager, including indirect reports? Recursive CTE from each manager downward, then count distinct employees returned per starting manager.
4. What's the difference between a self join and a recursive CTE, and when do you need each? A self join answers "one level" questions (who is X's direct manager?). A recursive CTE is needed whenever the depth of the hierarchy is unknown or arbitrary (who are all of X's reports, at any level?).

Frequently Asked Questions

Can a self join cause performance problems?

Yes, on large tables — you're effectively joining a table against itself, so make sure the join column (manager_id in this example) is indexed. Recursive CTEs on very deep or wide hierarchies can also be expensive; most databases let you cap recursion depth as a safeguard during development.

Is a self join a special kind of JOIN syntax?

No — there's no special SELF JOIN keyword. It's a regular INNER or LEFT JOIN where both sides happen to reference the same table under different aliases.

Practice This Pattern

Hierarchical data comes up constantly in real schemas — org charts, category trees, comment threads, bill-of-materials. Try it hands-on with our SQL practice questions, or work through a full case study in our case studies. For more on recursive CTEs, see how they're used in our gaps and islands guide.