What are the two members of a WITH RECURSIVE query, and how do they combine?
answer
- Two halves separated by a set operator
- One half never names the CTE
- The other half names it and repeats
- Seed row first, then the rule that extends it
- Anchor member plus recursive member, joined by UNION ALL
basics
~20 sA WITH RECURSIVE query has an anchor member that runs once to seed the result and does not mention the CTE's name, plus a recursive member that does reference that name and runs repeatedly. UNION ALL or UNION joins them.
solid answer
~40 sA recursive CTE body is a set operation between exactly two parts. The **anchor member** (also called the non-recursive or seed term) comes first, must not reference the CTE's own name, and runs once to produce the starting rows. The **recursive member** comes second, references the CTE name in its `FROM` clause, and is re-evaluated repeatedly, each pass feeding on the rows the previous pass produced. The two are combined with `UNION ALL` (keep every row) or `UNION` (drop rows already produced). The `RECURSIVE` keyword attaches to `WITH` once for the whole CTE list, not to an individual name, and the anchor member fixes the column count and types the recursive member must match.
code
sql · 7 linesWITH RECURSIVE counter(n) AS (
SELECT 1 -- anchor member: evaluated once
UNION ALL
SELECT n + 1 FROM counter -- recursive member: references the CTE
WHERE n < 5 -- stop condition
)
SELECT n FROM counter; -- returns 1, 2, 3, 4, 5go deeper
Be ready to write the skeleton from memory: WITH RECURSIVE name AS (anchor UNION ALL recursive-member-with-a-stop-condition), then the outer SELECT. Name both members and say which one may not mention the CTE.
Explain why the anchor cannot self-reference, that the anchor fixes column count and types, and that RECURSIVE attaches to WITH rather than to a single CTE name.
Show you can read an unfamiliar recursive CTE quickly: identify the seed, the extension rule, and the stop predicate, and say what would happen if any one of them were wrong.
Own the readability call — when a recursive CTE is the clearest expression of a problem in the query layer, and when the shape of the data means the work belongs in the application or a precomputed table instead.
## What a recursive CTE is for A recursive common table expression is the standard SQL construct for producing rows that depend on rows the same query has already produced — expanding an adjacency list, exploding a bill of materials, generating a series of numbers or dates, or splitting a delimited string one piece at a time. It is written `WITH RECURSIVE name AS (...)`, and it is the one place in standard SQL where a query may refer to its own name. ## The two members The body of a recursive CTE is a set operation with exactly two operands: - The **anchor member** (the standard calls it the non-recursive term; people also say *seed* or *base case*). It must **not** mention the CTE's own name. It is evaluated once and produces the rows the whole construct starts from. - The **recursive member** (recursive term). It **must** mention the CTE's own name in its `FROM` clause. It is evaluated repeatedly. The anchor comes first, then the set operator, then the recursive member: ```sql WITH RECURSIVE cte_name (col_a, col_b) AS ( SELECT ... -- anchor: no reference to cte_name UNION ALL SELECT ... -- recursive: references cte_name FROM cte_name JOIN some_table ON ... WHERE ... -- the stop condition lives here ) SELECT * FROM cte_name; ``` The outer statement then reads the CTE like any other table. ## UNION ALL versus UNION `UNION ALL` appends every row the recursive member produces. `UNION` discards rows that duplicate rows already produced, which also changes when the iteration stops. `UNION ALL` is the usual choice for tree-shaped data with a clear stop condition; `UNION` is the safer choice when the same row can be reached by more than one path. Engines differ on whether they accept `UNION` between the two members at all, so check your engine's documentation before relying on it. ## Where RECURSIVE goes `RECURSIVE` is written once, immediately after `WITH`, and covers the whole comma-separated CTE list — not once per name: ```sql WITH RECURSIVE lookup AS ( ... ), -- ordinary, non-recursive CTE walk AS ( ... UNION ALL ... ) -- the recursive one SELECT ... ``` A `WITH RECURSIVE` list may freely contain CTEs that are not recursive at all; the keyword is permission, not an obligation. ## Columns and types The optional column list after the CTE name (`cte_name (col_a, col_b)`) names the output columns, which is convenient because the recursive member has to refer to them before the CTE "exists". The anchor member determines how many columns there are and what their data types are; the recursive member must return the same number of columns with compatible types. A practical trap: when the recursive member builds progressively longer strings, some engines take the column's declared width from the anchor's literal, so cast the anchor value explicitly to the type and width you need. ## A worked example Generating the integers 1 through 5 with no table at all: ```sql WITH RECURSIVE counter(n) AS ( SELECT 1 -- anchor: one row, n = 1 UNION ALL SELECT n + 1 FROM counter -- recursive: reads the CTE WHERE n < 5 -- stop condition ) SELECT n FROM counter; -- 1, 2, 3, 4, 5 ``` The anchor yields `1`. The recursive member reads that row and yields `2`, then reads `2` and yields `3`, and so on. When it reads `5`, the predicate `n < 5` is false, the pass produces no rows, and iteration ends. ## What goes wrong The most common structural errors are: putting the self-reference in the anchor (the engine rejects it — the anchor has nothing to read yet), writing only one member so nothing ever seeds the result, spelling `WITH name RECURSIVE`, and omitting `RECURSIVE` entirely, which turns the self-reference into a "relation does not exist" error. The second most common error is forgetting the stop condition in the recursive member, which produces a query that never finishes. ## Version note `WITH RECURSIVE` entered the standard with SQL:1999 and is widely implemented. MySQL added common table expressions, including recursive ones, in 8.0; on older versions the construct is simply unavailable and the query has to be rewritten.
- Does every CTE in a WITH RECURSIVE list have to be recursive?No. `RECURSIVE` is written once after `WITH` and applies to the whole comma-separated list, but it only grants permission for self-reference. A list can mix ordinary CTEs with one recursive CTE, and often does — a lookup or filter CTE defined first, then the recursive one that reads it.
- Which member decides the column names and data types of the CTE?The anchor member fixes the column count and types; the recursive member must match with compatible types. The optional column list after the CTE name supplies the names, which matters because the recursive member refers to those columns before the CTE is fully defined. Cast literals in the anchor when the recursive member builds wider values.
The anchor member is the first domino you place by hand; the recursive member is the rule that says "whatever just fell knocks over the next one", applied over and over until nothing falls.
saying these in an interview costs you the question
- Says the anchor member can reference the CTE itself
- Describes WITH RECURSIVE as calling a stored procedure recursively
- Writes RECURSIVE after the CTE name instead of after WITH
- Thinks the recursive member is optional decoration
- Cannot name the two members or say which runs first