All 7 Common Table Expressions (CTEs) questions
More in Common Table Expressions (CTEs)
What is a common table expression, and why would you use one instead of a subquery? What is a recursive common table expression, and what kind of problems is it commonly used to solve? How do you prevent a recursive common table expression from running forever if the underlying data accidentally contains a circular reference? What are the main limitations of common table expressions that developers should be aware of? How would you use a CTE combined with a window function to identify and remove duplicate rows from a table? Can a common table expression be used inside an UPDATE, DELETE, or MERGE statement, and how does that work?
Advertisement
SQL Server › Common Table Expressions (CTEs)

How do you define multiple common table expressions within a single query?

Beginner

Answer

You separate multiple CTE definitions with a comma after the WITH keyword, and each subsequent CTE can even reference the ones defined before it, letting you build up a series of clearly named, logical steps that eventually feed into your final SELECT statement.
WITH RecentOrders AS (
  SELECT * FROM Orders WHERE OrderDate >= '2026-01-01'
),
HighValue AS (
  SELECT * FROM RecentOrders WHERE Amount > 500
)
SELECT * FROM HighValue;
Real-world example A reporting query breaks a complex filtering process into two clearly named steps, first isolating recent orders and then further filtering for high value ones, making the overall logic much easier to follow.

Common follow-ups: Can later CTEs reference earlier ones in the same WITH clause?;Is there a limit to how many CTEs can be defined in a single query?

Subqueries;Joins
All 7 Common Table Expressions (CTEs) questions