Lesson 32 of 61

5 mins read

SQL SELF JOIN: Hierarchies, Trees & Parent-Child Relationships

In Plain English

A `SELF JOIN` is a regular join in which a table is joined with ITSELF. It is used to query hierarchical data stored in a single table, such as organizational charts (employee -> manager), category subtrees (parent category -> subcategory), or graph network edges.

The Mental Model

"A self-join is like looking up family relationships in a single genealogy register: you find a person's entry, note their parent's ID number, and look up that same parent's name in another row of the same book."

Deep Dive: How It Works

Mandatory Table Aliases: Because both sides of the join reference the same table name, distinct aliases (e.g. `emp` and `mgr`) are strictly required to resolve column ambiguity.

Hierarchical Adjacency List: The `manager_id` column stores the primary key `id` of another row in the same `employees` table.

Preserving Top of Hierarchy (CEO): Always use `LEFT JOIN` so the CEO/Root (who has `manager_id IS NULL`) is not dropped from results.

Syntax Blueprint

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

Join the table to itself using two distinct aliases (e for employee, m for manager).

Core Rules to Remember

Distinct Aliases: Mandatory aliases (e.g. e and m) differentiate the two roles of the same table.

Hierarchical Trees: Models parent-child, supervisor-worker, and nested category relationships.

Use LEFT JOIN for Roots: Guarantees root records with NULL parent IDs remain visible.

Common Beginner Traps & How to Fix Them

Mistake:Using `INNER JOIN` in a self-join and wondering why the CEO / Top Manager disappeared.

Why it happens: The CEO has `manager_id = NULL`, which fails the INNER JOIN equality condition.

How to fix: Use `LEFT JOIN` on the manager relationship.

Pro Tip: For deep multi-level hierarchies (e.g. 5+ nested levels), use Recursive Common Table Expressions (`WITH RECURSIVE`).

Live Interactive Example

Hit Run Code to see it live

Querying Organizational Management Hierarchy with Self-Join

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: The self-join resolved manager IDs to names while preserving Grace Hopper as root.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Self-Join Category Sub-trees

Select `c.name AS subcategory`, `p.name AS parent_category` from `categories c LEFT JOIN categories p ON c.parent_id = p.id` ordered by `c.id`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.