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.

How to Format SQL CTEs | CTE Style Guide (2026)

sqlfmt▍

How to Format SQL CTEs | CTE Style Guide (2026) — sqlfmt

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,
  email
FROM active_users
ORDER BY created_at DESC;

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 summary
ORDER BY orders_last_30_days DESC;

Recursive CTE — employee hierarchy

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_hierarchy
ORDER BY depth, name;

Subquery vs CTE — same logic, two readability levels

-- Hard to read: nested subquery
SELECT email, total_spent
FROM (
  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_totals
WHERE total_spent > 1000;

-- Easier to read: named CTE
WITH 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_spent
FROM user_totals
WHERE total_spent > 1000;

How to Format SQL CTEs (Common Table Expressions)

Quick answer

FAQs

What is the standard indentation for SQL CTEs?

Should CTE names use snake_case or camelCase?

Where does the comma go in a multi-CTE query?

Do all SQL dialects support CTEs?

How do I format a recursive CTE?

Is a CTE faster than a subquery?

SQL BETWEEN Is Inclusive: Safer Date Range Queries

SQL DELETE vs TRUNCATE vs DROP: Choose Safely

SQL Transactions: COMMIT, ROLLBACK, and SAVEPOINT

The Basic CTE Structure

Indentation: How Deep to Go

Formatting Multi-CTE Chains

Recursive CTEs

CTE vs Subquery: When Each Format Makes Sense

Dialect Notes

Quick answer

FAQs

What is the standard indentation for SQL CTEs?

Should CTE names use snake_case or camelCase?

Where does the comma go in a multi-CTE query?

Do all SQL dialects support CTEs?

How do I format a recursive CTE?

Is a CTE faster than a subquery?

Keep reading

SQL BETWEEN Is Inclusive: Safer Date Range Queries

SQL DELETE vs TRUNCATE vs DROP: Choose Safely

SQL Transactions: COMMIT, ROLLBACK, and SAVEPOINT

The Basic CTE Structure

Indentation: How Deep to Go

Formatting Multi-CTE Chains

Recursive CTEs

CTE vs Subquery: When Each Format Makes Sense

Dialect Notes