Recursive CTE

Hook: Recursive CTEs are the SQL version of climbing a ladder one rung at a time: you start with a base row, then keep reusing the same rule until there are no more rows to add.

Question: What is a Recursive CTE?

Answer: A recursive CTE is a query that refers to itself so SQL can walk a hierarchy or repeated pattern, such as managers and employees, folders and subfolders, or parts and subparts. It has two parts: an anchor query that starts the result, and a recursive query that keeps finding the next level until no new rows appear.

Interview-Ready Answer: A recursive CTE is a CTE that builds a result set in steps. I start with one or more anchor rows, then the recursive member joins those rows back to the base table to find the next level, and SQL repeats that until the query returns no more rows. I like it because it turns a looping problem into one declarative query, and in real systems I use it for hierarchies like org charts, category trees, and bill of materials.

What It Is

A CTE is a Common Table Expression: a named temporary result used inside one statement. A recursive CTE is special because the CTE name appears inside its own definition, letting SQL grow a result set one level at a time. In practice, you write an anchor member first, then a recursive member that uses the rows found so far.

How It Works Under the Hood

1. Run the anchor member first. This is the seed set: for an org chart, it might be the CEO or every top-level manager.
2. Store those rows in a working table. Think of a working table as SQL’s scratch pad: it holds the current wave of rows to expand.
3. Run the recursive member against the current wave. The recursive query joins the working rows back to the base table to find the next level down.
4. Append new rows to the final result. Most engines use UNION ALL so every discovered row is kept without duplicate elimination overhead.
5. Replace the working table with the new rows. SQL then repeats the same recursive step again, but now on the rows just found.
6. Stop when no new rows are produced. That empty result is the termination condition; it is the SQL equivalent of “there are no more children.”

If you remember only one thing, remember this: anchor = start, recursive member = next step, empty step = stop.

When and Why to Use It

• Hierarchies: org charts, folder trees, product categories, comments.
• Graphs with bounded expansion: dependency chains, bill of materials, linked records.
• Path building: creating breadcrumb strings like Home > Electronics > Phones.

Use a recursive CTE when the depth is not fixed ahead of time. If you only ever need two levels, a simple self-join is easier. If you need a whole tree, recursion is clearer and usually safer than writing many joins by hand.

Recursive CTE vs Other Options

Performance and Limits

With a good index on the parent key, a tree walk is often close to O(n) in the number of reachable rows, because each row is visited once per level. Without an index on the join column, the recursive step may scan a large table repeatedly and drift toward much worse performance. Recursive CTEs also materialize intermediate rows, so very wide trees can use noticeable memory and temp space.

Guardrails differ by database. SQL Server defaults to MAXRECURSION 100 unless you override it. MySQL 8+ has cte_max_recursion_depth, which defaults to 1000. PostgreSQL does not impose a small built-in level cap in the same way, so a bad cycle can keep going until you cancel the query or hit resource limits. Also, if you need deterministic output, add a final ORDER BY; recursion itself does not promise presentation order.

Important Edge Cases

• Cycles: if row A points to B and B points back to A, the query can loop forever unless the database has a limit or you add a guard.
• Orphans: rows whose parent does not exist will not appear in a tree query unless you handle them separately.
• Type matching: the anchor and recursive parts must return the same number of columns with compatible types.
• Duplicates: UNION ALL keeps duplicates; UNION removes them but can be slower and can hide repeated paths.

Mental model for interviews: SQL is not “calling a function” here. It is repeatedly taking the rows from the previous wave, applying one join step, and stopping only when the wave becomes empty.

Real-World Story

Imagine an e-commerce catalog service that builds category breadcrumbs and “show all products under this branch” views. The data looks like a tree: Electronics → Phones → Android. A recursive CTE lets the service start at the root category and walk downward until all descendants are collected, which is perfect for navigation menus and search filters.

What goes wrong when people misunderstand recursion? A bad import creates a cycle, such as Phones pointing to Android and Android pointing back to Phones. Suddenly the breadcrumb query never finishes, the database throws a recursion-limit error or times out, and the API starts returning 500s. In logs you might see “maximum recursion depth exceeded,” “statement timeout,” or repeated execution of the same join, while users report that category pages load forever or show incomplete navigation. That is why production teams add indexes, test for cycles, and sometimes cap recursion depth as a safety belt.

Follow-up & Tricky Questions:

• Why do we usually use UNION ALL instead of UNION? Because UNION ALL keeps every row and avoids duplicate-check work, which makes recursion faster. UNION can be useful if you truly need deduplication, but it adds extra sorting or hashing cost and can change the meaning of the tree.
• How does the database know when to stop? It keeps applying the recursive member until that step returns zero new rows. That empty wave is the stop signal.
• How do you prevent infinite recursion? Add real data checks for cycles, add a depth column with a cutoff, and use engine guardrails like SQL Server MAXRECURSION. The best fix is clean relational data, because a recursion limit only stops the damage.
• What index helps most? Index the column used to find children, usually manager_id or parent_id. That is the key lookup in almost every recursive step.
• How do you return the path from root to leaf? Carry a path column in the recursive CTE and concatenate the current name onto the prior path. This is a common interview twist because it proves you understand state across levels.
• Does a recursive CTE guarantee output order? No. The traversal order is not a presentation promise, so you should always add a final ORDER BY if the result needs to be stable.
• Can you use recursive CTEs for graphs, not just trees? Yes, but graphs are trickier because a node can be reached by many paths. You often need cycle checks or a visited set, otherwise the same node may repeat forever or explode in size.
• Can the anchor and recursive parts return different columns? No. They must line up in column count and compatible types, or the query fails. This is a common syntax mistake under interview pressure.
• Does UNION automatically make recursion safe? Not necessarily. It may suppress exact duplicates, but if your rows differ by depth or path, the cycle can still keep generating new distinct rows.
• Can you put ORDER BY inside the recursive member to control traversal? Usually not in the way people hope. The reliable place to sort is the final query, because recursion is about generating rows, not guaranteeing display order.

Tricky / Gotcha Questions:

• If the hierarchy has one broken parent pointer, will the whole recursive query fail? Usually no. The query simply will not reach that row from the anchor, so the row disappears from the tree output unless you query for orphans separately.
• If I only want descendants two levels deep, do I still need recursion? Not really. A self-join is simpler for fixed depth, and recursion becomes more valuable when the depth is unknown or variable.
• Is a recursive CTE the same as a loop in application code? Functionally it can solve the same problem, but the database executes it set by set, with its own working table and stop condition. That makes it declarative, easier to optimize in some cases, and often safer than shipping rows to the app one by one.

Common Mistakes:

• Using UNION by habit. Correction: prefer UNION ALL unless you have a real deduplication requirement.
• Forgetting a termination guard. Correction: make sure the recursive step eventually stops, either by data rules or a depth limit.
• Not indexing the parent lookup column. Correction: add an index on parent_id or manager_id so each step does not rescan the whole table.
• Assuming output order is automatic. Correction: add a final ORDER BY if the UI or report needs predictable ordering.

Memory Hook: Seed, climb, stop. The anchor is the seed, the recursive member is the climb, and the climb stops when no new rows appear.

Cheat Sheet:

• anchor member = starting rows.
• recursive member = step that finds the next level.
• UNION ALL is the usual choice for speed.
• Termination happens when the recursive step returns no rows.
• Index the parent key for performance.
• Add ORDER BY at the end if order matters.

Practice Tasks:

• Build an org chart query that prints each employee’s depth from the CEO.
• Modify the example to return only employees under Ben, not the whole tree.
• Add a path column like Ava > Ben > Diego and sort by that path.