Recursive Queries with CTE in SQL
💡 Concept Name
Recursive CTE (Common Table Expression) is a CTE that references itself to perform hierarchical or repeated operations in SQL queries.
📘 Quick Intro
Recursive CTEs let you process parent-child or hierarchical data structures such as organization charts, category trees, and ancestry relationships by repeatedly applying a query to its own result set.
🧠 Analogy / Short Story
Think of climbing a family tree. You start from one person (base case), then climb to their parent, then grandparent, and so on—each step referencing the one before it. A recursive CTE works the same way: each row builds upon the previous one until there’s nothing left to climb.
🔧 Technical Explanation
- Recursive CTE consists of two parts:
- Anchor member: returns the base row(s).
- Recursive member: references the CTE itself to return additional rows.
- They are defined using the `WITH` keyword followed by the CTE name.
- Must include a `UNION ALL` or `UNION` between the anchor and recursive parts.
- Must include a termination condition to avoid infinite loops.
- Ideal for traversing trees, graphs, or hierarchies in SQL.
🎯 Purpose & Use Case
- ✅ Navigate organizational hierarchies (e.g., employee-manager).
- ✅ Traverse category or folder structures.
- ✅ Perform pathfinding in graph data.
- ✅ Generate sequences or iterate over levels in data.
💻 Real Code Example
-- Recursive CTE to get all subordinates of a manager
WITH EmployeeHierarchy AS (
-- Anchor member: start with manager
SELECT EmployeeID, ManagerID, FullName, 0 AS Level
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member: find subordinates
SELECT e.EmployeeID, e.ManagerID, e.FullName, eh.Level + 1
FROM Employees e
INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;
❓ Interview Q&A
Q1: What is a recursive CTE?
A: A CTE that references itself to process hierarchical or recursive data.
Q2: What are the components of a recursive CTE?
A: Anchor member (base result) and recursive member (references the CTE itself).
Q3: Why do we use UNION ALL in recursive CTEs?
A: To combine base and recursive results while preserving duplicates if needed.
Q4: How is infinite recursion avoided?
A: By ensuring a termination condition in the recursive query and SQL Server’s max recursion limit (default 100).
Q5: What does LEVEL represent in a recursive CTE?
A: It shows the depth or distance from the root node.
Q6: Can we modify data using recursive CTEs?
A: No, they are meant for data selection and traversal only.
Q7: Where are recursive CTEs commonly used?
A: Organization charts, category hierarchies, and nested folder structures.
Q8: Can recursive CTEs be used to generate sequences?
A: Yes, you can use them to generate number series dynamically.
Q9: How to increase max recursion in SQL Server?
A: Use `OPTION (MAXRECURSION n)` at the end of the query.
Q10: What happens if recursion limit is exceeded?
A: SQL Server throws an error and terminates the query.
📝 MCQs
Q1. What does a recursive CTE reference?
- A table
- Another database
- Itself
- A stored procedure
Q2. What are the two parts of a recursive CTE?
- Head and tail
- Anchor and recursive member
- Start and end
- Parent and child
Q3. Which keyword is used to define a CTE?
- CTE
- RECURSIVE
- WITH
- DECLARE
Q4. What is the purpose of UNION ALL in a CTE?
- Join tables
- Create duplicates
- Sort data
- Combine anchor and recursive parts
Q5. How is infinite recursion prevented?
- By default
- Query timeout
- By index
- By a stop condition and max recursion limit
Q6. What kind of data is ideal for recursive CTE?
- Flat
- Hierarchical
- Temporal
- Unstructured
Q7. What SQL clause sets recursion depth?
- TOP
- MAX LEVEL
- OPTION (MAXRECURSION)
- LIMIT
Q8. What is the default max recursion in SQL Server?
- 10
- 50
- 100
- 1000
Q9. What field helps track depth in CTE?
- Path
- RowNum
- Level
- Rank
Q10. Can recursive CTEs be used to flatten tree data?
- No
- Only in MySQL
- Yes
- Only with stored procedures
💡 Bonus Insight
Recursive CTEs can simplify deeply nested joins or loops into clean, readable queries. Always test them with small datasets and use the `OPTION (MAXRECURSION n)` directive to control depth and avoid infinite recursion in production.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!