Multiple CTEs

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.

Exercise Multiple CTEs

Define two CTEs named active and big and join them.

Show example 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;

Test yourself

Multiple CTEs: match the terms

multiple CTE

comma
chained

sequential
reference

use
readable

clean

Add second CTE

WITH active AS (
  SELECT * FROM customers WHERE status = 'active'
)____
big AS (
  SELECT * FROM orders WHERE amount > 100
)
SELECT * FROM big;

Show answer

WITH active AS (
  SELECT * FROM customers WHERE status = 'active'
),
big AS (
  SELECT * FROM orders WHERE amount > 100
)
SELECT * FROM big;

How are multiple CTEs separated?
- With a semicolon
- With AND
- With a comma
- With UNION
Show answer

With a comma — Multiple CTEs are separated by commas.

Next lesson: CTE vs subquery

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies