CTE referencing another CTE in SQL

Introduction

CTEs let you break complex queries into smaller parts. Referencing one CTE inside another helps organize and reuse query parts clearly.

When you want to split a big query into smaller, readable steps.

When you need to use the result of one CTE inside another CTE.

When you want to avoid repeating the same subquery multiple times.

When you want to build layered calculations step-by-step.

When you want to improve query clarity and maintainability.

Syntax

SQL

WITH cte1 AS (
  -- first query
),
cte2 AS (
  SELECT * FROM cte1 WHERE condition
)
SELECT * FROM cte2;

Each CTE is separated by a comma.

Later CTEs can use earlier CTEs as tables.

Examples

The second CTE uses the first CTE to filter employees whose names start with 'A'.

SQL

WITH first_cte AS (
  SELECT id, name FROM employees
),
second_cte AS (
  SELECT id FROM first_cte WHERE name LIKE 'A%'
)
SELECT * FROM second_cte;

The second CTE calculates squares of numbers from the first CTE.

SQL

WITH numbers AS (
  SELECT 1 AS num UNION ALL SELECT 2 UNION ALL SELECT 3
),
squares AS (
  SELECT num, num * num AS square FROM numbers
)
SELECT * FROM squares;

Sample Program

First CTE 'sales' selects product sales. Second CTE 'total_sales' sums quantities per product. Final query shows products with total sales over 10.

SQL

WITH sales AS (
  SELECT product_id, quantity FROM orders
),
total_sales AS (
  SELECT product_id, SUM(quantity) AS total_quantity FROM sales GROUP BY product_id
)
SELECT * FROM total_sales WHERE total_quantity > 10;

Important Notes

CTEs improve readability but may not always improve performance.

You can reference multiple CTEs in one query by chaining them.

Some databases limit how many CTEs you can chain.

Summary

CTEs let you write queries in clear, small steps.

You can reference one CTE inside another to build layered queries.

This helps avoid repeating code and makes queries easier to understand.