Let’s be honest: you’ve written a subquery. We all have. You nest a SELECT inside a FROM clause, pat yourself on the back, and run it. Then, a week later, you try to read that query again and it looks like a tangled mess of brackets and aliases that even its own mother couldn’t love. This is the problem the WITH clause—also known as a Common Table Expression or CTE—solves. It’s not some arcane performance hack; it’s a readability superpower. It allows you to name a subquery upfront and then reference that name later in your main query. Think of it as a CREATE VIEW statement that’s scoped to the life of a single query. It’s your chance to write a query that explains itself.

The basic syntax is stupidly simple, which is why it’s so brilliant. You start with WITH, give your subquery a name, and then use it like a regular table.

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
)
SELECT region, total_sales
FROM regional_sales
WHERE total_sales > 1000000
ORDER BY total_sales DESC;

See what we did there? We moved the gnarly aggregation and grouping logic into a neatly packaged, named block called regional_sales. The main SELECT becomes almost embarrassingly readable: “From our pre-calculated regional sales, show me the ones over a million, sorted.” You’re not just writing SQL for the database anymore; you’re writing it for the next human who has to read it (who, statistically speaking, is probably a future, slightly annoyed version of you).

Chaining CTEs Like a Pro

The real beauty begins when you chain these things together. You can define multiple CTEs in a single WITH clause, separated by commas. Each one can build on the previous, creating a logical pipeline of data transformation. This is where you leave nested-from-hell queries in the dust.

WITH
    cleaned_orders AS (
        -- First, get rid of the test data we forgot to purge
        SELECT *
        FROM orders
        WHERE account_id != 999
    ),
    regional_sales AS (
        -- Then, aggregate the clean data
        SELECT region, SUM(amount) AS total_sales
        FROM cleaned_orders
        GROUP BY region
    ),
    top_regions AS (
        -- Finally, filter to just the top performers
        SELECT region
        FROM regional_sales
        WHERE total_sales > 1000000
    )
-- Our main query is now a trivial join
SELECT r.region, s.total_sales
FROM top_regions r
JOIN regional_sales s ON r.region = s.region
ORDER BY s.total_sales DESC;

This is a narrative. It reads from top to bottom: “First, we clean the orders. Then, we turn them into regional sales. Then, we identify our top regions. Finally, we get the details for those top regions.” It’s modular. You can test each CTE individually by running just the query inside its parentheses. Try doing that easily with the fourth nested subquery in a 15-line FROM clause. I’ll wait.

The Scoping Rules (It’s Not a Temp Table)

Here’s a critical point that often trips people up: a CTE is not a temporary table. It’s a named query. The SQL engine effectively inlines the CTE’s logic wherever you use its name. This has two major implications.

First, you can only reference a CTE that you’ve already defined. The following is illegal and will rightly earn you an error.

WITH
    uses_future_cte AS ( -- Nope. 'future_cte' doesn't exist yet!
        SELECT * FROM future_cte
    ),
    future_cte AS (
        SELECT 1 AS col
    )
SELECT * FROM uses_future_cte;

Second, you can reference a CTE multiple times, but be warned: the query gets run each time. If you reference regional_sales twice in your main query, that aggregation is happening twice. For small datasets, who cares? For large ones, you might start to care a lot. In such cases, a temporary table might be a more performant choice, sacrificing some elegance for raw speed.

Materialize? Maybe, But Don’t Trust the Magic Wand

Some database systems, like PostgreSQL, offer a MATERIALIZED keyword in their CTE implementation. Using WITH regional_sales AS MATERIALIZED (...) tells the planner to evaluate the CTE once and store the results, effectively making it a temporary table. This can be a fantastic performance boost if you reference the CTE multiple times. But—and this is a big but—don’t just slap MATERIALIZED on everything. You’re forcing a materialization where the query planner might have found a more efficient way to inline it. Use it deliberately, like a surgeon’s scalpel, not a club. Test and compare execution plans. The WITH clause gives you the tools to write clearly; it’s still your job to use them wisely.