0Pricing
SQL Academy · Lesson

Limits of Self Joins

When you need recursion instead.

Limits of Self Joins is a free SQL Academy lesson on CoddyKit — lesson 4 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 Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is a Self Join?

A self join is when a table is joined to itself. It is useful for comparing rows within the same table, such as finding employees and their managers stored in a single employees table.

Before exploring its limits, let us remind ourselves how a basic self join works in practice.

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

One Level Deep

A self join handles one hop in a hierarchy elegantly. If you want each employee paired with their direct manager, one self join is all you need.

This works perfectly when your data is only one level deep or when you only care about direct parent-child relationships.

SELECT child.name AS employee, parent.name AS direct_manager
FROM employees child
LEFT JOIN employees parent ON child.manager_id = parent.id;

Two Levels: Already Getting Messy

What if you need employees, their managers, and their managers' managers? You must add a second self join. The query grows and becomes harder to read.

Each additional level of hierarchy requires one more join alias and one more JOIN clause.

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

Three Levels: The Pattern Breaks Down

Adding a third level forces yet another join. By now the query is verbose, fragile, and difficult to maintain. If the hierarchy depth changes, you must rewrite the entire query.

This is the first major limit of self joins: they do not scale with depth.

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

Unknown Depth: Self Joins Cannot Help

In real-world organizational charts or category trees, the depth is often unknown at query time. Self joins require you to hard-code the number of levels. If the hierarchy is 10 levels deep tomorrow, your 3-level self join query silently misses data.

This is a fundamental limitation: self joins cannot traverse an arbitrary number of levels.

-- This only retrieves up to 3 levels deep.
-- Employees deeper than level 3 are simply missing from results.
SELECT e.name, m.name, gm.name
FROM employees e
LEFT JOIN employees m  ON e.manager_id = m.id
LEFT JOIN employees gm ON m.manager_id = gm.id;

Cycles Break Self Joins Completely

Another serious limitation: if the data contains a cycle (A manages B, B manages C, C manages A), a self join query does not loop infinitely, but it also does not detect or report the cycle correctly.

You cannot guard against circular references using plain self joins. Recursive queries have built-in cycle detection mechanisms that self joins lack entirely.

-- Cyclic data: row 3 points back to row 1
-- id | name    | manager_id
--  1 | Alice   | 3   <-- cycle!
--  2 | Bob     | 1
--  3 | Charlie | 2

-- A self join just shows one hop; it cannot detect the loop
SELECT e.name, m.name AS reports_to
FROM employees e
JOIN employees m ON e.manager_id = m.id;

Introducing Recursive CTEs

SQL provides a purpose-built solution for traversing hierarchies of unknown depth: the recursive Common Table Expression (CTE). It uses the WITH RECURSIVE syntax supported by PostgreSQL, MySQL 8+, SQLite, and SQL Server.

A recursive CTE has two parts: an anchor member (the starting rows) and a recursive member (the step that follows each relationship).

WITH RECURSIVE org_tree AS (
  -- Anchor: start with the top-level CEO (no manager)
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: find each employee whose manager is already in org_tree
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT name, depth FROM org_tree ORDER BY depth;

Tracking the Full Path

One powerful feature of recursive CTEs is that you can accumulate context as you descend. For example, you can build the full path from the root to each node — something completely impossible with a static self join.

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id,
         name AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id,
         ot.path || ' > ' || e.name
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT name, path FROM org_tree ORDER BY path;

Self Join vs Recursive CTE: When to Choose

Use a self join when:

  • You need exactly one or two levels of hierarchy.
  • The depth is fixed and known in advance.
  • You want simplicity with no CTE overhead.

Use a recursive CTE when:

  • Depth is variable or unknown.
  • You need the full ancestry or descendant path.
  • You want cycle detection via CYCLE clause or manual guards.

Performance Considerations

Self joins on indexed columns are extremely fast for fixed-depth queries. Each join is a single lookup and the database optimiser handles it well.

Recursive CTEs are more flexible but can be expensive on deep or wide trees. Always add a depth limit guard in the recursive member to prevent runaway queries caused by bad data or unexpected cycles.

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
  WHERE ot.depth < 10   -- safety guard: stop at depth 10
)
SELECT name, depth FROM org_tree;

Real-World Use Cases Needing Recursion

Many common data models require arbitrary-depth traversal that self joins simply cannot handle:

  • Category trees — nested product categories in an e-commerce catalog.
  • Bill of materials — a product made of parts, each part made of sub-parts.
  • Comment threads — replies to replies to replies.
  • File system paths — directories inside directories.

In all these cases, reach for a recursive CTE rather than stacking self joins.

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, name AS full_path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id,
         ct.full_path || ' / ' || c.name
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id, name, full_path FROM category_tree ORDER BY full_path;

Knowledge Check

Test your understanding of the limits of self joins and when to use recursive CTEs instead.

Lesson Recap

In this lesson you learned the limits of self joins for hierarchical data:

  • Self joins work well for one or two fixed levels of hierarchy.
  • Each additional level requires another explicit JOIN, making queries fragile and hard to maintain.
  • Self joins cannot handle unknown depth — rows beyond the hard-coded levels are silently excluded.
  • They offer no protection against cyclic references in the data.
  • When depth is variable or unknown, use a recursive CTE (WITH RECURSIVE) instead.
  • Always add a depth guard in recursive queries to protect against runaway execution.

Knowing when to switch from a self join to a recursive CTE is a key skill for querying any tree-structured data in SQL.

Frequently asked questions

Is the “Limits of Self Joins” lesson free?

Yes — the full text of “Limits of Self Joins” is free to read here on the web, and the SQL Academy 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 Academy course, upgrade to CoddyKit PRO.

What will I learn in “Limits of Self Joins”?

When you need recursion instead. You practise SQL Academy 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 Academy?

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

How long does the “Limits of Self Joins” 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 Academy lesson?

Yes. Every SQL Academy 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

  1. What Is a Self Join
  2. Employees and Managers
  3. Comparing Rows in the Same Table
  4. Limits of Self Joins
← Back to SQL Academy