SQL Interview Prep · Lesson

Avoiding Infinite Recursion

Cycle detection, depth limits, and the recursion guard every interviewer checks for.

Avoiding Infinite Recursion is a free SQL Interview Prep 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 Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Question Behind the Question

After you write a recursive CTE, a sharp interviewer asks: "What happens if the data has a cycle?" This checks whether you understand that recursion can run forever — and whether you know how to guard against it.

A cycle is when the hierarchy loops back on itself: A reports to B, B reports to A. The naive recursive member will bounce between them indefinitely.

How a Cycle Forms

Trees are supposed to be acyclic, but real data is messy. A bad update can set an employee as their own (indirect) manager. A graph — like "users who follow users" — is cyclic by nature.

When the recursive member re-encounters a node it already visited, it produces that node again, which re-triggers its children, and the loop never empties. Recursion only stops when a step returns no rows; a cycle guarantees it always returns rows.

Guard 1: A Depth Limit

The simplest safety net is a depth counter with a cap in the recursive member. Even if a cycle exists, recursion stops at the limit.

This is a blunt instrument — it caps legitimate deep trees too — but it is quick and interview-friendly.

WITH RECURSIVE org 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, o.depth + 1
    FROM employees e JOIN org o ON e.manager_id = o.id
    WHERE o.depth < 50
)
SELECT * FROM org;

Guard 2: A Visited Path

A precise guard tracks the path of visited nodes and refuses to re-enter one already on the path. Accumulate ids into a string (or array) and check membership before recursing.

This stops cycles exactly while still allowing arbitrary depth on legitimate trees.

WITH RECURSIVE org AS (
    SELECT id, name, manager_id,
           CAST(',' || id || ',' AS VARCHAR(2000)) AS path
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id,
           o.path || e.id || ','
    FROM employees e JOIN org o ON e.manager_id = o.id
    WHERE o.path NOT LIKE '%,' || e.id || ',%'
)
SELECT id, name, path FROM org;

Why the Path Check Works

The condition path NOT LIKE '%,' || e.id || ',%' means "only follow this edge if the child id is not already in the path." The commas act as delimiters so id 1 does not falsely match inside id 15.

If a cycle would revisit a node, the WHERE filters that row out, the recursive member eventually returns nothing, and recursion terminates cleanly.

Guard 3: Native CYCLE Clause

Modern Postgres (14+) and the SQL standard offer a built-in CYCLE clause that automates the path check and flags cycles for you. It is the cleanest answer when the engine supports it.

WITH RECURSIVE org AS (
    SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e JOIN org o ON e.manager_id = o.id
)
CYCLE id SET is_cycle USING cycle_path
SELECT id, name, is_cycle FROM org;

SQL Server's MAXRECURSION

SQL Server enforces a default cap of 100 recursion levels. If a cycle (or a deep tree) exceeds it, the query errors out rather than looping forever — an implicit safety valve.

You can raise or remove it with OPTION (MAXRECURSION n), where 0 means unlimited. But removing the cap without a path guard reintroduces the infinite-loop risk on cyclic data.

-- Cap recursion at 200 levels in SQL Server
SELECT * FROM org
OPTION (MAXRECURSION 200);

Detecting vs Preventing Cycles

Interviewers may distinguish two goals:

Prevent — silently skip the cyclic edge so the query completes (the path-check WHERE).
Detect and report — surface which rows are part of a cycle so a data team can fix the bad data (the CYCLE clause's is_cycle flag).

Knowing both, and when each is appropriate, is a senior-level distinction.

Performance Considerations

Recursion can be expensive even without cycles. Tips interviewers like to hear:

Index the join column (e.g. manager_id) so each iteration's join is fast.
Filter early in the anchor to seed only the subtree you need, not the whole table.
Avoid SELECT * — carry only the columns recursion requires plus your depth/path.

A Safe Template

Combine the guards into a template you can reproduce under pressure: depth column as a backstop, path check as the precise guard. Even if one is overkill for clean data, showing both signals rigor.

WITH RECURSIVE walk AS (
    SELECT id, parent_id, 1 AS depth,
           CAST(',' || id || ',' AS VARCHAR(4000)) AS path
    FROM nodes WHERE parent_id IS NULL
    UNION ALL
    SELECT n.id, n.parent_id, w.depth + 1,
           w.path || n.id || ','
    FROM nodes n JOIN walk w ON n.parent_id = w.id
    WHERE w.depth < 100
      AND w.path NOT LIKE '%,' || n.id || ',%'
)
SELECT id, depth FROM walk;

Common Interview Pitfalls

Final traps to avoid:

Removing MAXRECURSION on SQL Server with no other guard — reopens infinite-loop risk.
A path string column declared too short, causing truncation and a silently broken guard.
Matching ids without comma delimiters, so id 1 falsely matches inside id 21.
Assuming the data is acyclic just because it "should" be — always ask.

Quick Check

Pick the guard that stops cycles precisely without capping legitimate depth.

Recap

Every recursive CTE answer should address safety:

Cycles make the recursive member never return empty, so recursion never stops.
Depth cap = quick backstop; visited-path check = precise cycle prevention; CYCLE clause = native detection in modern engines.
SQL Server's MAXRECURSION 100 is an implicit valve — do not remove it without another guard.
Index the join column and seed narrowly for performance.

You can now write, traverse, generate, and safeguard recursive CTEs end to end.

Frequently asked questions

Is the “Avoiding Infinite Recursion” lesson free?

Yes — the full text of “Avoiding Infinite Recursion” 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 “Avoiding Infinite Recursion”?

Cycle detection, depth limits, and the recursion guard every interviewer checks for. 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 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Avoiding Infinite Recursion” 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