How to Format Recursive CTEs in SQL: Hierarchical Query Structure
By Sharon Ben-Moshe · October 12, 2026 · 1 min read
Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.
Format recursive CTEs (WITH RECURSIVE) for hierarchical data. Master clean separation of anchor member, UNION ALL, recursive step, and depth guards.
Quick answer
Format recursive CTEs by explicitly separating the query into four distinct visual sections: 1) the CTE header with column aliases, 2) the Anchor Member (base query), 3) the UNION ALL operator on its own line, and 4) the Recursive Member with a join back to the CTE and an explicit termination depth guard (WHERE level < max_depth).
FAQs
What is an anchor member in a recursive CTE?
The anchor member is the initial SELECT query that executes once to produce the starting result set (such as the root nodes of an org chart or category tree).
Why is UNION ALL used instead of UNION in recursive CTEs?
The SQL standard mandates UNION ALL (or UNION) to bind the anchor and recursive members. UNION ALL is preferred because it avoids expensive deduplication hashing on every recursive iteration.
How do I prevent infinite loops in recursive CTEs?
Include a recursion depth counter (`level + 1`) and add a termination guard in the recursive member: `WHERE level < 20`. In SQL Server, you can also set `OPTION (MAXRECURSION 50)`.
Does SQL Server require the 'RECURSIVE' keyword?
No! In PostgreSQL, MySQL, and SQLite, you write `WITH RECURSIVE`. In SQL Server (T-SQL), the keyword `RECURSIVE` is omitted; SQL Server detects recursion automatically if the CTE references itself.
Keep reading
Building an Enterprise SQL Style Guide: Formatting Standards, Linting, and Team Conventions
By Sharon Ben-Moshe · Oct 13, 2026
The complete blueprint for an enterprise SQL style guide. Standardize formatting, keyword casing, indentation, naming conventions, and CI linting.
Read postThe Master Guide to SQL Common Table Expressions (CTEs): Syntax, Optimization Fences, and Recursion
By Sharon Ben-Moshe · Oct 13, 2026
The definitive guide to SQL Common Table Expressions (CTEs). Master chaining, recursive hierarchies, data-modifying CTEs, and optimization fences.
Read postTry it now
Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.