SQL Interview Prep · Lesson

Anchor and Recursive Members

The two-part structure of a recursive CTE and how termination works.

Anchor and Recursive Members is a free SQL Interview Prep lesson on CoddyKit — lesson 1 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.

Why Recursive CTEs Come Up

When an interviewer hands you an org chart, a bill of materials, or a category tree and asks for every descendant, they are testing whether you reach for a recursive CTE. Plain joins can only walk a fixed number of levels; recursion walks an arbitrary depth.

The give-away phrase in a question is "to any depth" or "all the way down". That is your cue. In this lesson you will learn the two-part structure that every recursive CTE shares: the anchor and the recursive member.

The Two-Part Skeleton

A recursive CTE always has the keyword WITH RECURSIVE (Postgres, SQLite, MySQL 8+; SQL Server omits RECURSIVE) and a body made of two queries combined by UNION ALL:

Anchor member — the starting rows, runs once.
Recursive member — references the CTE name itself, runs repeatedly.

Memorize this skeleton; interviewers love asking you to write it from scratch.

WITH RECURSIVE cte AS (
    -- anchor member
    SELECT ...
    UNION ALL
    -- recursive member
    SELECT ... FROM cte JOIN ...
)
SELECT * FROM cte;

What the Anchor Does

The anchor member is an ordinary query with no reference to the CTE. It produces the seed rows — the level-zero starting point. For an org chart it is usually the CEO (the row whose manager is NULL); for a number series it is the first number.

The anchor runs exactly once. Its output becomes the first batch of rows fed into the recursive step.

-- Anchor: the top of the hierarchy
SELECT id, name, manager_id, 1 AS depth
FROM employees
WHERE manager_id IS NULL

What the Recursive Member Does

The recursive member references the CTE by name. On each iteration it joins the rows produced by the previous iteration to the base table to find the next level down.

It does not see the whole CTE so far — only the rows added in the immediately preceding step. This is the key mental model interviewers probe.

-- Recursive: children of the rows found so far
SELECT e.id, e.name, e.manager_id, c.depth + 1
FROM employees e
JOIN cte c ON e.manager_id = c.id

Putting It Together

Combine the anchor and recursive members with UNION ALL and the engine iterates automatically. Each pass appends the next level until the recursive member returns zero rows, at which point recursion stops.

Here is a complete, runnable org-chart walk that also tracks depth.

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
)
SELECT id, name, depth FROM org ORDER BY depth, id;

How Termination Works

Recursion stops when the recursive member produces no new rows. There is no explicit loop counter required — the join naturally runs dry once you reach the leaves of the tree.

In the org example, when you reach employees with no direct reports, the next iteration's join finds no children, returns empty, and the engine halts. Understanding this self-terminating behavior is a classic follow-up question.

UNION ALL vs UNION

Interviewers often ask why we use UNION ALL and not UNION. Two reasons:

Performance — UNION deduplicates on every iteration, which is expensive.
Correctness — in a tree, duplicate rows usually cannot occur, so deduplication is wasted work.

Use UNION only when the structure is a graph and you deliberately want to collapse repeated nodes — but for cycle safety, explicit guards are better (covered later).

Tracking Depth and Path

Two extra columns make recursive results far more useful and are commonly requested in interviews:

depth — start at 1 in the anchor, add 1 in the recursive member.
path — accumulate the chain of ids or names so you can see the route from root to node.

Building path as a string also doubles as a cycle-detection tool later.

WITH RECURSIVE org AS (
    SELECT id, name, manager_id, 1 AS depth,
           CAST(name AS VARCHAR(1000)) AS path
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, o.depth + 1,
           o.path || ' > ' || e.name
    FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT name, depth, path FROM org;

Column Types Must Match

A subtle gotcha: the anchor and recursive member must return the same number of columns with compatible types. If you build a path string, the anchor's initial value must be cast wide enough (e.g. VARCHAR(1000)) or the engine may truncate or throw a type-mismatch error on later iterations.

This is exactly the kind of detail an interviewer plants to see if you have actually run a recursive CTE rather than just read about one.

Bill-of-Materials Example

The same skeleton solves a bill of materials: given a part, list every sub-part at any depth. The anchor selects the top assembly; the recursive member walks the parent_part to child_part links.

Notice the structure is identical to the org chart — only the column names change. Recognizing that one skeleton fits many problems is the real interview skill.

WITH RECURSIVE bom AS (
    SELECT child_part, parent_part, 1 AS lvl
    FROM parts WHERE parent_part = 'ENGINE'
    UNION ALL
    SELECT p.child_part, p.parent_part, b.lvl + 1
    FROM parts p JOIN bom b ON p.parent_part = b.child_part
)
SELECT child_part, lvl FROM bom;

Dialect Notes

Quick cross-dialect cheat sheet interviewers appreciate:

PostgreSQL, SQLite, MySQL 8+: WITH RECURSIVE name AS (...).
SQL Server: just WITH name AS (...) — the RECURSIVE keyword is implicit, and it enforces a default MAXRECURSION of 100.
Oracle: supports both recursive CTEs and the older CONNECT BY syntax.

Saying "SQL Server doesn't use the word RECURSIVE" shows real breadth.

Quick Check

Test your grasp of the two-part structure.

Recap

You now own the recursive-CTE skeleton:

WITH RECURSIVE + anchor + UNION ALL + recursive member.
The anchor seeds level zero and runs once.
The recursive member joins the previous iteration to the base table and runs until it returns no rows.
Use UNION ALL, track depth and path, and keep column types compatible.

Next: applying this skeleton to walk a real org chart up and down.

Frequently asked questions

Is the “Anchor and Recursive Members” lesson free?

Yes — the full text of “Anchor and Recursive Members” 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 “Anchor and Recursive Members”?

The two-part structure of a recursive CTE and how termination works. 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 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Anchor and Recursive Members” 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