Techie BabajiLearn to Code. Build Real Skills.
SQL Server · Combining Data

Common Table Expressions

A CTE gives a name to a query so you can build complex logic step by step, like variables for result sets.

Intermediate 5 min Lesson 15 of 31Examples run on SQL Server 2022 and Azure SQL Database.
Before you start:GROUP BYSubqueries
0/31 in SQL Server
On this page · 5 min · Intermediate

On this page

  1. In simple words
  2. Learning objectives
  3. Explanation
  4. Syntax
  5. Code example
  6. Step by step
  7. Real-world use
  8. Common mistakes
  9. Best practices
  10. Remember this
  11. Practice exercise
  12. Mini quiz
  13. Related topics
  14. My notes
Reading time
5 min
Difficulty
Intermediate
Updated
Oct 27, 2026

Related tutorials

Interview questions

In simple words#

A CTE is rough work on the side of your exam paper: name a temporary result, then use it in the final answer. It makes long queries readable.

CTE
Common Table Expression: WITH Name AS (…).
Readability
Break a big query into named steps.
Recursive CTE
Walk hierarchies like manager → employee.

Learning objectives#

  • Write a CTE with WITH … AS
  • Chain several CTEs
  • Write a recursive CTE
  • Know when a CTE helps readability

Explanation#

WITH Name AS (query) defines a temporary named result that exists only for the next statement.

Several CTEs can be chained with commas, each building on the previous one.

A recursive CTE has an anchor query and a recursive part joined with UNION ALL. It is ideal for hierarchies such as categories or org charts.

The statement before WITH must end with a semicolon.

Syntax#

SQL Illustrative
WITH Step1 AS (SELECT ...),
     Step2 AS (SELECT ... FROM Step1)
SELECT * FROM Step2;

Code example: Order totals, then large orders#

SQLOrder totals, then large orders Illustrative
WITH OrderTotals AS (
  SELECT OrderId, SUM(Qty * UnitPrice) AS Total
  FROM OrderItems
  GROUP BY OrderId
)
SELECT OrderId, Total
FROM OrderTotals
WHERE Total > 5000
ORDER BY Total DESC;


WITH Numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM Numbers WHERE n < 5
)
SELECT n FROM Numbers;
Expected output
OrderId  Total
101      56400.00
102      12000.00
104      9000.00

n
1
2
3
4
5

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

  1. OrderTotals calculates the total of each order once.
  2. The main query filters and sorts those totals like a normal table.
  3. In the recursive CTE, the anchor returns 1; the recursive part adds 1 until n reaches 5.

Real-world use case#

Category trees (Electronics → Laptops → Gaming laptops) and employee reporting lines are queried with recursive CTEs.

Common mistakes#

Missing semicolon before WITH

If the previous statement does not end with ;, SQL Server reports a syntax error near WITH.

Infinite recursion

Without a stopping condition, recursion stops at the default limit of 100 levels with an error. Add a WHERE condition or MAXRECURSION.

Best practices#

  • Use CTEs to name intermediate steps
  • End the previous statement with a semicolon
  • Always include a termination condition in recursive CTEs
  • Remember a CTE is not cached — referencing it twice may run it twice

Remember this#

WITH Name AS (…) SELECT … FROM Name.

CTE = named rough work for readability.

Recursive CTE walks hierarchies.

Practice exercise#

Your task: Use a CTE to find each customer’s number of orders, then return only customers with more than one order.

Show solution
SQL Illustrative
WITH C AS (SELECT CustomerId, COUNT(*) AS Orders FROM Orders GROUP BY CustomerId)
SELECT * FROM C WHERE Orders > 1;  -- CustomerId 1 (Asha), 2 orders

Mini quiz#

Common Table Expressions quizMultiple choice Question 1 of 1

Which operator joins the anchor and recursive parts of a recursive CTE?

Score so far: 0/0

Want more? Practise all SQL Server MCQs · SQL Server interview questions

My notes#

Saved in this browser.

Progress is saved in this browser. Sign in to sync.