Multiple CTEs in one query in SQL

Introduction

Using multiple CTEs helps break down complex queries into smaller, easy parts. It makes your query clearer and easier to understand.

When you want to organize your query into steps for better clarity.

When you need to reuse intermediate results multiple times in one query.

When you want to separate different calculations or filters before the final result.

When you want to improve readability of a long query by naming parts.

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

Syntax

SQL

WITH cte1 AS (
  -- first query here
),
cte2 AS (
  -- second query here
)
SELECT * FROM cte1 JOIN cte2 ON cte1.id = cte2.id;

Each CTE is separated by a comma.

You write all CTEs after WITH and before the main SELECT.

Examples

This example uses two CTEs to get employee names and their salaries, then joins them.

SQL

WITH first_cte AS (
  SELECT id, name FROM employees
),
second_cte AS (
  SELECT id, salary FROM salaries
)
SELECT first_cte.name, second_cte.salary
FROM first_cte
JOIN second_cte ON first_cte.id = second_cte.id;

This example calculates total sales per product and then joins with product names.

SQL

WITH sales_cte AS (
  SELECT product_id, SUM(amount) AS total_sales FROM sales GROUP BY product_id
),
products_cte AS (
  SELECT id, product_name FROM products
)
SELECT products_cte.product_name, sales_cte.total_sales
FROM sales_cte
JOIN products_cte ON sales_cte.product_id = products_cte.id;

Sample Program

This query uses two CTEs: one to find employees with salary over 50000, and another to count employees per department. Then it joins them to show high earners with their department size.

SQL

WITH
  high_salary AS (
    SELECT id, name, salary, department_id FROM employees WHERE salary > 50000
  ),
  department_count AS (
    SELECT department_id, COUNT(*) AS num_employees FROM employees GROUP BY department_id
  )
SELECT h.name, h.salary, d.num_employees
FROM high_salary h
JOIN department_count d ON h.department_id = d.department_id
ORDER BY h.salary DESC;

Important Notes

Make sure the columns you join on exist and match in both CTEs.

CTEs are temporary and only exist during the query execution.

You can have as many CTEs as you want, but keep queries readable.

Summary

Multiple CTEs let you split a query into named parts for clarity.

Separate each CTE with a comma after WITH.

Use CTEs to organize, reuse, and simplify complex queries.