What Are CTEs (Common Table Expressions) and How Should I Format Them?

Understanding Common Table Expressions (CTEs)

A Common Table Expression, or CTE, is a named temporary result set that exists for the duration of a single SQL query. CTEs allow you to break complex queries into more readable, manageable pieces. They're created using the WITH clause and referenced multiple times within the same query. Understanding CTEs and formatting them properly transforms your SQL from incomprehensible to elegant.

Basic CTE Structure

A simple CTE looks like:

WITH cte_name AS (
  SELECT column1, column2
  FROM source_table
  WHERE condition = true
)
SELECT * FROM cte_name;

This creates a CTE called cte_name that selects specific columns with a filter. The main query then selects from this CTE. The CTE is a temporary result set that exists only within the scope of this query.

Why CTEs Are Valuable

Before CTEs, developers created complex nested subqueries that were difficult to read:

SELECT * FROM (
  SELECT customer_id, SUM(order_total) as total
  FROM (
    SELECT o.customer_id, SUM(oi.price * oi.quantity) as order_total
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.order_date > '2024-01-01'
    GROUP BY o.order_id, o.customer_id
  ) order_summaries
  GROUP BY customer_id
) customer_totals;

With CTEs, the same logic is clearer:

WITH order_summaries AS (
  SELECT o.customer_id, SUM(oi.price * oi.quantity) as order_total
  FROM orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  WHERE o.order_date > '2024-01-01'
  GROUP BY o.order_id, o.customer_id
),
customer_totals AS (
  SELECT customer_id, SUM(order_total) as total
  FROM order_summaries
  GROUP BY customer_id
)
SELECT * FROM customer_totals;

This version is dramatically more readable because the logic is separated into named, reusable pieces.

CTE Formatting Best Practices

Use meaningful names - Name CTEs descriptively to clarify their purpose:

Good: WITH customer_annual_revenue AS Bad: WITH cte1 AS

Good names make your query self-documenting.

Format WITH clause consistently - Place the WITH keyword at the start, and separate multiple CTEs with commas:

WITH first_cte AS (
  SELECT ...
),
second_cte AS (
  SELECT ...
)
SELECT * FROM second_cte;

Indent subquery content - Indent the SELECT statement inside the CTE to show nesting:

WITH customer_data AS (
  SELECT
    customer_id,
    customer_name,
    email
  FROM customers
  WHERE active = true
)
SELECT * FROM customer_data;

Use line breaks for readability - Even in SELECT lists, use line breaks:

WITH customer_summary AS (
  SELECT
    customer_id,
    customer_name,
    email,
    registration_date,
    last_purchase_date
  FROM customers
)
SELECT * FROM customer_summary;

Rather than:

WITH customer_summary AS (
  SELECT customer_id, customer_name, email, registration_date, last_purchase_date FROM customers
)
SELECT * FROM customer_summary;

Align column names - Align related columns for visual clarity:

WITH monthly_sales AS (
  SELECT
    DATEPART(MONTH, order_date)         AS month,
    DATEPART(YEAR, order_date)          AS year,
    SUM(order_total)                    AS total_sales,
    COUNT(DISTINCT customer_id)         AS unique_customers
  FROM orders
  GROUP BY DATEPART(YEAR, order_date), DATEPART(MONTH, order_date)
)
SELECT * FROM monthly_sales;

Recursive CTEs: Advanced CTE Usage

Recursive CTEs allow a CTE to reference itself, useful for hierarchical data. A recursive CTE has two parts: the base case and the recursive case:

WITH RECURSIVE category_hierarchy AS (
  -- Base case: top-level categories
  SELECT
    category_id,
    category_name,
    parent_category_id,
    1 AS depth
  FROM categories
  WHERE parent_category_id IS NULL

  UNION ALL

  -- Recursive case: child categories
  SELECT
    c.category_id,
    c.category_name,
    c.parent_category_id,
    ch.depth + 1
  FROM categories c
  JOIN category_hierarchy ch ON c.parent_category_id = ch.category_id
  WHERE ch.depth < 10  -- Prevent infinite recursion
)
SELECT * FROM category_hierarchy;

Recursive CTEs are powerful but complex. Format them carefully to show the two distinct parts.

Multiple CTEs and Ordering

When using multiple CTEs, order them logically:

WITH
-- Dimension tables (slowly changing dimensions)
customer_dim AS (
  SELECT customer_id, customer_name FROM customers
),

product_dim AS (
  SELECT product_id, product_name, category FROM products
),

-- Fact tables and calculations
sales_facts AS (
  SELECT
    s.sale_id,
    s.customer_id,
    s.product_id,
    s.sale_amount,
    s.sale_date
  FROM sales s
),

monthly_revenue AS (
  SELECT
    DATEPART(MONTH, sf.sale_date) AS month,
    SUM(sf.sale_amount) AS revenue
  FROM sales_facts sf
  GROUP BY DATEPART(MONTH, sf.sale_date)
)

SELECT
  mr.month,
  mr.revenue
FROM monthly_revenue mr
ORDER BY mr.month;

This organization—dimensions first, then fact tables, then derived calculations—makes the query structure clear.

CTE Scoping and Visibility

An important characteristic of CTEs is that they're only available within the query that defines them. Each CTE can reference:

• Previously defined CTEs
• Tables in the database
• NOT later-defined CTEs (they must be defined first)

WITH first_cte AS (
  SELECT ... FROM base_table
),
second_cte AS (
  SELECT ... FROM first_cte  -- Valid: references earlier CTE
),
third_cte AS (
  SELECT ... FROM second_cte -- Valid: references earlier CTE
)
SELECT * FROM third_cte;

This forward-dependency structure is important to understand for proper CTE organization.

Performance Considerations with CTEs

CTEs don't necessarily improve performance—they improve readability. The query optimizer may handle a CTE similarly to a subquery. Some databases materialize CTEs (execute them and store results), while others inline them (treat them like subqueries).

Performance tips:

Don't assume materialization - Some databases don't materialize CTEs, so they might execute multiple times if referenced multiple times in the main query.

Test before optimizing - Use query execution plans to see how your database handles CTEs.

Use indexes appropriately - CTEs benefit from the same indexing strategies as regular queries.

Keep CTEs focused - Don't create overly broad CTEs that select unnecessary columns or rows; this impacts performance when the CTE is referenced.

CTE Naming Conventions

Develop naming conventions for your team:

Prefix by type:

• dim_ for dimension tables: WITH dim_customers AS
• fact_ for fact/transaction tables: WITH fact_sales AS
• agg_ for aggregations: WITH agg_monthly_revenue AS

Descriptive suffixes:

• _summary for aggregated results: WITH customer_summary AS
• _details for detailed data: WITH transaction_details AS
• _expanded for joined/enriched data: WITH customer_expanded AS

Common Mistakes to Avoid

Missing commas between CTEs - The most common syntax error:

-- Wrong: missing comma after first CTE
WITH first_cte AS (SELECT ...)
second_cte AS (SELECT ...)
SELECT * FROM second_cte;

-- Correct:
WITH first_cte AS (SELECT ...),
second_cte AS (SELECT ...)
SELECT * FROM second_cte;

Naming CTEs like temporary tables - Don't name them "temp_" or with a "#" prefix; they're not temporary tables:

-- Avoid
WITH #temp_customers AS

-- Prefer
WITH staging_customers AS

Overly complex CTEs - If a CTE becomes too large or complex, consider breaking it into multiple CTEs or creating a view instead.

Not documenting CTE purpose - Add comments explaining what each CTE does:

WITH /* Annual revenue by customer */
annual_customer_revenue AS (
  SELECT customer_id, SUM(order_total) as annual_revenue
  FROM orders
  WHERE YEAR(order_date) = 2024
  GROUP BY customer_id
)
SELECT * FROM annual_customer_revenue;

CTE vs Subqueries vs Views

CTEs - Use for query-specific logic that makes a single query more readable Subqueries - Use for inline logic you don't need to reference multiple times Views - Use for reusable logic that multiple queries need

CTEs are ideal when you need to organize a complex query into understandable pieces, especially when you might reference the same logic multiple times within one query.

CTE Best Practices Summary

1. Use meaningful CTE names that describe their content and purpose
2. Format consistently with proper indentation and line breaks
3. Organize logically - dimension tables, then fact tables, then calculations
4. Keep CTEs focused - select only needed columns and rows
5. Comment complex CTEs to explain their purpose
6. Test performance - don't assume CTEs are materialized or perform well
7. Follow naming conventions established by your team
8. Use recursive CTEs carefully - they're powerful but complex

CTEs transform SQL from a monolithic wall of text into organized, readable, maintainable logic. By formatting them properly and organizing them thoughtfully, you create SQL that other developers—and your future self—will appreciate reading and maintaining.