Home/Learn/SQL/CTE (WITH Clause)

CTE (WITH Clause)

Intermediate

Subqueries & CTEs

A Common Table Expression (WITH clause) gives a subquery a name, improving readability and enabling reuse within the same query without creating a temporary table.

Overview

A CTE is defined with the WITH keyword before the main SELECT and can be referenced by name one or more times in the query body. Multiple CTEs can be chained in a single WITH block, each building on previous ones. For readability, CTEs are almost always superior to deeply nested subqueries. For performance, CTEs are typically treated as inline views (the planner can look through them), but PostgreSQL 12+ introduced the MATERIALIZED / NOT MATERIALIZED hints to control whether the CTE result is cached as a temporary table or inlined each time. Unlike temp tables, CTEs do not survive beyond the statement. They are ideal for multi-step analytical queries where each step produces a clean intermediate result.

Multi-Step Order Analysis with Chained CTEs

Break a complex query into named steps. Each CTE builds on the previous one. Compare the readable CTE version with an equivalent nested subquery to appreciate the difference.

SQL — 4-step chained CTEs for order analysis

-- Goal: for each city, find the top product category by revenue last month

-- Step 1: orders in the last 30 days
WITH recent_orders AS (
    SELECT o.id, o.user_id, o.product_id, o.amount
    FROM orders o
    WHERE o.created_at >= NOW() - INTERVAL '30 days'
      AND o.status = 'completed'
),

-- Step 2: join to get city and category
enriched AS (
    SELECT
        u.city,
        p.category,
        ro.amount
    FROM recent_orders ro
    JOIN users    u ON u.id = ro.user_id
    JOIN products p ON p.id = ro.product_id
),

-- Step 3: revenue by (city, category)
revenue_by_city_cat AS (
    SELECT city, category, SUM(amount) AS revenue
    FROM enriched
    GROUP BY city, category
),

-- Step 4: rank categories within each city
ranked AS (
    SELECT
        city, category, revenue,
        RANK() OVER (PARTITION BY city ORDER BY revenue DESC) AS rnk
    FROM revenue_by_city_cat
)

-- Final: top category per city
SELECT city, category, revenue
FROM ranked
WHERE rnk = 1
ORDER BY revenue DESC;

CTE vs Nested Subquery — Readability Comparison

The same logic expressed as nested subqueries quickly becomes unreadable. The CTE version is easier to review, debug, and extend.

SQL — equivalent nested subquery (readability contrast)

-- Same query as nested subqueries (no CTEs):
SELECT city, category, revenue
FROM (
    SELECT city, category, revenue,
           RANK() OVER (PARTITION BY city ORDER BY revenue DESC) AS rnk
    FROM (
        SELECT u.city, p.category, SUM(o.amount) AS revenue
        FROM (
            SELECT id, user_id, product_id, amount
            FROM orders
            WHERE created_at >= NOW() - INTERVAL '30 days'
              AND status = 'completed'
        ) o
        JOIN users u ON u.id = o.user_id
        JOIN products p ON p.id = o.product_id
        GROUP BY u.city, p.category
    ) revenue_data
) ranked
WHERE rnk = 1;
-- Identical result, but mentally you must read inside-out.

Materialisation Hints — MATERIALIZED vs NOT MATERIALIZED

PostgreSQL 12+ lets you control whether a CTE is materialised into a temp buffer (MATERIALIZED) or inlined into the main query (NOT MATERIALIZED). Use MATERIALIZED to force caching of an expensive CTE used multiple times.

SQL — MATERIALIZED / NOT MATERIALIZED hints (PostgreSQL 12+)

-- Default (PostgreSQL 12+): planner decides whether to inline or materialise.

-- Force materialisation: useful when the CTE is referenced more than once
-- and computing it twice would be expensive.
WITH expensive_agg AS MATERIALIZED (
    SELECT department_id, AVG(salary) AS avg_sal, COUNT(*) AS headcount
    FROM employees
    GROUP BY department_id
)
SELECT
    e.name, e.salary, ea.avg_sal, ea.headcount
FROM employees e
JOIN expensive_agg ea ON ea.department_id = e.department_id
WHERE e.salary > ea.avg_sal;

-- NOT MATERIALIZED: tell the planner to inline (treat like a view/subquery)
-- Useful when you want the planner to push predicates inside the CTE.
WITH filtered AS NOT MATERIALIZED (
    SELECT * FROM orders WHERE status = 'completed'
)
SELECT user_id, COUNT(*) FROM filtered
WHERE created_at >= '2024-01-01'
GROUP BY user_id;

Key Points to Remember

1CTEs are defined before the main SELECT with the WITH keyword and referenced by name in the query body.
2Multiple CTEs in one WITH block are separated by commas and can reference earlier CTEs in the same block.
3CTEs improve readability over nested subqueries by naming each logical step.
4In PostgreSQL 12+, the planner inlines CTEs by default; use MATERIALIZED to cache an expensive CTE used multiple times.
5CTEs do not persist beyond the query — use a temporary table if you need the intermediate result in multiple statements.
6A CTE cannot be referenced in a sibling CTE, only in CTEs defined after it or in the main query.

Interview Questions

What is the difference between a CTE and a subquery in terms of readability and performance?

MediumAmazon

When would you use MATERIALIZED vs NOT MATERIALIZED in PostgreSQL?

HardGoogle

Can a CTE reference another CTE defined in the same WITH block? Explain with an example.

EasySwiggy

How is a CTE different from a temporary table?

EasyAtlassian

Ask Aria about CTE (WITH Clause)

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

IN vs EXISTS vs ANY vs ALL

Recursive CTE