Multiple CTEs in one query in SQL - Step-by-Step Execution

Concept Flow - Multiple CTEs in one query

Define CTE1

↓

Define CTE2

→Use CTE1 and CTE2 in main query

↓

End

First, you define multiple CTEs one after another. Then, you use them together in the main query to get the final result.

Execution Sample

SQL

WITH
  cte1 AS (SELECT 1 AS num),
  cte2 AS (SELECT 2 AS num)
SELECT cte1.num + cte2.num AS sum
FROM cte1, cte2;

This query defines two CTEs and then adds their numbers together in the main query.

Execution Table

Step	Action	CTE1 Content	CTE2 Content	Main Query Result
1	Define cte1	[{num:1}]	N/A	N/A
2	Define cte2	[{num:1}]	[{num:2}]	N/A
3	Execute main query joining cte1 and cte2	[{num:1}]	[{num:2}]	[{sum:3}]
4	Return final result	[{num:1}]	[{num:2}]	[{sum:3}]

💡 All CTEs defined and main query executed, returning the sum of numbers.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
cte1	undefined	[{num:1}]	[{num:1}]	[{num:1}]
cte2	undefined	undefined	[{num:2}]	[{num:2}]
main_query_result	undefined	undefined	undefined	[{sum:3}]

Key Moments - 2 Insights

Why do we separate multiple CTEs with commas?

Because in the execution_table rows 1 and 2, each CTE is defined one after another separated by commas to tell SQL these are separate named queries.

How does the main query use the CTEs?

As shown in execution_table row 3, the main query treats CTEs like tables and can join or combine their results to produce the final output.

Visual Quiz - 3 Questions

Look at the execution_table, what is the content of cte2 after step 2?

A undefined

B [{num:1}]

C [{num:2}]

D [{sum:3}]

💡 Hint

Check the 'CTE2 Content' column at step 2 in the execution_table.

At which step does the main query produce the final sum?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Look at the 'Main Query Result' column in the execution_table to see when the sum appears.

If we remove cte2, what would happen to the main query result?

A It would still return sum 3

B It would cause an error

C It would return sum 1

D It would return sum 2

💡 Hint

Refer to variable_tracker and execution_table where main query uses both cte1 and cte2.

Concept Snapshot

Multiple CTEs let you define several temporary named result sets separated by commas.
Use WITH cte1 AS (...), cte2 AS (...) before the main query.
Then refer to these CTEs like tables in your main SELECT.
This helps organize complex queries step-by-step.
All CTEs must be defined before the main query runs.

Full Transcript

This visual execution shows how multiple CTEs are defined and used in one SQL query. First, cte1 is defined with a simple SELECT returning 1. Then cte2 is defined returning 2. Both are separated by commas. Next, the main query selects from both cte1 and cte2, adding their numbers together to get 3. The execution table tracks each step, showing the content of each CTE and the final result. The variable tracker shows how cte1, cte2, and the main query result change over time. Key moments clarify why commas separate CTEs and how the main query uses them. The quiz tests understanding of CTE contents and query results at each step. This helps beginners see how multiple CTEs work together 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