Right, so you’ve got the hang of a single CTE. It’s a neat way to tidy up a query. But the real party trick starts when you chain them together. Think of it like a production line: the output of one CTE becomes the input for the next. This is where you stop just writing queries and start designing them.

You’re not limited to just one. You can define multiple CTEs in a single WITH clause, separated by commas. The order of definition is your assembly line setup. The first one you define is the first step in the process, and the final SELECT statement at the end is the quality check that puts the finished product on the truck.

Chaining CTEs: A Production Line, Not a Buffet

The most important rule, and the one that trips people up the most, is that each CTE can only reference the ones that were defined before it. It’s a linear flow of data. You can’t have CTE B reference CTE C if C is defined after it. The database engine hasn’t built C yet when it’s trying to build B! It’s like trying to use a part that hasn’t been manufactured in the previous stage.

Let’s say we have an ecommerce.orders table and a products table. We want a report showing high-value orders for a specific product category.

WITH
-- First CTE: Get all orders for 'Gadgets'
gadget_orders AS (
    SELECT o.order_id, o.customer_id, o.total_amount, o.order_date
    FROM ecommerce.orders o
    JOIN ecommerce.order_items i ON o.order_id = i.order_id
    JOIN ecommerce.products p ON i.product_id = p.product_id
    WHERE p.category = 'Gadgets'
),
-- Second CTE: Uses the first one to find high-value orders
high_value_gadget_orders AS (
    SELECT *
    FROM gadget_orders
    WHERE total_amount > 1000
)
-- Final SELECT: Uses the second CTE to get our result
SELECT customer_id, COUNT(*) AS number_of_big_gadget_orders
FROM high_value_gadget_orders
GROUP BY customer_id
ORDER BY number_of_big_gadget_orders DESC;

See how that works? gadget_orders is built first. Then, high_value_gadget_orders takes the entire result set from gadget_orders and applies its own filter. Finally, the main query aggregates the already-filtered results from the second CTE. This is massively clearer than one giant, nested query and performs just as well.

The Power of Mental Modularity

This isn’t just about the database; it’s about your brain. Breaking a complex problem into discrete, named steps is a fundamental programming principle. SQL finally lets you do this natively. Each CTE becomes a module you can reason about and test independently. You can SELECT * FROM first_cte LIMIT 5; to quickly debug that step without running the whole monstrous query. This is a game-changer for development and maintenance.

The Nested WITH Clause Shibboleth

Here’s a weird edge case that feels like a design flaw but is actually in the SQL standard. You can also define a WITH clause inside the main query itself. This creates a CTE that’s only visible to that main query. It’s nesting, and it’s mostly confusing.

WITH
top_level_cte AS (
    SELECT customer_id FROM orders
)
SELECT *
FROM top_level_cte
WHERE customer_id IN (
    -- A CTE that ONLY exists inside this subquery
    WITH nested_cte AS (SELECT customer_id FROM high_value_customers)
    SELECT customer_id FROM nested_cte
);

Just because you can do this doesn’t mean you should. It’s a fantastic way to make your query utterly incomprehensible. The only time this might be vaguely justified is if you’re stuck in a database that doesn’t support multiple top-level CTEs (looking at you, older MySQL versions), but even then, it’s a code smell. Stick to the clean, linear, top-level chain. Your colleagues will thank you.

Best Practices: Don’t Outsmart Yourself

  1. Meaningful Names: cte1, cte2, cte3 is worse than no CTEs at all. Name them for what they do (filtered_orders, customer_totals), not their order.
  2. Performance Isn’t Magic: Each CTE is effectively a materialized temporary result set. For huge datasets, this can be expensive. The optimizer is smart, but chaining five massive CTEs might be slower than a well-tuned single query. Always check the execution plan if performance is critical.
  3. Know When to Stop: If you find yourself chaining more than four or five CTEs, pause. You might be trying to do too much in one query. Sometimes, breaking it into multiple queries or using a temporary table is the more maintainable choice.

The power here is control. You’re dictating the exact flow of execution, making your intent crystal clear, and building a complex result step-by-step. It’s the difference between giving someone a list of instructions and handing them a pre-assembled kit.