Multiple CTEs
- SQL course
- Advanced
- Lesson 3/81
- 4 min
Use several CTEs in one query.
Multiple CTEs
You can define multiple CTEs separated by commas. Each can reference the others.
WITH active AS (
SELECT * FROM customers WHERE status = 'active'
),
big AS (
SELECT * FROM orders WHERE amount > 100
)
SELECT a.name, b.amount
FROM active a
JOIN big b ON a.id = b.customer_id;This query defines two CTEs and joins them.
WITH defines a temporary result set (CTE) named active; the query after it uses it like a table. a.name, b.amount is selected from active a. big b is joined on a.id = b.customer_id, keeping only matching rows.
Multiple CTEs — details
In multiple CTEs, each can use the previous one. It enables chained queries.
For example, you can compute active customers first, then their large orders, and finally totals per city in separate CTEs, moving step by step.
Practice Multiple CTEs
Define two CTEs named active and big and join them.
Starter code
-- multiple CTE
WITHSample solution
WITH active AS (
SELECT * FROM customers WHERE status = 'active'
),
big AS (
SELECT * FROM orders WHERE amount > 100
)
SELECT a.name, b.amount
FROM active a
JOIN big b ON a.id = b.customer_id;Expected output
name amount Ayşe 250
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Multiple CTEs: match the terms
| Term | Meaning |
|---|---|
multiple CTE |
comma |
chained |
sequential |
reference |
use |
readable |
clean |
2 Add second CTE
WITH active AS (
SELECT * FROM customers WHERE status = 'active'
)____
big AS (
SELECT * FROM orders WHERE amount > 100
)
SELECT * FROM big;WITH active AS (
SELECT * FROM customers WHERE status = 'active'
),
big AS (
SELECT * FROM orders WHERE amount > 100
)
SELECT * FROM big;Explanation: Several CTEs are written under one WITH, separated by commas; WITH is not repeated.
3 How are multiple CTEs separated?
- With a semicolon
- With AND
- With a comma Correct answer
- With UNION
Explanation: Multiple CTEs are separated by commas.