Multiple CTEs in one query in PostgreSQL - Deep Dive

Overview - Multiple CTEs in one query

What is it?

Multiple CTEs (Common Table Expressions) in one query means defining several temporary named result sets at the start of a query. Each CTE can be used like a temporary table within the main query. This helps break complex queries into smaller, readable parts. It is like creating mini-steps before the final result.

Why it matters

Without multiple CTEs, complex queries become hard to read and maintain. You might repeat the same subquery many times, making the query slower and error-prone. Multiple CTEs let you organize logic clearly, improve performance, and make debugging easier. This saves time and reduces mistakes in real projects.

Where it fits

Before learning multiple CTEs, you should know basic SQL SELECT queries and simple CTEs. After mastering multiple CTEs, you can learn recursive CTEs, window functions, and query optimization techniques. This topic is a bridge from simple queries to advanced SQL.

Mental Model

Core Idea

Multiple CTEs let you build step-by-step temporary tables that feed into each other to simplify complex queries.

Think of it like...

It's like cooking a meal with several prep bowls: you chop vegetables in one bowl, marinate meat in another, and mix sauce in a third, then combine everything to cook the final dish.

WITH
├─ cte1 AS (SELECT ...),
├─ cte2 AS (SELECT ... FROM cte1),
└─ cte3 AS (SELECT ... FROM cte2)
SELECT * FROM cte3;

Build-Up - 7 Steps

1

Foundation

Understanding Single CTE Basics

Concept: Learn what a single CTE is and how it works as a temporary named result set.

A CTE starts with WITH keyword followed by a name and a query in parentheses. This named query can be used in the main SELECT. For example: WITH cte AS (SELECT id, name FROM users) SELECT * FROM cte; This runs the inner query first and then selects from it.

Result

The query returns all rows from the users table with columns id and name.

Understanding a single CTE is key because multiple CTEs are just several of these chained together.

2

Foundation

Using CTEs to Simplify Queries

Concept: See how CTEs help break down complex queries into readable parts.

Instead of writing a long query with nested subqueries, you can name parts with CTEs: WITH recent_orders AS ( SELECT * FROM orders WHERE order_date > '2024-01-01' ) SELECT * FROM recent_orders WHERE total > 100; This separates filtering recent orders from the final selection.

Result

The query returns orders after 2024-01-01 with totals over 100.

Breaking queries into parts with CTEs makes them easier to read and maintain.

3

Intermediate

Defining Multiple CTEs Together

🤔Before reading on: do you think multiple CTEs run in parallel or sequentially? Commit to your answer.

Concept: Learn how to write several CTEs separated by commas and how they can depend on each other.

You can define multiple CTEs by separating them with commas: WITH cte1 AS (SELECT * FROM users WHERE active = true), cte2 AS (SELECT * FROM orders WHERE user_id IN (SELECT id FROM cte1)) SELECT * FROM cte2; Here, cte2 uses cte1 inside its query.

Result

The query returns orders made by active users.

Multiple CTEs run in order, so later CTEs can use earlier ones, enabling stepwise query building.

4

Intermediate

Referencing Earlier CTEs in Later Ones

🤔Before reading on: can a CTE refer to a CTE defined after it? Commit to yes or no.

Concept: Understand that CTEs can only refer to those defined before them, not after.

In this example: WITH cte1 AS (SELECT * FROM users), cte2 AS (SELECT * FROM orders WHERE user_id IN (SELECT id FROM cte1)) SELECT * FROM cte2; cte2 can use cte1, but if you swap their order, it will cause an error.

Result

The query runs successfully only if cte1 is defined before cte2.

CTEs are processed top-down, so forward references are not allowed, which enforces logical flow.

5

Intermediate

Using Multiple CTEs for Complex Data Transformations

Concept: See how multiple CTEs can chain transformations step-by-step.

Example: WITH filtered_users AS (SELECT * FROM users WHERE signup_date > '2023-01-01'), user_orders AS (SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id), active_users AS ( SELECT f.id, f.name, o.order_count FROM filtered_users f JOIN user_orders o ON f.id = o.user_id WHERE o.order_count > 5 ) SELECT * FROM active_users; This breaks the logic into filtering, counting, and joining.

Result

The query returns users who signed up after 2023-01-01 and have more than 5 orders.

Chaining CTEs lets you build complex queries in clear, manageable steps.

6

Advanced

Performance Considerations with Multiple CTEs

🤔Before reading on: do you think CTEs always improve query speed? Commit to yes or no.

Concept: Learn how multiple CTEs affect query planning and execution performance.

In PostgreSQL, CTEs act as optimization fences by default, meaning each CTE is executed separately and results stored temporarily. This can slow down queries if CTEs are large or repeated. However, PostgreSQL 12+ can inline CTEs sometimes, improving speed. Understanding when CTEs help or hurt performance is key.

Result

Using many large CTEs may slow queries unless PostgreSQL can inline them.

Knowing CTE execution behavior helps write queries that balance readability and performance.

7

Expert

Advanced Chaining and Recursive CTEs

🤔Before reading on: can multiple CTEs include recursive ones in the same WITH clause? Commit to yes or no.

Concept: Explore mixing multiple CTEs including recursive CTEs for hierarchical data.

You can define multiple CTEs where one is recursive: WITH RECURSIVE cte1 AS (SELECT id, parent_id FROM categories WHERE parent_id IS NULL), cte2 AS ( SELECT id, parent_id FROM cte1 UNION ALL SELECT c.id, c.parent_id FROM categories c JOIN cte2 ON c.parent_id = cte2.id ) SELECT * FROM cte2; This finds all categories and their descendants.

Result

The query returns a full hierarchy of categories starting from root nodes.

Combining multiple and recursive CTEs unlocks powerful hierarchical queries in a single statement.

Under the Hood

When a query with multiple CTEs runs, PostgreSQL processes each CTE in order. Each CTE is like a temporary result set stored in memory or disk. By default, CTEs act as optimization fences, so the planner executes them separately before the main query. Later CTEs can reference earlier ones, creating a chain of temporary tables. This isolation ensures clarity but can affect performance.

Why designed this way?

CTEs were designed to improve query readability and modularity by naming subqueries. The optimization fence behavior was chosen to guarantee predictable execution and prevent unexpected side effects. Alternatives like inline subqueries were less readable and harder to debug. Over time, PostgreSQL added inlining to improve performance while keeping clarity.

WITH clause processing order:

┌─────────────┐
│   CTE1      │
│ (exec first)│
└─────┬───────┘
      │
┌─────▼───────┐
│   CTE2      │
│ (uses CTE1) │
└─────┬───────┘
      │
┌─────▼───────┐
│   CTE3      │
│ (uses CTE2) │
└─────┬───────┘
      │
┌─────▼───────┐
│ Main Query  │
│ (uses CTEs) │
└─────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do multiple CTEs always run faster than nested subqueries? Commit to yes or no.

Common Belief: Multiple CTEs always improve query speed compared to nested subqueries.

Reality: CTEs can slow down queries because they act as optimization fences, preventing some planner optimizations.

Why it matters: Assuming CTEs always speed up queries can lead to slower performance in production systems.

Quick: Can a CTE refer to another CTE defined after it? Commit to yes or no.

Common Belief: CTEs can reference any other CTE in the same WITH clause regardless of order.

Reality: CTEs can only reference CTEs defined before them, not after.

Why it matters: Trying to reference a later CTE causes errors and breaks query logic.

Quick: Are CTEs always materialized as temporary tables? Commit to yes or no.

Common Belief: CTEs are always materialized and stored separately before the main query runs.

Reality: PostgreSQL 12+ can inline CTEs (treat them like subqueries) if safe, avoiding materialization.

Why it matters: Knowing this helps optimize queries by writing CTEs that can be inlined for better performance.

Quick: Does using multiple CTEs guarantee easier debugging? Commit to yes or no.

Common Belief: Multiple CTEs always make debugging easier than nested queries.

Reality: While CTEs improve readability, complex chains can still be hard to debug if not well named or documented.

Why it matters: Overusing CTEs without clear naming can confuse developers and slow down troubleshooting.

Expert Zone

1

CTEs act as optimization fences by default, but PostgreSQL 12+ can inline them selectively, affecting performance subtly.

2

The order of CTE definitions matters not just for references but also for planner decisions and execution order.

3

Recursive CTEs combined with multiple CTEs allow elegant solutions for hierarchical and graph queries in a single statement.

When NOT to use

Avoid multiple CTEs when simple joins or subqueries suffice, especially if performance is critical and CTEs cause materialization overhead. Use inline subqueries or lateral joins as alternatives for better optimization.

Production Patterns

In production, multiple CTEs are used to modularize complex reports, prepare intermediate aggregates, and implement stepwise data transformations. Recursive CTEs combined with multiple CTEs handle organizational charts, bill of materials, and tree traversals efficiently.

Connections

Modular Programming

Multiple CTEs build queries in modular steps similar to how functions break code into reusable parts.

Understanding modular programming helps grasp why breaking queries into multiple CTEs improves clarity and maintainability.

Data Pipelines

Multiple CTEs resemble stages in a data pipeline where each step transforms data before passing it on.

Seeing CTEs as pipeline stages clarifies how data flows and transforms inside a query.

Mathematical Function Composition

Multiple CTEs compose queries stepwise like composing functions where output of one is input to next.

Knowing function composition helps understand chaining CTEs to build complex results from simple parts.

Common Pitfalls

#1 Referencing a CTE before it is defined.

Wrong approach:

WITH
cte2 AS (SELECT * FROM cte1),
cte1 AS (SELECT * FROM users)
SELECT * FROM cte2;

Correct approach:

WITH
cte1 AS (SELECT * FROM users),
cte2 AS (SELECT * FROM cte1)
SELECT * FROM cte2;

Root cause:

CTEs must be defined in order because later CTEs can reference earlier ones, but not vice versa.

#2 Assuming multiple CTEs always improve performance.

Wrong approach:

WITH
cte1 AS (SELECT * FROM large_table),
cte2 AS (SELECT * FROM cte1 WHERE condition)
SELECT * FROM cte2;

Correct approach:

Rewrite query using joins or inline subqueries when performance is critical, or check if PostgreSQL can inline CTEs:
SELECT * FROM large_table WHERE condition;

Root cause:

CTEs act as optimization fences by default, causing separate execution and possible slowdowns.

#3 Overusing multiple CTEs without clear naming or documentation.

Wrong approach:

WITH
a AS (SELECT ...),
b AS (SELECT ... FROM a),
c AS (SELECT ... FROM b)
SELECT * FROM c;

Correct approach:

WITH
active_users AS (SELECT ...),
recent_orders AS (SELECT ... FROM active_users),
high_value_orders AS (SELECT ... FROM recent_orders)
SELECT * FROM high_value_orders;

Root cause:

Poor naming makes queries hard to read and debug despite using multiple CTEs.

Key Takeaways

Multiple CTEs let you break complex queries into clear, manageable steps by defining several temporary named result sets.

CTEs run in order, so later CTEs can use earlier ones, but not the other way around.

While multiple CTEs improve readability, they can affect performance because they act as optimization fences unless the database inlines them.

Combining multiple and recursive CTEs enables powerful queries for hierarchical and graph data.

Good naming and understanding CTE execution order are essential to avoid common mistakes and write maintainable queries.