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.

Practice

1. What is the main purpose of using multiple CTEs (Common Table Expressions) in a single SQL query?

easy

A. To automatically create indexes on the tables used

B. To break down complex queries into simpler, named parts for better readability

C. To permanently store intermediate results in the database

D. To speed up the database engine by running queries in parallel

Solution

1. Step 1: Understand the role of CTEs

   CTEs are temporary named result sets used inside a query to organize complex logic.

2. Step 2: Identify the benefit of multiple CTEs

   Using multiple CTEs lets you split a big query into smaller, readable parts without permanent storage or indexing.

3. Final Answer:

   To break down complex queries into simpler, named parts for better readability -> Option B

4. Quick Check:

   Multiple CTEs improve query clarity = D [OK]

Hint: Multiple CTEs split queries into named parts for clarity [OK]

Common Mistakes:

• Thinking CTEs create permanent tables
• Assuming CTEs improve query speed automatically
• Confusing CTEs with indexes

2. Which of the following is the correct syntax to define two CTEs named cte1 and cte2 in one SQL query?

easy

A. WITH cte1 AS (SELECT * FROM table1), cte2 AS (SELECT * FROM table2) SELECT * FROM cte1 JOIN cte2 ON cte1.id = cte2.id;

B. WITH cte1 AS (SELECT * FROM table1) cte2 AS (SELECT * FROM table2) SELECT * FROM cte1 JOIN cte2 ON cte1.id = cte2.id;

C. WITH cte1 (SELECT * FROM table1); WITH cte2 (SELECT * FROM table2); SELECT * FROM cte1 JOIN cte2 ON cte1.id = cte2.id;

D. WITH cte1 = (SELECT * FROM table1), cte2 = (SELECT * FROM table2) SELECT * FROM cte1 JOIN cte2 ON cte1.id = cte2.id;

Solution

1. Step 1: Recall correct CTE syntax

   Multiple CTEs are separated by commas after WITH, each defined as name AS (query).

2. Step 2: Check each option

   WITH cte1 AS (SELECT * FROM table1), cte2 AS (SELECT * FROM table2) SELECT * FROM cte1 JOIN cte2 ON cte1.id = cte2.id; correctly uses commas and AS keyword; others miss commas or use wrong syntax.

3. Final Answer:

   WITH cte1 AS (SELECT * FROM table1), cte2 AS (SELECT * FROM table2) SELECT * FROM cte1 JOIN cte2 ON cte1.id = cte2.id; -> Option A

4. Quick Check:

   Multiple CTEs separated by commas = B [OK]

Hint: Separate multiple CTEs with commas after WITH [OK]

Common Mistakes:

• Omitting commas between CTEs
• Using semicolons between CTEs
• Using equals sign instead of AS

3. Given the following SQL query with multiple CTEs, what will be the output?

WITH cte1 AS (SELECT 1 AS num UNION ALL SELECT 2), cte2 AS (SELECT num FROM cte1 WHERE num = 2) SELECT * FROM cte2;

medium

A. 1 and 2

B. 1

C. 2

D. No rows returned

Solution

1. Step 1: Analyze cte1 content

   cte1 returns two rows with num values 1 and 2.

2. Step 2: Analyze cte2 filtering

   cte2 selects only rows from cte1 where num = 2, so only one row with num=2.

3. Final Answer:

   2 -> Option C

4. Quick Check:

   cte2 filters num=2 = A [OK]

Hint: Trace each CTE step-by-step to find final output [OK]

Common Mistakes:

• Assuming cte2 returns all rows from cte1
• Confusing UNION ALL with UNION
• Ignoring the WHERE filter in cte2

4. Identify the error in the following SQL query using multiple CTEs:

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

medium

A. CTEs cannot select from tables

B. CTEs must be named with different names

C. Missing JOIN condition between cte1 and cte2

D. Multiple WITH clauses are not allowed in one query

Solution

1. Step 1: Review CTE syntax rules

   Only one WITH clause is allowed per query; multiple CTEs must be separated by commas inside one WITH.

2. Step 2: Identify the error in the query

   The query uses two separate WITH clauses, which is invalid syntax.

3. Final Answer:

   Multiple WITH clauses are not allowed in one query -> Option D

4. Quick Check:

   Only one WITH clause per query = A [OK]

Hint: Use one WITH clause with commas for multiple CTEs [OK]

Common Mistakes:

• Writing multiple WITH clauses in one query
• Forgetting commas between CTEs
• Assuming CTEs create permanent tables

5. You want to calculate the average order amount per customer and then find customers whose average order is above 100. Which of the following queries correctly uses multiple CTEs to achieve this?

hard

A. WITH avg_orders AS (SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id), high_avg AS (SELECT customer_id FROM avg_orders WHERE avg_amount > 100) SELECT * FROM high_avg;

B. WITH avg_orders AS (SELECT customer_id, AVG(amount) AS avg_amount FROM orders), high_avg AS (SELECT customer_id FROM avg_orders WHERE avg_amount > 100) SELECT * FROM high_avg;

C. WITH avg_orders AS (SELECT customer_id, amount FROM orders GROUP BY customer_id), high_avg AS (SELECT customer_id FROM avg_orders WHERE amount > 100) SELECT * FROM high_avg;

D. WITH avg_orders AS (SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id) SELECT * FROM avg_orders WHERE avg_amount > 100;

Solution

1. Step 1: Calculate average order per customer

   WITH avg_orders AS (SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id), high_avg AS (SELECT customer_id FROM avg_orders WHERE avg_amount > 100) SELECT * FROM high_avg; correctly groups orders by customer_id and calculates AVG(amount).

2. Step 2: Filter customers with avg_amount > 100

   WITH avg_orders AS (SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id), high_avg AS (SELECT customer_id FROM avg_orders WHERE avg_amount > 100) SELECT * FROM high_avg; uses a second CTE to filter customers with average above 100, then selects from it.

3. Final Answer:

   WITH avg_orders AS (SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id), high_avg AS (SELECT customer_id FROM avg_orders WHERE avg_amount > 100) SELECT * FROM high_avg; -> Option A

4. Quick Check:

   Multiple CTEs for calculation and filtering = C [OK]

Hint: Use one CTE to calculate, second to filter results [OK]

Common Mistakes:

• Missing GROUP BY when calculating averages
• Not filtering on aggregated column
• Incorrect CTE definitions without commas