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.

Practice

1. What is the main purpose of referencing one CTE inside another CTE in SQL?

easy

A. To create permanent tables in the database

B. To speed up the database server automatically

C. To avoid using SELECT statements

D. To break down complex queries into smaller, easier steps

Solution

1. Step 1: Understand what CTEs do

   CTEs (Common Table Expressions) let you write parts of a query separately for clarity.

2. Step 2: See why referencing one CTE inside another helps

   Referencing one CTE inside another breaks complex logic into smaller, readable steps without repeating code.

3. Final Answer:

   To break down complex queries into smaller, easier steps -> Option D

4. Quick Check:

   CTEs help simplify queries = C [OK]

Hint: Think of CTEs as building blocks for queries [OK]

Common Mistakes:

• Confusing CTEs with permanent tables
• Believing CTEs speed up queries automatically
• Thinking CTEs replace SELECT statements

2. Which of the following is the correct syntax to reference one CTE inside another in SQL?

easy

A. WITH cte1 AS (SELECT * FROM table1), cte2 AS (SELECT * FROM cte1 WHERE id > 10) SELECT * FROM cte2;

B. WITH cte1 AS (SELECT * FROM table1) SELECT * FROM cte1; WITH cte2 AS (SELECT * FROM cte1 WHERE id > 10);

C. CREATE CTE cte1 AS SELECT * FROM table1; CREATE CTE cte2 AS SELECT * FROM cte1 WHERE id > 10;

D. SELECT * FROM cte1; WITH cte2 AS (SELECT * FROM cte1 WHERE id > 10);

Solution

1. Step 1: Review correct CTE syntax

   Multiple CTEs are defined together after WITH, separated by commas, and can reference each other.

2. Step 2: Check each option

   WITH cte1 AS (SELECT * FROM table1), cte2 AS (SELECT * FROM cte1 WHERE id > 10) SELECT * FROM cte2; correctly defines cte1, then cte2 referencing cte1, then selects from cte2. Others misuse WITH or CREATE keywords.

3. Final Answer:

   WITH cte1 AS (SELECT * FROM table1), cte2 AS (SELECT * FROM cte1 WHERE id > 10) SELECT * FROM cte2; -> Option A

4. Quick Check:

   Multiple CTEs separated by commas = A [OK]

Hint: Use one WITH clause for all CTEs, separated by commas [OK]

Common Mistakes:

• Using multiple WITH clauses separately
• Trying to CREATE CTEs like tables
• Placing SELECT before WITH

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

WITH first_cte AS (
  SELECT 1 AS num UNION ALL SELECT 2 UNION ALL SELECT 3
), second_cte AS (
  SELECT num * 10 AS val FROM first_cte WHERE num > 1
)
SELECT * FROM second_cte ORDER BY val;

medium

A. [20, 30]

B. Syntax error

C. [1, 2, 3]

D. [10, 20, 30]

Solution

1. Step 1: Analyze first_cte output

   first_cte returns rows with num = 1, 2, 3.

2. Step 2: Analyze second_cte filtering and calculation

   second_cte selects num * 10 as val where num > 1, so num=2 and 3, val=20 and 30.

3. Final Answer:

   [20, 30] -> Option A

4. Quick Check:

   Filter num > 1, multiply by 10 = [20, 30] [OK]

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

Common Mistakes:

• Including num=1 in second_cte output
• Confusing num and val columns
• Assuming syntax error due to multiple CTEs

4. Identify the error in this SQL query using CTEs:

WITH cte1 AS (
  SELECT id, name FROM users
),
cte2 AS (
  SELECT id, name FROM cte3 WHERE id > 5
)
SELECT * FROM cte2;

medium

A. SELECT statement missing after CTEs

B. Missing comma between CTE definitions

C. cte2 references cte3 which is not defined

D. CTEs cannot reference other CTEs

Solution

1. Step 1: Check CTE definitions

   cte1 is defined, but cte2 references cte3 which is not defined anywhere.

2. Step 2: Confirm syntax correctness

   Comma between cte1 and cte2 is present, and SELECT after CTEs is correct, so no syntax error there.

3. Final Answer:

   cte2 references cte3 which is not defined -> Option C

4. Quick Check:

   Undefined CTE reference = A [OK]

Hint: Check all referenced CTE names are defined above [OK]

Common Mistakes:

• Assuming missing comma error
• Thinking CTEs can't reference others
• Overlooking undefined CTE names

5. You have two CTEs: sales_cte with columns (product_id, total_sales) and products_cte with columns (product_id, product_name). How do you write a query using CTE referencing another CTE to get product names with sales over 1000?

hard

A. WITH sales_cte AS (SELECT product_id, SUM(amount) AS total_sales FROM sales GROUP BY product_id) SELECT product_name FROM products WHERE product_id IN (SELECT product_id FROM sales_cte WHERE total_sales > 1000);

B. WITH sales_cte AS (SELECT product_id, SUM(amount) AS total_sales FROM sales GROUP BY product_id), products_cte AS (SELECT product_id, product_name FROM products), final_cte AS (SELECT p.product_name FROM products_cte p JOIN sales_cte s ON p.product_id = s.product_id WHERE s.total_sales > 1000) SELECT * FROM final_cte;

C. WITH products_cte AS (SELECT product_id, product_name FROM products), sales_cte AS (SELECT product_id, SUM(amount) AS total_sales FROM sales GROUP BY product_id) SELECT product_name FROM products_cte WHERE total_sales > 1000;

D. WITH final_cte AS (SELECT product_name FROM products JOIN sales ON products.product_id = sales.product_id WHERE sales.amount > 1000) SELECT * FROM final_cte;

Solution

1. Step 1: Define sales_cte and products_cte correctly

   sales_cte sums sales per product_id; products_cte selects product info.

2. Step 2: Create final_cte referencing both CTEs

   Join products_cte and sales_cte on product_id, filter total_sales > 1000, select product_name.

3. Step 3: Select from final_cte

   Final SELECT returns product names with sales over 1000.

4. Final Answer:

   WITH sales_cte AS (SELECT product_id, SUM(amount) AS total_sales FROM sales GROUP BY product_id), products_cte AS (SELECT product_id, product_name FROM products), final_cte AS (SELECT p.product_name FROM products_cte p JOIN sales_cte s ON p.product_id = s.product_id WHERE s.total_sales > 1000) SELECT * FROM final_cte; -> Option B

5. Quick Check:

   Layered CTEs with join and filter = B [OK]

Hint: Build CTEs stepwise, join in last CTE for filtering [OK]

Common Mistakes:

• Referencing columns not in CTEs
• Filtering before joining CTEs
• Not joining CTEs properly