Common Table Expressions

Basic CTE

A Common Table Expression (CTE) is a temporary named result set defined with the WITH clause. It exists only for the duration of the query and improves readability by breaking complex logic into named steps.

-- Define a CTE for high-value orders, then filter
WITH high_value_orders AS (
    SELECT order_id, customer_id, total_amount
    FROM orders
    WHERE total_amount > 1000
)
SELECT hvo.order_id, c.customer_name, hvo.total_amount
FROM high_value_orders hvo
JOIN customers c ON hvo.customer_id = c.customer_id;

CTEs are defined before the main query and referenced by name. They can be thought of as inline views that have a meaningful name.

-- CTE for aggregated data
WITH monthly_sales AS (
    SELECT 
        DATE_FORMAT(order_date, '%Y-%m') AS month,
        SUM(total_amount) AS total_sales
    FROM orders
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT month, total_sales,
       total_sales - LAG(total_sales) OVER (ORDER BY month) AS growth
FROM monthly_sales;

The key advantage of CTEs over subqueries is readability. Instead of nesting subqueries deeply, you define each step as a named CTE and compose them in the main query. This makes complex queries easier to understand, debug, and maintain.

Multiple CTEs

You can define multiple CTEs in a single query by separating them with commas. Each CTE can reference previously defined CTEs, creating a pipeline of data transformations.

-- Multiple CTEs for a sales report
WITH 
total_sales AS (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
),
customer_details AS (
    SELECT c.customer_id, c.customer_name, c.email,
           ts.total_spent
    FROM customers c
    JOIN total_sales ts ON c.customer_id = ts.customer_id
),
high_spenders AS (
    SELECT *
    FROM customer_details
    WHERE total_spent > 5000
)
SELECT customer_name, email, total_spent
FROM high_spenders
ORDER BY total_spent DESC;

Multiple CTEs allow you to build complex logic incrementally. Each CTE handles one step of the transformation, making the overall query easier to follow.

-- CTE referencing another CTE
WITH 
active_customers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= '2025-01-01'
),
active_with_details AS (
    SELECT ac.customer_id, c.customer_name, c.city
    FROM active_customers ac
    JOIN customers c ON ac.customer_id = c.customer_id
)
SELECT city, COUNT(*) AS active_customer_count
FROM active_with_details
GROUP BY city
ORDER BY active_customer_count DESC;

The order of CTE definitions matters. A CTE can only reference CTEs defined before it, not after. The main query can reference all CTEs.

Recursive CTE

A recursive CTE references itself, allowing you to traverse hierarchical data like org charts, file systems, or category trees. It uses UNION ALL to combine the base case (anchor) with the recursive case.

-- Employee org chart: find all subordinates of a manager
WITH RECURSIVE org_chart AS (
    -- Anchor: the top-level manager
    SELECT employee_id, employee_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive: find employees reporting to current level
    SELECT e.employee_id, e.employee_name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT employee_name, level
FROM org_chart
ORDER BY level, employee_name;

The recursive CTE has two parts:

1. Anchor member: The initial result set (non-recursive)
2. Recursive member: References the CTE itself, joined with the base table

-- Category hierarchy: find all subcategories
WITH RECURSIVE category_tree AS (
    SELECT category_id, category_name, parent_id, category_name AS path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT c.category_id, c.category_name, c.parent_id,
           CONCAT(ct.path, ' > ', c.category_name)
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT category_name, path
FROM category_tree;

Most databases require the RECURSIVE keyword. Always include a termination condition (like level < 10) to prevent infinite loops if the data contains cycles.

CTEs vs Subqueries

CTEs and subqueries serve similar purposes but differ in readability, reusability, and performance.

Readability: CTEs are defined at the top with meaningful names, making the query easier to read. Subqueries are nested inline, which can become confusing with multiple levels.

-- Subquery version (harder to read)
SELECT customer_name, total_spent
FROM customers c
JOIN (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
) s ON c.customer_id = s.customer_id
WHERE total_spent > (
    SELECT AVG(total_spent) FROM (
        SELECT SUM(total_amount) AS total_spent
        FROM orders GROUP BY customer_id
    ) avg_calc
);

-- CTE version (cleaner)
WITH customer_totals AS (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
),
avg_calc AS (
    SELECT AVG(total_spent) AS avg_spent FROM customer_totals
)
SELECT c.customer_name, ct.total_spent
FROM customers c
JOIN customer_totals ct ON c.customer_id = ct.customer_id
WHERE ct.total_spent > (SELECT avg_spent FROM avg_calc);

Reusability: A CTE can be referenced multiple times in the main query. A subquery must be duplicated if needed in multiple places.

Performance: Most databases optimize CTEs and subqueries similarly. Some databases (like SQL Server) may materialize CTEs, while others inline them. In PostgreSQL, CTEs are optimization fences by default (though this changed in version 12+).

Use CTEs when you have complex multi-step logic, need to reference the same derived result multiple want to improve query readability.