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.