Common Table Expressions (CTE) and Recursive CTE
Quick Answer: A Common Table Expression (CTE) is a named temporary result set used inside a single SQL statement. A Recursive CTE refers to itself and repeatedly processes related rows. We use recursive CTEs for employee hierarchies, category trees and organizational structures.
1. Why Do We Need Common Table Expressions?
Imagine Priya manages a company with 1,000 employees. She needs to prepare reports about managers and their reporting teams.
Every employee has an employee ID and a manager ID. Some employees manage teams, while others report to those managers.
How can Priya retrieve this information without writing deeply nested queries?
The problem without CTE
We can use nested queries to retrieve employees reporting to a particular manager. As the query grows, understanding its logic becomes harder.
Example 1: Retrieve employees using a derived table
Assume the Employees table already contains these three records.
SELECT EmployeeName, Department
FROM
(
SELECT EmployeeName, Department
FROM Employees
WHERE Department = 'IT'
) AS ITEmployees
ORDER BY EmployeeName;
Expected output
EmployeeName Department ------------ ---------- Arjun IT Ravi IT
The query works, but the inner query adds another layer. Large reports can become difficult to read and maintain.
- Nested queries can make SQL harder to understand.
- Complex reports become difficult to organize.
- Repeated query logic can make maintenance harder.
- Understanding multi-level relationships needs more effort.
Now imagine Priya managing thousands of employees across many departments. She needs readable queries and clear reporting relationships.
We need a way to name intermediate query results. We also need a way to process hierarchical data.
The solution: Common Table Expressions
CTE = a named result set available within one SQL statement.
Recursive CTE = a CTE that refers to itself to process related rows.
Anchor member = the first query that returns the starting records.
Recursive member = the query that retrieves the next related records.
Priya can use a normal CTE to organize employee queries. She can use a recursive CTE to find every employee under a manager.
Animation 1: How a Recursive CTE builds an employee hierarchy
Watch Priya start with one manager and find employees across reporting levels.
The recursive query starts with Priya. It finds Ravi and Arjun, then Ravi's team members.
2. Implement CTE and Recursive CTE in SQL Server
Let's use one Employees table for both examples.
EmployeeId = a unique employee number.
ManagerId = the employee ID of the reporting manager.
WITH = the keyword used to define a CTE.
Create a normal CTE
Priya wants a clean list of employees working in the IT department.
Example 2: Normal CTE and recursive CTE
Assume Employees contains the five records shown in the output.
WITH ITEmployees AS
(
SELECT EmployeeId, EmployeeName, Department
FROM Employees
WHERE Department = 'IT'
)
SELECT EmployeeId, EmployeeName, Department
FROM ITEmployees;
WITH EmployeeHierarchy AS
(
SELECT EmployeeId, EmployeeName, ManagerId, 0 AS Level
FROM Employees
WHERE EmployeeId = 101
UNION ALL
SELECT E.EmployeeId, E.EmployeeName, E.ManagerId,
H.Level + 1
FROM Employees E
INNER JOIN EmployeeHierarchy H
ON E.ManagerId = H.EmployeeId
)
SELECT EmployeeId, EmployeeName, ManagerId, Level
FROM EmployeeHierarchy
ORDER BY Level, EmployeeId
OPTION (MAXRECURSION 100);
Notice the first CTE creates ITEmployees. The outer query reads its result like a table.
The second CTE starts with employee 101. Its recursive member finds employees whose ManagerId matches the current EmployeeId.
UNION ALL combines the starting records and recursive results. The level increases each time another reporting level appears.
Expected output: Recursive CTE
EmployeeId EmployeeName ManagerId Level ---------- ------------ --------- ----- 101 Priya NULL 0 102 Ravi 101 1 103 Arjun 101 1 104 Meena 102 2 105 Kiran 102 2
Important: MAXRECURSION 100 limits recursive levels to 100. SQL Server's default limit is 100. Use MAXRECURSION 0 only when you have a safe termination condition.
3. Quick Summary
Normal CTE vs Recursive CTE
| Normal CTE | Recursive CTE |
|---|---|
| Defines a named query result. | Defines a query that references itself. |
| Does not need recursion. | Uses anchor and recursive members. |
| Useful for readable queries and reports. | Useful for hierarchical data. |
| Does not need a recursion limit. | Uses MAXRECURSION to control recursion. |
The important terms to remember
- CTE: A named result set for one SQL statement.
- WITH: The keyword used to define a CTE.
- Anchor member: The starting query of a recursive CTE.
- Recursive member: The query that finds the next related records.
- MAXRECURSION: Controls the allowed recursive levels.
4. Common Mistakes and Best Practices
1. Forgetting the anchor member
A recursive CTE needs a starting query. Without it, the hierarchy has no starting records.
Better approach: Define the anchor member before the recursive member.
2. Creating an endless recursive query
Incorrect relationships or missing stopping conditions can cause excessive recursion.
Better approach: Check the hierarchy and set an appropriate MAXRECURSION limit.
3. Assuming a CTE is a permanent table
A CTE exists only within the statement that immediately follows it.
Better approach: Use a temporary table or permanent table when data must remain available across statements.
Design tip: Use normal CTEs to organize complex queries. Use recursive CTEs when data has a parent-child relationship. A CTE is not automatically faster than a subquery.
5. How CTE and Recursive CTE Work in Interviews
Question 1: What is a CTE in SQL Server?
What the interviewer expects: Explain its purpose and scope.
Strong answer: "A CTE is a named result set defined using WITH. I use it to make complex SQL queries easier to read. It is available only within its following statement."
Question 2: What is a Recursive CTE?
What the interviewer expects: Explain recursive processing.
Strong answer: "A Recursive CTE refers to itself. It starts with an anchor query and repeatedly processes related records. I use it to retrieve employee reporting hierarchies."
Question 3: What is MAXRECURSION?
What the interviewer expects: Explain recursion limits.
Strong answer: "MAXRECURSION controls how many recursive levels SQL Server can process. It helps prevent unexpectedly long recursive queries."
What interviewers expect at different levels
- Junior developer: Explain CTE syntax and basic examples.
- Senior developer: Write recursive queries and handle hierarchy data.
- Technical architect: Discuss recursion limits, cycle handling and query performance.
6. Hands-On Practice
Beginner: School hierarchy
Create a CTE that retrieves students belonging to the Science department. Display their student IDs and names.
Expected result: A filtered list of Science students.
Intermediate: Hospital reporting hierarchy
Create a recursive CTE for Lakshmi's hospital. Start with the hospital director and retrieve all reporting staff.
Hint: Use EmployeeId and ManagerId.
Advanced: Company organization chart
Extend Priya's employee hierarchy to include multiple reporting levels.
- Display every employee and their reporting level.
- Include employees from different departments.
- Identify and safely handle circular reporting relationships.
Challenge: Prevent cycles from causing repeated employee records.
7. Next Steps and Community
Previous article: Subqueries and Correlated Subqueries in SQL Server.
Next article: SQL Server Window Functions – ROW_NUMBER, RANK, DENSE_RANK and NTILE.
For more details, read the official Microsoft documentation:
Try Priya's employee hierarchy and add another reporting level. Share your recursive query and discuss different approaches with other learners.