Multiple CTEs in one query in SQL - Deep Dive

Overview - Multiple CTEs in one query

What is it?

Multiple CTEs in one query means using several temporary named result sets, called Common Table Expressions (CTEs), within a single SQL query. Each CTE is like a mini-table created on the fly that you can use later in the main query or other CTEs. This helps organize complex queries by breaking them into smaller, readable parts. You write them at the start of your query using the WITH keyword, separated by commas.

Why it matters

Without multiple CTEs, complex queries become hard to read, write, and maintain because all logic is crammed into one big statement. Multiple CTEs let you build step-by-step results, making your SQL easier to understand and debug. This saves time and reduces errors when working with databases, especially in real projects with many data transformations.

Where it fits

Before learning multiple CTEs, you should understand basic SQL SELECT queries and single CTE usage. After mastering multiple CTEs, you can explore advanced SQL topics like recursive CTEs, window functions, and query optimization techniques.

Mental Model

Core Idea

Multiple CTEs let you create several named temporary tables in one query to build complex results step-by-step.

Think of it like...

It's like preparing ingredients separately before cooking a meal: you chop vegetables, marinate meat, and measure spices in different bowls, then combine them to make the final dish.

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

Build-Up - 6 Steps

1

Foundation

Understanding Single CTE Basics

Concept: Learn what a Common Table Expression (CTE) is and how to use one in a simple query.

A CTE is a temporary named result set defined using WITH before the main query. For example: WITH first_cte AS ( SELECT id, name FROM users WHERE active = 1 ) SELECT * FROM first_cte; This creates a temporary table called first_cte with active users, which the main query then selects from.

Result

The query returns all active users from the users table.

Understanding a single CTE shows how SQL can break down queries into readable parts, making complex logic easier to manage.

2

Foundation

Basic Syntax for Multiple CTEs

Concept: Learn how to write multiple CTEs separated by commas in one WITH clause.

You can define several CTEs in one query by separating them with commas: WITH cte1 AS ( SELECT * FROM orders WHERE status = 'pending' ), cte2 AS ( SELECT * FROM customers WHERE region = 'East' ) SELECT * FROM cte1 JOIN cte2 ON cte1.customer_id = cte2.id; This creates two temporary tables, cte1 and cte2, used in the final SELECT.

Result

The query returns pending orders joined with customers from the East region.

Knowing the syntax for multiple CTEs allows you to organize different parts of your query logic clearly and reuse intermediate results.

3

Intermediate

Referencing Earlier CTEs in Later Ones

🤔Before reading on: Do you think later CTEs can use earlier CTEs in the same WITH clause? Commit to yes or no.

Concept: Later CTEs can use the results of earlier CTEs, enabling stepwise data transformations.

In multiple CTEs, each CTE can refer to any CTE defined before it. For example: WITH cte1 AS ( SELECT id, amount FROM sales WHERE amount > 100 ), cte2 AS ( SELECT id, amount, amount * 0.1 AS tax FROM cte1 ) SELECT * FROM cte2; Here, cte2 uses cte1's results to calculate tax.

Result

The query returns sales with amounts over 100 and their calculated tax.

Understanding that CTEs can build on each other lets you create complex, layered queries that are easier to read and maintain.

4

Intermediate

Using Multiple CTEs for Complex Joins

🤔Before reading on: Can multiple CTEs simplify queries with many joins? Commit to yes or no.

Concept: Multiple CTEs help break down complex joins into manageable parts.

Instead of writing one big join, split it into steps: WITH recent_orders AS ( SELECT * FROM orders WHERE order_date > '2024-01-01' ), customer_info AS ( SELECT id, name FROM customers ), order_details AS ( SELECT order_id, product_id, quantity FROM order_items ) SELECT ro.id, ci.name, od.product_id, od.quantity FROM recent_orders ro JOIN customer_info ci ON ro.customer_id = ci.id JOIN order_details od ON ro.id = od.order_id; This breaks the query into clear parts.

Result

The query returns recent orders with customer names and order details.

Breaking complex joins into multiple CTEs improves query clarity and debugging ease.

5

Advanced

Performance Considerations with Multiple CTEs

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

Concept: Multiple CTEs improve readability but may affect performance depending on the database engine and query plan.

Some databases treat CTEs as inline views (optimized away), while others materialize them (store temporarily). Materialization can slow queries if CTEs are large or used multiple times. For example, repeating a large CTE in multiple places may cause repeated work. Understanding your database's behavior helps write efficient queries.

Result

Query performance may vary; sometimes multiple CTEs slow down execution.

Knowing how your database handles CTEs helps balance readability with performance.

6

Expert

Recursive CTEs Combined with Multiple CTEs

🤔Before reading on: Can recursive CTEs be combined with other CTEs in one query? Commit to yes or no.

Concept: You can mix recursive CTEs with multiple non-recursive CTEs to solve complex hierarchical queries stepwise.

Example combining recursive and regular CTEs: WITH RECURSIVE employee_hierarchy AS ( SELECT id, manager_id, name FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.manager_id, e.name FROM employees e JOIN employee_hierarchy eh ON e.manager_id = eh.id ), active_employees AS ( SELECT * FROM employee_hierarchy WHERE active = 1 ) SELECT * FROM active_employees; This finds all active employees in the hierarchy.

Result

The query returns all active employees with their hierarchical relationships.

Combining recursive and multiple CTEs unlocks powerful ways to handle complex data relationships in one query.

Under the Hood

When a query with multiple CTEs runs, the database processes each CTE in order. Some databases inline CTEs, treating them like temporary views merged into the main query. Others materialize CTEs, creating temporary storage for their results. Later CTEs can reference earlier ones, so the engine resolves dependencies step-by-step. The final main query then uses these prepared results to produce the output.

Why designed this way?

Multiple CTEs were designed to improve query readability and modularity. Before CTEs, complex queries used nested subqueries or temporary tables, which were hard to read and maintain. CTEs provide a clear, declarative way to name intermediate results. Allowing multiple CTEs in one WITH clause lets users build layered logic without cluttering the main query.

WITH clause processing order:

WITH
┌───────────┐
│  cte1     │
└────┬──────┘
     │
┌────▼──────┐
│  cte2     │ (can use cte1)
└────┬──────┘
     │
┌────▼──────┐
│  cte3     │ (can use cte1, cte2)
└────┬──────┘
     │
┌────▼──────┐
│ main query│ (uses cte1, cte2, cte3)
└───────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do multiple CTEs always improve query performance? Commit to yes or no.

Common Belief: Using multiple CTEs always makes queries faster because they break down logic.

Reality: Multiple CTEs improve readability but can sometimes slow queries if the database materializes them multiple times.

Why it matters: Assuming CTEs always speed up queries can lead to slow, inefficient database operations in production.

Quick: Can you reference a CTE defined after the current one in the same WITH clause? Commit to yes or no.

Common Belief: You can reference any CTE in the WITH clause regardless of order.

Reality: Later CTEs can reference earlier ones, but not the other way around; order matters.

Why it matters: Ignoring CTE order causes syntax errors and confusion when building layered queries.

Quick: Does defining multiple CTEs create permanent tables in the database? Commit to yes or no.

Common Belief: CTEs create permanent tables you can reuse later.

Reality: CTEs are temporary and exist only during query execution; they do not persist.

Why it matters: Misunderstanding this leads to expecting data persistence and reusability that doesn't exist.

Quick: Can you use multiple CTEs to replace all subqueries? Commit to yes or no.

Common Belief: Multiple CTEs can always replace subqueries for better clarity.

Reality: While often clearer, some subqueries are simpler or more efficient than multiple CTEs.

Why it matters:

Expert Zone

1

Some databases materialize CTEs by default, causing repeated computation if referenced multiple times; knowing this helps optimize queries.

2

Recursive CTEs combined with multiple CTEs allow elegant solutions for hierarchical and iterative data problems in one query.

3

CTEs do not create indexes, so large CTEs may slow down queries compared to temporary tables with indexes.

When NOT to use

Avoid multiple CTEs when performance is critical and the database materializes them repeatedly; consider temporary tables or inline subqueries instead. Also, for very simple queries, multiple CTEs may add unnecessary complexity.

Production Patterns

In production, multiple CTEs are used to modularize complex reporting queries, build layered data transformations, and combine recursive logic with filtering. They help teams maintain and debug SQL codebases by separating concerns clearly.

Connections

Modular Programming

Multiple CTEs build on the idea of breaking complex tasks into smaller, reusable parts.

Understanding modular programming helps grasp why multiple CTEs improve query clarity and maintainability.

Functional Composition

Multiple CTEs compose intermediate results step-by-step, similar to composing functions in programming.

Knowing function composition clarifies how each CTE transforms data before passing it on.

Cooking Recipes

Like preparing ingredients separately before combining, multiple CTEs prepare data pieces before final use.

This cross-domain view shows how breaking down complex tasks into steps is a universal problem-solving approach.

Common Pitfalls

#1 Referencing a CTE before it is defined in the WITH clause.

Wrong approach:

WITH cte2 AS (SELECT * FROM cte1), cte1 AS (SELECT 1 AS id)
SELECT * FROM cte2;

Correct approach:

WITH cte1 AS (SELECT 1 AS id), cte2 AS (SELECT * FROM cte1)
SELECT * FROM cte2;

Root cause:

Misunderstanding that CTEs must be defined in order because later CTEs can only reference earlier ones.

#2 Assuming multiple CTEs always improve query speed without checking execution plans.

Wrong approach:

Using many large CTEs without analyzing performance, e.g., multiple heavy joins in separate CTEs.

Correct approach:

Analyze query plans and consider temporary tables or inline views if performance suffers.

Root cause:

Believing readability always equals performance, ignoring database engine behavior.

#3 Expecting CTEs to persist data beyond the query execution.

Wrong approach:

Creating a CTE and trying to query it later in a separate SQL statement.

Correct approach:

Use temporary tables or permanent tables if data persistence is needed.

Root cause:

Confusing temporary query-scoped CTEs with physical tables.

Key Takeaways

Multiple CTEs let you break complex SQL queries into clear, manageable parts by creating several temporary named result sets.

Later CTEs can reference earlier ones, enabling stepwise data transformations and layered logic.

While multiple CTEs improve readability, they do not always improve performance; understanding your database's execution behavior is crucial.

CTEs are temporary and exist only during query execution; they do not create permanent tables.

Combining recursive and multiple CTEs unlocks powerful ways to handle hierarchical and iterative data problems in one query.