SQL Interview Prep · Lesson

Anchor and Recursive Members

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

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;

All lessons in this course

← Back to SQL Interview Prep