What is not allowed inside the recursive member of a WITH RECURSIVE query?
answer
- Each pass must only add rows
- Anything that could unsay a row is banned
- Count how many times the CTE names itself
- Set-level operations wait for the outer query
- One self-reference, no aggregates, no negation
basics
~20 sThe recursive member may reference the CTE's own name only once, in its FROM clause, and not inside a subquery. Aggregates, window functions, GROUP BY, HAVING and DISTINCT over the recursive reference are rejected, as is putting it on the null-supplying side of an outer join.
solid answer
~50 sThe recursive member is deliberately restricted so the iteration has a well-defined fixpoint. The standard requires that the CTE's own name appear **exactly once** in the recursive member's `FROM` clause — not twice, not inside a nested subquery, and not on the NULL-supplying side of an outer join or under a negation. It also disallows aggregate functions, window functions, `GROUP BY`, `HAVING` and `DISTINCT` in that member. The reason is **monotonicity**: each pass must only *add* rows. An aggregate or a negation over the growing result could invalidate a row already emitted, so iterating would never settle. Engines enforce a very similar list with different error messages. The workaround is always the same shape — carry the value you need forward as an extra column of the recursive member, and do the aggregation, ranking or deduplication in the outer query once the CTE is complete.
code
sql · 15 lines-- Rejected: aggregate over the recursive reference
WITH RECURSIVE t(n, total) AS (
SELECT 1, 1
UNION ALL
SELECT n + 1, SUM(total) FROM t WHERE n < 5
)
SELECT * FROM t;
-- Accepted: aggregate outside, after the CTE is complete
WITH RECURSIVE t(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM t WHERE n < 5
)
SELECT SUM(n) AS total FROM t;go deeper
Know that the recursive member is limited: reference the CTE once, keep it to joins and WHERE clauses, and do any grouping or totals in the outer query.
Name the concrete restrictions and give the reason — each pass must be purely additive, so aggregates and negation over the growing result have no fixpoint.
Show the rewrite fluently: carry per-row state forward as columns, use UNION rather than DISTINCT for in-iteration dedup, and push aggregation and ranking outside the CTE.
Recognise when the restrictions are telling you the problem is not a fixpoint at all — iterative aggregation over a growing set usually belongs in a different formulation or outside the query layer entirely.
## The restriction list The standard constrains what may appear in the recursive member of a `WITH RECURSIVE` query. The commonly enforced rules are: - The self-reference must appear **exactly once**, and directly in the recursive member's `FROM` clause. Referencing the CTE twice — to walk two directions at once, say — is rejected. - The self-reference must not be buried inside a subquery in the recursive member. - It must not sit on the NULL-supplying side of an outer join (the right side of a `LEFT JOIN`, for example), nor under a negation such as `NOT EXISTS`. - Aggregate functions, window functions, `GROUP BY`, `HAVING` and `DISTINCT` are not allowed in the recursive member. Individual engines phrase the errors differently and a few are slightly stricter or laxer at the edges, so read your engine's message rather than guessing — but if a construct is on this list, assume it will be rejected. ## Why the rules exist: monotonicity A recursive CTE is evaluated by applying the recursive member over and over until a pass adds nothing. That procedure only makes sense if each pass is **monotone** — it can add rows but never retract or change rows already produced. All the restrictions protect that property. An aggregate is the clearest case. Suppose the recursive member could compute `SUM()` over the CTE. Pass 3 would produce a total; pass 4 adds rows, which changes what that total *should* have been, invalidating a row already emitted. The iteration would oscillate rather than converge, and there would be no defined answer to return. Negation is the same problem from the other direction: `NOT EXISTS (SELECT ... FROM cte ...)` is true until a later pass produces the matching row, at which point an earlier decision becomes wrong. Two self-references make each pass depend on the result twice over, with no single well-defined frontier. And the NULL-supplying side of an outer join manufactures rows out of the *absence* of matches, which is again non-monotone as the result grows. So the rules are not arbitrary vendor pickiness — they are the price of a construct whose meaning is defined by a fixpoint. ## What to do instead The workaround is always the same: keep only per-row work inside the recursive member, carry forward whatever state you need as extra columns, and do set-level work outside. ```sql -- Rejected: aggregate over the recursive reference WITH RECURSIVE t(n, total) AS ( SELECT 1, 1 UNION ALL SELECT n + 1, SUM(total) FROM t WHERE n < 5 -- not allowed ) SELECT * FROM t; -- Accepted: aggregate in the outer query WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 5 ) SELECT SUM(n) AS total FROM t; ``` Running totals along a path are expressible: they are per-row arithmetic on the previous row's value, not an aggregate over a set. ```sql WITH RECURSIVE t(n, running) AS ( SELECT 1, 1 UNION ALL SELECT n + 1, running + (n + 1) FROM t WHERE n < 5 ) SELECT n, running FROM t; -- 1/1, 2/3, 3/6, 4/10, 5/15 ``` That is the trick worth remembering: arithmetic on the carried column is fine; aggregation over the CTE is not. Likewise, ranking or deduplication that you would express with a window function goes in the outer query, and duplicate suppression *during* iteration is expressed by using `UNION` rather than `UNION ALL` between the two members — not by writing `DISTINCT` inside the recursive member. ## What is perfectly legal Plenty is allowed and worth stating so the restrictions do not sound broader than they are: joining the self-reference to ordinary tables, `WHERE` predicates of any complexity, `CASE` expressions, scalar functions, string concatenation, arithmetic, casts, and referencing other (non-recursive) CTEs defined in the same `WITH` list. The anchor member has none of these restrictions at all — it can aggregate, group, sort and use window functions freely, because it runs once before any iteration begins. ## Answering the question Name two or three concrete restrictions — one self-reference in the `FROM` clause, no aggregates or window functions, not on the nullable side of an outer join — then give the reason in one sentence: each pass has to be purely additive or the fixpoint would not exist. That reason is what distinguishes a memorised list from understanding.
- Why is an aggregate function specifically forbidden there?Because evaluation is a fixpoint: each pass must only add rows. An aggregate computed over the CTE mid-iteration would be invalidated by rows a later pass adds, so a row already emitted would become wrong and the iteration would never settle. Aggregate in the outer query once the CTE is complete.
- The anchor member has none of these restrictions — why not?The anchor is evaluated exactly once, before any iteration begins, and never references the CTE. Nothing it computes can be invalidated by later passes, so it may aggregate, group, sort and use window functions like any ordinary query.
saying these in an interview costs you the question
- Puts SUM() in the recursive member expecting a running total
- Thinks the CTE can be referenced twice to walk two directions
- Believes DISTINCT inside the recursive member deduplicates the iteration
- Calls the restrictions arbitrary vendor quirks
- Puts the self-reference on the right of a LEFT JOIN