SQL CTEs (Common Table Expressions) - Complete WITH Clause Guide

Published: May 29, 2026

Common Table Expressions (CTEs) are a powerful SQL feature that lets you create temporary named result sets. They make complex queries more readable and maintainable by breaking them into logical, reusable components.

What is a CTE?

A Common Table Expression (CTE) is a temporary named result set that exists only within the scope of a single statement. CTEs are defined using the WITH clause and can be referenced in SELECT, INSERT, UPDATE, or DELETE statements.

Basic CTE Syntax

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

Non-Recursive CTEs

Non-recursive CTEs are the simplest form and don't reference themselves.

Example: Monthly Sales Summary

WITH monthly_sales AS (
    SELECT 
        MONTH(order_date) AS month,
        YEAR(order_date) AS year,
        SUM(amount) AS total_sales
    FROM orders
    GROUP BY MONTH(order_date), YEAR(order_date)
)
SELECT 
    year,
    month,
    total_sales,
    LAG(total_sales) OVER (ORDER BY year, month) AS previous_month_sales
FROM monthly_sales;

Multiple CTEs

You can define multiple CTEs and reference them together:

WITH customer_orders AS (
    SELECT customer_id, COUNT(*) as order_count
    FROM orders
    GROUP BY customer_id
),
high_value_customers AS (
    SELECT customer_id FROM customer_orders
    WHERE order_count > 10
)
SELECT * FROM high_value_customers;

Recursive CTEs

Recursive CTEs reference themselves and are useful for hierarchical data like organizational structures or category trees.

Syntax

WITH RECURSIVE cte_name AS (
    -- Anchor member (base case)
    SELECT column1, column2, ... FROM table
    WHERE condition
    
    UNION ALL
    
    -- Recursive member (recursive case)
    SELECT column1, column2, ... FROM table
    JOIN cte_name ON condition
)
SELECT * FROM cte_name;

Example: Organizational Hierarchy

WITH RECURSIVE employee_hierarchy AS (
    -- Anchor: Get all top-level employees (no manager)
    SELECT employee_id, employee_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive: Get employees under current level
    SELECT e.employee_id, e.employee_name, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh 
        ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy
ORDER BY level, employee_name;

CTE vs Subqueries vs Temporary Tables

• CTE: Readable, reusable within one statement, faster execution
• Subqueries: Inline queries, good for simple cases, less readable
• Temporary Tables: Persisted, reusable across statements, more overhead

Benefits of Using CTEs

• Improved readability: Break complex logic into named steps
• Reusability: Reference the same CTE multiple times
• Debugging: Easier to test each CTE independently
• Maintainability: Logical organization of query logic
• Performance: Often optimized better than subqueries

Visualize Your CTEs

Complex CTE hierarchies can be hard to understand at first glance. Use SQL Visualizer to see how your CTEs relate to each other and to the source tables.

CTE Best Practices

• Use meaningful names for your CTEs (e.g., monthly_sales instead of cte1)
• Limit recursion depth to avoid infinite loops in recursive CTEs
• Use MAXRECURSION option to set upper limit: OPTION(MAXRECURSION 100)
• Test recursive CTEs incrementally to verify logic
• Comment your CTE logic for future maintainers

Common Mistakes with CTEs

• Forgetting the comma between multiple CTEs
• Infinite loops in recursive CTEs (always add exit condition)
• Using non-deterministic functions in recursive CTEs
• Not setting MAXRECURSION limit (default is 100)

Conclusion

CTEs are an essential tool for writing clean, maintainable SQL. Whether you're using them for simple data organization or complex recursive queries, they help make your SQL more understandable and efficient. Master CTEs and take your SQL skills to the next level!