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

Concept Flow - Multiple CTEs in one query

Start Query

↓

Define CTE 1

↓

Define CTE 2

↓

Use CTEs in Main Query

↓

Execute Main Query

↓

Return Result

The query starts by defining multiple CTEs one after another, then uses them in the main query to produce the final result.

Execution Sample

PostgreSQL

WITH cte1 AS (
  SELECT 1 AS num
),
cte2 AS (
  SELECT num + 1 AS num_plus_one FROM cte1
)
SELECT * FROM cte2;

This query defines two CTEs: cte1 with a number 1, and cte2 which adds 1 to cte1's number, then selects from cte2.

Execution Table

Step	Action	CTE1 Result	CTE2 Result	Main Query Output
1	Evaluate cte1	[{num: 1}]	N/A	N/A
2	Evaluate cte2 using cte1	[{num: 1}]	[{num_plus_one: 2}]	N/A
3	Execute main query selecting from cte2	[{num: 1}]	[{num_plus_one: 2}]	[{num_plus_one: 2}]
4	End of query execution	[{num: 1}]	[{num_plus_one: 2}]	[{num_plus_one: 2}]

💡 All CTEs evaluated and main query executed, returning final result.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
cte1	undefined	[{num: 1}]	[{num: 1}]	[{num: 1}]
cte2	undefined	undefined	[{num_plus_one: 2}]	[{num_plus_one: 2}]
main_query_output	undefined	undefined	undefined	[{num_plus_one: 2}]

Key Moments - 2 Insights

Why does cte2 depend on cte1's result?

Because cte2 uses cte1 in its SELECT statement, it must wait until cte1 is evaluated (see execution_table step 2).

Can the main query use both cte1 and cte2?

Yes, the main query can use any or all defined CTEs after they are evaluated (see execution_table step 3).

Visual Quiz - 3 Questions

Look at the execution table, what is the output of cte1 after step 1?

A [{num_plus_one: 2}]

B [{num: 1}]

C N/A

D undefined

💡 Hint

Check the 'CTE1 Result' column at step 1 in the execution_table.

At which step does the main query produce its output?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Main Query Output' column in the execution_table.

If cte2 did not use cte1, how would the execution_table change?

A cte2 would be evaluated independently at step 2

B cte1 would not be evaluated

C Main query would run before cte2

D There would be no main query output

💡 Hint

Consider dependency order shown in execution_table steps 1 and 2.

Concept Snapshot

WITH cte1 AS (subquery1),
     cte2 AS (subquery2),
     ...
SELECT ... FROM cte1, cte2;

- Define multiple CTEs separated by commas.
- Later CTEs can use earlier ones.
- Main query uses these CTEs to get final results.

Full Transcript

This visual execution shows how multiple Common Table Expressions (CTEs) work in one PostgreSQL query. First, cte1 is defined and evaluated, producing a result. Then cte2 is defined using cte1's result and evaluated. Finally, the main query selects from cte2 and returns the final output. Variables track the state of each CTE and the main query output after each step. Key moments clarify dependencies between CTEs and usage in the main query. The quiz tests understanding of the evaluation order and outputs.