Skip to content
SQLSimplified
cte

Multiple CTEs in One Query

Chain several CTEs to compute department stats then rank departments by average salary.

Overview

You can define several CTEs in one WITH block, separated by commas, and even reference earlier ones. This keeps complex queries readable by breaking them into named, composable steps. Here we first aggregate per department, then rank those aggregates.

The Query

Employees & Departments

Loading database engine...

Step-by-Step Breakdown

  1. dept_stats — aggregates headcount and average salary per department.
  2. ranked — joins those stats to department names and ranks by average pay. It references dept_stats defined just above it.
  3. The final SELECT * returns the composed result.

CTEs are not materialized (usually)

Most engines treat CTEs as inline views — they don't force intermediate storage, so they're primarily for readability, not performance.

Variations

Two CTEs for store: order totals then top customers:

Store (Customers/Orders/Products)

Loading database engine...

Common Mistakes

  • Referencing a CTE before it's defined. You can only use a CTE after its definition in the WITH list (or via later CTEs).
  • Forgetting the comma between CTEs. Separate definitions with commas; don't repeat WITH.
  • Name collisions. Each CTE needs a unique name within the block.

Related examples

Common Table Expressions (CTEs) With WITH
cte
Use a WITH clause to compute total spend per customer as a named, reusable result set, then filter and join against it.
JOIN Followed by GROUP BY
join
Join employees to departments, then aggregate to compute headcount and average salary per department.
Recursive CTE: Building a Number Series
cte
Use a recursive CTE to generate a sequence of integers from 1 to 10.

Search

Search lessons, functions, examples, and practice problems