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.
SQL CTEs turn into a wall of text fast. Here's how to format WITH clauses, multi-CTE chains, and recursive CTEs so the structure reads at a glance.
Quick answer
Indent the CTE body 2–4 spaces, put the closing parenthesis and comma on their own lines, and clearly separate the final SELECT. Use uppercase for SQL keywords and consistent snake_case for CTE names throughout the chain.
FAQs
What is the standard indentation for SQL CTEs?
2 or 4 spaces for the CTE body relative to the opening parenthesis — both are widely used. The closing parenthesis goes on its own line at the base indentation level, not appended to the last row of the inner SELECT. Pick a depth and apply it consistently across your codebase.
Should CTE names use snake_case or camelCase?
snake_case is standard in SQL because SQL identifiers are case-insensitive and most style guides default to lowercase. Names like active_users or monthly_revenue are preferred over activeUsers or ActiveUsers.
Where does the comma go in a multi-CTE query?
Either trailing (at the end of the closing parenthesis line) or leading (at the start of the next CTE name on its own line). Both are valid; what matters is consistency within a file. If your team uses leading commas in SELECT lists, use leading commas between CTEs too.
Do all SQL dialects support CTEs?
PostgreSQL, SQL Server, SQLite (3.8.3+), MySQL (8.0+), and BigQuery all support WITH clause CTEs. MySQL 5.7 is the main exception — it predates CTE support and requires subqueries instead.
How do I format a recursive CTE?
Use RECURSIVE after WITH (PostgreSQL, MySQL, SQLite, BigQuery), separate the base case and recursive step with UNION ALL, and add a comment before each part. SQL Server detects recursion automatically and does not require the RECURSIVE keyword.
Is a CTE faster than a subquery?
Usually not — most query planners treat CTEs and equivalent subqueries identically. The benefit is readability and maintainability, not performance. PostgreSQL's MATERIALIZED hint forces the CTE to materialize as a temporary result, which can help or hurt performance depending on the query plan.
Try it now
Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.
A Common Table Expression — the WITH clause before a SELECT — is one of SQL's most powerful readability tools. It's also one of the easiest to format badly. A three-CTE chain with no consistent indentation looks like a wall of text; the same query with a clear structure is self-documenting. Here's the formatting approach that scales from a single CTE to a chain of ten.
The Basic CTE Structure
A CTE has three parts: the WITH keyword, the name and body of the expression, and the final SELECT that consumes it. Every formatting decision flows from keeping those three parts visually distinct.
Basic CTE — single expression
WITH active_users AS ( SELECT user_id, email, created_at FROM users WHERE status = 'active')SELECT user_id, emailFROM active_usersORDER BY created_at DESC;
The conventions above: uppercase SQL keywords (WITH, SELECT, FROM, WHERE), snake_case for the CTE name, 2-space indentation inside the SELECT body, and the closing parenthesis on its own line at zero indent. This is the layout most SQL formatters produce by default and most style guides recommend.
Indentation: How Deep to Go
Two or four spaces — both are standard. The right choice depends on your team's existing SQL style guide. The critical rule is that the CTE body's SELECT columns are indented one level relative to the SELECT keyword itself, so the structure scans without counting characters.
The closing parenthesis marks the boundary of the CTE definition and belongs on its own dedented line, never appended to the last row of the inner SELECT. This makes it easy to add columns or a WHERE clause later without hunting for the closing delimiter.
Formatting Multi-CTE Chains
When you have more than one CTE, each follows the previous closing parenthesis with a comma. The two main placement styles are trailing (comma at the end of the closing parenthesis line) and leading (comma at the start of the next CTE name on its own line). Either is consistent — pick the style that matches how your team handles commas in SELECT column lists and apply it everywhere.
Multi-CTE chain — three expressions with leading commas
WITH active_users AS ( SELECT user_id, email FROM users WHERE status = 'active' ), recent_orders AS ( SELECT user_id, COUNT(*) AS order_count FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '30 days' GROUP BY user_id ), summary AS ( SELECT u.email, COALESCE(o.order_count, 0) AS orders_last_30_days FROM active_users u LEFT JOIN recent_orders o ON u.user_id = o.user_id )SELECT *FROM summaryORDER BY orders_last_30_days DESC;
Name CTEs after what they produce, not how they're built. active_users is better than filtered_result or cte1. Noun and verb-noun patterns like monthly_revenue, ranked_products, or daily_signups make the chain read like a data pipeline — which is exactly what it is. When you can read the CTE names from top to bottom and understand the query's story, the formatting is working.
Recursive CTEs
Recursive CTEs require the RECURSIVE keyword after WITH in PostgreSQL, MySQL, SQLite, and BigQuery, and a UNION ALL between the base case and the recursive step. SQL Server detects recursion automatically — you write the same UNION ALL structure but omit the RECURSIVE keyword. Format the two parts with inline comments so the boundary is unmistakable.
Comments inside a recursive CTE are one of the rare places inline SQL comments pay for themselves. Labeling the base case and the recursive step makes the structure legible to someone unfamiliar with recursive CTEs, and it prevents the two halves from blurring together during a code review.
CTE vs Subquery: When Each Format Makes Sense
The readability argument for CTEs over subqueries is that a CTE name surfaces what the result set represents. A subquery buried three levels deep gives no such hint. The practical rule: if you would give the result set a name to explain it, use a CTE. The comparison below shows the same query in both forms — same logic, two very different readability levels.
Paste a subquery-heavy query into sqlfmt's free formatter and it automatically applies CTE-ready indentation and flags keyword typos at the same time.
Dialect Notes
CTE support is near-universal in modern SQL but the specifics vary. PostgreSQL supports MATERIALIZED and NOT MATERIALIZED hints on CTEs to control whether the planner caches the result — useful in performance tuning. MySQL added CTE support in version 8.0 (2018); code targeting MySQL 5.7 must use subqueries instead. SQLite supports CTEs since version 3.8.3. BigQuery supports standard CTEs and added recursive CTE support in 2023. SQL Server (T-SQL) supports CTEs fully but omits the RECURSIVE keyword — the engine detects recursion from the UNION ALL structure automatically.
All five dialects — PostgreSQL, MySQL, SQLite, T-SQL, and BigQuery — are supported in sqlfmt. Paste your WITH clause and the formatter applies consistent indentation and keyword casing regardless of which database you're targeting.
WITH RECURSIVE employee_hierarchy AS ( -- Base case: top-level employees with no manager SELECT employee_id, name, manager_id, 0 AS depth FROM employees WHERE manager_id IS NULL UNION ALL -- Recursive step: employees reporting to the previous level SELECT e.employee_id, e.name, e.manager_id, h.depth + 1 FROM employees e INNER JOIN employee_hierarchy h ON e.manager_id = h.employee_id)SELECT *FROM employee_hierarchyORDER BY depth, name;
Subquery vs CTE — same logic, two readability levels
-- Hard to read: nested subquerySELECT email, total_spentFROM ( SELECT u.email, SUM(o.amount) AS total_spent FROM users u JOIN orders o ON u.user_id = o.user_id GROUP BY u.email) AS user_totalsWHERE total_spent > 1000;-- Easier to read: named CTEWITH user_totals AS ( SELECT u.email, SUM(o.amount) AS total_spent FROM users u JOIN orders o ON u.user_id = o.user_id GROUP BY u.email)SELECT email, total_spentFROM user_totalsWHERE total_spent > 1000;