All 7 Common Table Expressions (CTEs) questions
More in Common Table Expressions (CTEs)
How do you define multiple common table expressions within a single query? 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
Back to results for “SQL Queries”
SQL Server › Common Table Expressions (CTEs)

What is a common table expression, and why would you use one instead of a subquery?

Beginner

Answer

A common table expression, defined using the WITH keyword, creates a temporary named result set that you can reference within a single query, making complex queries easier to read and organize compared to nesting several subqueries inside each other, especially when the same intermediate result needs to be referenced more than once.
WITH HighValueOrders AS (
  SELECT CustomerId, Amount
  FROM Orders
  WHERE Amount > 500
)
SELECT CustomerId, COUNT(*) AS OrderCount
FROM HighValueOrders
GROUP BY CustomerId;
Real-world example A sales report uses a CTE to first isolate high value orders, then groups and counts them by customer, making the overall query much easier to read than nesting the same filter logic inside a subquery.

Common follow-ups: Does a CTE actually improve query performance compared to a subquery?;Can a query have more than one CTE defined at the same time?

Subqueries;Query Optimization & Plans
All 7 Common Table Expressions (CTEs) questions