CTE referencing another CTE in SQL - Step-by-Step Execution

Concept Flow - CTE referencing another CTE

Define CTE1

↓

CTE1 Result Ready

↓

Define CTE2 referencing CTE1

↓

CTE2 Result Ready

↓

Main Query uses CTE2

↓

Final Result Returned

First, define the first CTE. Then define the second CTE that uses the first CTE. Finally, the main query uses the second CTE to produce the final result.

Execution Sample

SQL

WITH cte1 AS (
  SELECT id, name FROM users WHERE active = 1
),
cte2 AS (
  SELECT id, name FROM cte1 WHERE name LIKE 'A%'
)
SELECT * FROM cte2;

This query defines two CTEs: cte1 filters active users, cte2 filters those whose names start with 'A', then selects from cte2.

Execution Table

Step	Action	Query Part	Result Preview
1	Evaluate cte1	SELECT id, name FROM users WHERE active = 1	[{id:1, name:'Alice'}, {id:3, name:'Adam'}, {id:5, name:'Bob'}]
2	Evaluate cte2 using cte1	SELECT id, name FROM cte1 WHERE name LIKE 'A%'	[{id:1, name:'Alice'}, {id:3, name:'Adam'}]
3	Main query selects from cte2	SELECT * FROM cte2	[{id:1, name:'Alice'}, {id:3, name:'Adam'}]
4	End	No more steps	Final result returned

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

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
cte1	undefined	[{id:1, name:'Alice'}, {id:3, name:'Adam'}, {id:5, name:'Bob'}]	[{id:1, name:'Alice'}, {id:3, name:'Adam'}, {id:5, name:'Bob'}]	[{id:1, name:'Alice'}, {id:3, name:'Adam'}, {id:5, name:'Bob'}]
cte2	undefined	undefined	[{id:1, name:'Alice'}, {id:3, name:'Adam'}]	[{id:1, name:'Alice'}, {id:3, name:'Adam'}]

Key Moments - 2 Insights

Why does cte2 depend on cte1's result before it can be evaluated?

Because cte2's query uses cte1 as its data source, it must wait until cte1 is fully evaluated (see execution_table step 2) to filter its rows.

Can the main query use cte1 directly if cte2 references cte1?

Yes, the main query can use any CTE defined before it, including cte1 and cte2, but in this example it only uses cte2 (see execution_table step 3).

Visual Quiz - 3 Questions

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

A [{id:1, name:'Alice'}, {id:3, name:'Adam'}]

B [{id:1, name:'Alice'}, {id:3, name:'Adam'}, {id:5, name:'Bob'}]

C undefined

D []

💡 Hint

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

At which step does cte2 get evaluated using cte1's data?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at the 'Action' column in execution_table for when cte2 is evaluated.

If the main query changed to select from cte1 instead of cte2, what would the final result include?

A All active users

B No users

C Only users with names starting with 'A'

D All users regardless of active status

💡 Hint

Refer to variable_tracker for cte1's content after step 1.

Concept Snapshot

WITH cte1 AS (query1),
cte2 AS (query2 referencing cte1)
SELECT * FROM cte2;

- CTEs are temporary named result sets.
- Later CTEs can use earlier CTEs.
- Main query can use any defined CTE.
- Evaluation happens in order of definition.

Full Transcript

This visual execution shows how SQL Common Table Expressions (CTEs) can reference each other. First, cte1 is defined and evaluated, filtering active users. Then cte2 is defined using cte1's result, further filtering users whose names start with 'A'. Finally, the main query selects from cte2, returning the filtered list. Variables cte1 and cte2 hold intermediate results. The execution table traces each step, showing how data flows from one CTE to the next and then to the final output. Key moments clarify why cte2 depends on cte1 and how the main query can use any CTE. The quiz tests understanding of these steps and data states.