SQL Interview Prep · Lesson

SELF JOIN for Hierarchies

Joining a table to itself to model employee-manager and parent-child relationships.

SELF JOIN for Hierarchies is a free SQL Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What a SELF JOIN Really Is

A self join is simply a join where a table appears on both sides. There is no special SELF JOIN keyword; you write a normal INNER or LEFT JOIN and reference the same table twice.

The trick that makes it work is table aliases. You give each copy a different alias so the engine treats them as two independent tables.

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

Why Aliases Are Mandatory

Without distinct aliases the query is ambiguous: every column name appears twice and the engine cannot tell which copy you mean. Aliasing each instance solves this.

Read the join as 'pair each employee row with the employee row that is its manager.' The alias e is the worker, m is the manager, and both come from the same physical table.

-- e = the employee, m = that employee's manager
SELECT e.id, e.name, m.name AS reports_to
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.id;

The Employee-Manager Model

The classic self-join scenario is an adjacency list: a single table stores rows, and each row points to its parent via a foreign key to the same table.

An employees table with a manager_id that references employees.id models an entire org chart in one table. Each manager is just another employee row.

-- One table holds the whole hierarchy
-- employees(id, name, manager_id)
-- manager_id -> employees.id

Listing Everyone With Their Manager

The most-asked self-join question: show each employee next to their manager's name. Join the employee copy to the manager copy on e.manager_id = m.id.

This returns one row per employee whose manager exists. Note that the very top of the org, the CEO, has a NULL manager_id and will be excluded by an inner join.

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

LEFT JOIN to Keep the Top of the Tree

To include the CEO (whose manager_id is NULL), switch to a LEFT JOIN. The employee side is preserved; the manager columns come back NULL for rows with no parent.

Interviewers use this to test whether you remember that an inner self join drops root nodes. The fix is the same as any outer-join 'keep unmatched rows' situation.

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

Counting Direct Reports Per Manager

A common follow-up: how many people report directly to each manager? Self join, then group by the manager.

We join workers to managers, group on the manager's identity, and count the workers. This counts only direct reports, not the entire sub-tree below them.

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

Going Two Levels Deep

To get an employee, their manager, and their manager's manager, chain three copies of the table. Each level is another self join.

This works for a fixed, known depth. If you need arbitrary depth, a self join is not enough, that is the cue for a recursive CTE, which interviewers expect you to mention.

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

Self Join vs Recursive CTE

Key distinction interviewers test:

A self join handles a fixed number of levels. Three copies = three levels, no more.
A recursive CTE handles unlimited depth by re-joining the table to itself until no new rows appear.

So 'show each employee and their direct manager' is a self join, but 'list every ancestor up the chain' needs recursion.

Parent-Child Categories

The same pattern models any tree: product categories, comment threads, geographic regions. A categories table with parent_id referencing its own id is identical in shape to the employee-manager case.

Recognizing that 'a table with a self-referencing foreign key' equals 'self join or recursion' is the reusable insight.

SELECT c.name AS category,
       p.name AS parent_category
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id;

Common Self-Join Mistakes

Watch for these in interviews:

Forgetting aliases, causing ambiguous-column errors.
Using INNER JOIN and silently dropping root rows (NULL parent).
Joining on the wrong direction: e.id = m.manager_id instead of e.manager_id = m.id.

Always state out loud which alias is the child and which is the parent before writing the ON.

When to Use a Self Join

Reach for a self join whenever a table relates rows to other rows in the same table:

Hierarchies with one fixed level of lookup (employee to manager).
Pairing or comparing rows of one table (covered next lesson).

If the relationship is recursive and unbounded, name a recursive CTE as the better tool. That nuance separates juniors from mid-levels.

Quick Check

Test your grasp of self joins on hierarchies.

Recap: SELF JOIN for Hierarchies

Key takeaways:

A self join is a normal join with the same table on both sides, distinguished by aliases.
An adjacency list (self-referencing foreign key like manager_id) models a tree in one table.
Use INNER JOIN for matched pairs; LEFT JOIN to keep root rows with NULL parents.
Self joins handle a fixed depth; unbounded traversal needs a recursive CTE.

Frequently asked questions

Is the “SELF JOIN for Hierarchies” lesson free?

Yes — the full text of “SELF JOIN for Hierarchies” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “SELF JOIN for Hierarchies”?

Joining a table to itself to model employee-manager and parent-child relationships. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “SELF JOIN for Hierarchies” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep