Common Table Expression (CTE)

A Common Table Expression (CTE) is a temporary named result set defined within a WITH clause that exists only for the duration of a single SQL statement. CTEs improve readability, enable recursive queries, and allow breaking complex logic into named, composable steps. Supported in PostgreSQL, MySQL 8+, SQLite 3.8.3+, SQL Server, and Oracle.

Basic Syntax

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

Multiple CTEs

Chain multiple CTEs separated by commas. Later CTEs can reference earlier ones:

WITH
    active_projects AS (
        SELECT * FROM projects WHERE status = 'active'
    ),
    project_budgets AS (
        SELECT project_id, SUM(amount) AS total
        FROM expenses
        GROUP BY project_id
    )
SELECT ap.name, pb.total
FROM active_projects ap
JOIN project_budgets pb ON ap.id = pb.project_id;

Recursive CTEs

Recursive CTEs reference themselves, enabling hierarchical and graph traversal queries. Structure:

WITH RECURSIVE org_chart AS (
    -- Base case (anchor member)
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
 
    UNION ALL
 
    -- Recursive case
    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level;

Use cases:

• Organizational hierarchies (employee → manager)
• Bill of materials (part → sub-parts)
• Graph traversal (node → adjacent nodes)
• Generating series (date ranges, number sequences)

Termination: The recursion stops when the recursive member returns no new rows. Add a WHERE level < N safety guard to prevent infinite loops in cyclic data.

CTEs vs Subqueries vs Views

Feature	CTE	Subquery	View
Scope	Single statement	Single use	Schema-level
Reusability in query	Can reference multiple times	Must duplicate	Can reference anywhere
Recursion	Yes	No	No
Readability	Named, top-down	Nested, inside-out	Named, persistent
Optimization	Typically inlined by optimizer	Inlined	Re-executed

Performance Notes

• Most optimizers inline CTEs (treat them as subqueries), so there’s no performance penalty
• PostgreSQL 12+ inlines by default; use MATERIALIZED hint to force materialization if the CTE is referenced multiple times and expensive
• Recursive CTEs can be expensive on large hierarchies — ensure the recursive join column is indexed (see Index)

Sources

• Lecture-31-70.107 Advanced SQL Subqueries, Paritions, and Common Table Expressions