Recursive CTEs — Org Charts and Hierarchies

Q: What does a NULL in the Reports_To_ID column signify?

A NULL value represents the absence of a manager reference, identifying the top of the organization like a Director.

Q: How does an org chart recursive query terminate without a WHERE clause limit?

The recursion terminates automatically when the INNER JOIN finds no subordinates reporting to the previous level, producing an empty result set.

Q: Can a CTE be referenced outside of the query immediately following it?

No, the scope of any CTE is strictly limited to the single SELECT, INSERT, UPDATE, or DELETE query immediately following its WITH clause.

The one SQL feature most analysts never use — and the one that solves 'show me all descendants' in 10 lines. FIND_VIDEO: search 'recursive CTE SQL hierarchical query' — recommended channel: Modern SQL / Itzik Ben-Gan. Aim for 11 min or under.

6 minutesVideo LessonPDF notes

Watch Recursive CTEs — Org Charts and Hierarchies on YouTube

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Loading progress...

Key moments

CTE Fundamentals — Introduces standard Common Table Expressions, the WITH clause syntax, query scope, and the concept of recursion.
Number Iterator CTE — Demonstrates generating numbers 1 to 25 using an anchor query and an incrementing recursive query with a termination condition.
Org Chart Schema — Explains the Employee_Structure table schema, manager-report relationships, and NULL values at the top of the hierarchy.
Hierarchical Reporting Query — Builds and executes the recursive query to traverse downward reporting lines starting from a specific manager ID.

PDF notes

Bottom line: Master recursive Common Table Expressions (CTEs) in SQL to solve iterative and hierarchical data problems. You will be able to construct anchor and recursive query members linked by UNION ALL to generate iterative sequences and traverse multi-level organizational reporting lines.

This lesson on Recursive CTEs — Org Charts and Hierarchies is hands-on and example-driven. Master recursive Common Table Expressions (CTEs) in SQL to solve iterative and hierarchical data problems. You will be able to construct anchor and recursive query members linked by UNION ALL to generate iterative sequences and traverse multi-level organizational reporting lines.

What You'll Be Able To Do

Construct an anchor query to initialize the base result set of a recursive CTE.
Write a recursive member that references the CTE expression to iterate data levels.
Combine anchor and recursive query blocks using UNION ALL to produce a unified result set.
Traverse parent-child org chart structures by joining employee records to previous recursion records.
Define termination conditions to halt recursion upon meeting boundary limits or encountering empty sets.

Detailed Concept Walkthrough

1. Recursive CTE Architecture

A recursive CTE is a common table expression that repeatedly references itself to process hierarchical or sequential data. It establishes an initial base result set and iteratively reapplies a recursive query until reaching a termination condition.

Anchor Member: Defines the starting point or initialization of the dataset using a standard SELECT statement without self-referencing. This query executes exactly once at the beginning of evaluation.
Set Union: Combines the initial anchor result set with the subsequent result sets generated by the recursive step using the UNION or UNION ALL operator.
Recursive Member: References the CTE by name, pulling from the results of the previous execution step to compute the next layer of data until it returns an empty set.

WITH Recursive_Template AS (
    -- Anchor / Base Query (runs once)
    SELECT ID, Reports_To_ID FROM Employee_Structure WHERE Reports_To_ID IS NULL
    UNION ALL
    -- Recursive Member (references Recursive_Template)
    SELECT e.ID, e.Reports_To_ID
    FROM Employee_Structure e
    INNER JOIN Recursive_Template r ON e.Reports_To_ID = r.ID
)
SELECT * FROM Recursive_Template;

Key Takeaway: A recursive CTE requires an anchor query for initialization and a recursive query referencing the CTE name to iterate through data levels.

2. Sequence Generation via Number Iteration

Sequential series generation uses a recursive CTE to increment a scalar value row by row without procedural loops. The recursion runs repeatedly until a predicate boundary in the recursive member halts execution.

Initialization: The anchor query defines the initial seed value (e.g., SELECT 1 AS Number_Iterator) to establish the first record.
Iterative Execution: The recursive query selects from the CTE, adding a constant step to the previous row value (e.g., Number_Iterator + 1).
Termination Predicate: A WHERE clause in the recursive member stops recursion once the iterator reaches the specified boundary condition.

WITH Simple_CTE AS (
    -- Base query: initialize seed at 1
    SELECT 1 AS Number_Iterator
    UNION ALL
    -- Recursive execution: add 1 until reaching 25
    SELECT Number_Iterator + 1
    FROM Simple_CTE
    WHERE Number_Iterator < 25
)
SELECT Number_Iterator FROM Simple_CTE;

Key Takeaway: Iterative sequence CTEs must include an explicit WHERE filter in the recursive member to prevent infinite recursion.

3. Hierarchical Org Chart Traversal

Organizational charts model parent-child relationships where employees store a reference to their manager's ID. Recursive CTEs traverse this tree from top to bottom by matching managers to direct report records across generations.

Root Identification: The anchor selects the target manager or root record (such as Employee_ID = 2 or Reports_To_ID IS NULL for the top director).
Relationship Joining: The recursive query performs an INNER JOIN between the underlying employee table and the CTE, matching the employee's Reports_To_ID to the prior level's Employee_ID.
Natural Termination: Recursion halts automatically when the join yields zero rows, indicating that the lowest level of individual contributors with no direct reports has been reached.

WITH Employee_Hierarchy AS (
    -- Anchor query: target starting manager
    SELECT Employee_ID, Reports_To_ID
    FROM Employee_Structure
    WHERE Reports_To_ID = 2
    UNION ALL
    -- Recursive member: match direct reports to previous level
    SELECT e.Employee_ID, e.Reports_To_ID
    FROM Employee_Structure e
    INNER JOIN Employee_Hierarchy h ON e.Reports_To_ID = h.Employee_ID
)
SELECT Employee_ID, Reports_To_ID FROM Employee_Hierarchy;

Key Takeaway: Hierarchy traversal terminates naturally when the recursive join yields an empty set at the bottom of the reporting tree.

Topics Covered in Recursive CTEs — Org Charts and Hierarchies

CTE Fundamentals (0:00 - 0:35) — Introduces standard Common Table Expressions, the WITH clause syntax, query scope, and the concept of recursion.
Number Iterator CTE (0:35 - 1:30) — Demonstrates generating numbers 1 to 25 using an anchor query and an incrementing recursive query with a termination condition.
Org Chart Schema (1:30 - 2:15) — Explains the Employee_Structure table schema, manager-report relationships, and NULL values at the top of the hierarchy.
Hierarchical Reporting Query (2:15 - 3:20) — Builds and executes the recursive query to traverse downward reporting lines starting from a specific manager ID.

SQL Cheat Sheet

WITH cte_name AS (...) — Defines a temporary named query block within the statement scope
```
WITH Simple_CTE AS (SELECT 1 AS N) SELECT * FROM Simple_CTE;
```

Anchor Member — Defines base seed record set that initializes recursion

SELECT Employee_ID, Reports_To_ID FROM Employee_Structure WHERE Reports_To_ID = 2

UNION ALL — Combines base anchor rows with iterative recursive execution rows
```
SELECT 1 UNION ALL SELECT 2;
```

Recursive Member — Self-referencing query block that extracts the next hierarchy level

SELECT e.Employee_ID, e.Reports_To_ID FROM Employee_Structure e JOIN Hierarchy h ON e.Reports_To_ID = h.Employee_ID

WHERE Termination_Condition — Sets upper limit to halt sequential recursion loops
```
WHERE Number_Iterator < 25
```

Comparison Table

Feature	Standard CTE	Recursive CTE
Self-Reference	Cannot reference its own definition	References itself in the recursive member
Query Structure	Single SELECT statement block	Anchor SELECT, UNION ALL, Recursive SELECT
Primary Purpose	Modularize queries and simplify joins	Traverse trees, hierarchies, and series
Termination	Runs once over base data	Stops via condition or empty set

Common Pitfalls

Mistake: Omitting the termination filter in a sequence generator CTE. Avoid: Always include a WHERE boundary condition in the recursive member.
Mistake: Inverting the join keys when traversing hierarchical reporting lines. Avoid: Join the table parent key to the CTE child key.
Mistake: Using UNION instead of UNION ALL in high-volume recursive iterations. Avoid: Use UNION ALL to prevent costly deduplication during recursion.

FAQs

What does a NULL in the Reports_To_ID column signify? A NULL value represents the absence of a manager reference, identifying the top of the organization like a Director.
How does an org chart recursive query terminate without a WHERE clause limit? The recursion terminates automatically when the INNER JOIN finds no subordinates reporting to the previous level, producing an empty result set.
Can a CTE be referenced outside of the query immediately following it? No, the scope of any CTE is strictly limited to the single SELECT, INSERT, UPDATE, or DELETE query immediately following its WITH clause.

Common pitfalls

Mistake: Omitting the termination filter in a sequence generator CTE. Avoid: Always include a WHERE boundary condition in the recursive member.
Mistake: Inverting the join keys when traversing hierarchical reporting lines. Avoid: Join the table parent key to the CTE child key.
Mistake: Using UNION instead of UNION ALL in high-volume recursive iterations. Avoid: Use UNION ALL to prevent costly deduplication during recursion.

Welcome to the SQL tutorial with me, James from Matador Software. And today we're looking at recursive CTEs within SQL Server. However, before we start that, it's important to note what standard or non-recursive CTE is if you haven't come across it. So, a CTE or common table expression is used to hold temporary result sets in order to break apart more complex queries. You may have heard them referred to as a WITH statement because they're created using the WITH clause, and the scope of a CTE is limited to the current query, and it improves readability and collaboration when debugging, and so on. Now a recursive CTE is simply just a common table expression that references itself. If we were to look at general definition, it can be thought of as a repeated procedure, or in the Latin roots of recursion, translates as the act of running back or again, and this will make sense later on. So now we're going to move into SQL Server Management Studio and look at two practical examples of using recursive CTEs where we'll finalize this with a hierarchical query for within a company, looking at the IDs of certain management and the hierarchy below that through the employees. So in this CTE example here, the simple CTE, what we actually just want to do is return the numbers 1 to 25 in a column that we are going to call Number_Iterator by using a recursive CTE. So we need to add the WITH clause and name it, so it'll be Simple_CTE, and then after 'AS' and the parenthesis, we break into a standard SELECT statement. So, establishing 1 as our number is the base query that we return. That's what we could refer to as the initialization. We then join this onto the recursive execution, which is simply through referencing itself, we are just adding 1 to that number from the Simple_CTE that we named. And then we terminate this when the number is at 25, and we SELECT N as Number_Iterator, and we can query it like that. So, pretty simple stuff, but important to note that you essentially have those two elements when you return the initialization and base query, and then when we use UNION to reference itself and actually carry out the recursion. So now we're looking at the Employee_Structure table that I've created. Very simple, we have Employee_IDs, and the number of the Employee_ID that they report to. Now you can assume that the Director in row 1 is the head of the company because they don't report to anyone. That's got a NULL value. Remember, NULL is simply just the absence of a value. Now you can see that Employee 2, which is a Senior Manager, reports to 1, the Director. Employee 3 is also a Senior Manager, reports to a Director. And this trend continues where we have Employee 4, who's a Manager, reports to one of the Senior Managers, ID 2, and Employee 5 is also a Manager, reports to the other Senior Manager, and so on. But let's say we want to use a classic example that recursive CTE is powerful for, to return the hierarchy of a specific reportees. So maybe we want to return the hierarchy of people who report to ID 2, that manager. Well, we can do that, and it's particularly helpful because if we don't have use to something like an Azure Active Directory, we might want to see this or stakeholders might for several reasons, just get the hierarchy. Now through this, you can see me typing out the query, and feel free to to copy this or pause it or slow it down, whatever you like. But really I will explain how this is working. So we want that Reports_To_ID value, where we've stated the number 2, to really be the highest level in the hierarchy. Now what's going to happen here, as we can see, we have the base query, then the recursive element. So what we want to happen here then is we want the recursive execution query to be joined to the initialization using UNION ALL. Recursion is happening because of the query referencing the CTE itself, based on the employee in the hierarchy CTE as an input. The JOIN then returns employees who have their managers as a previous record, and this is returned by the recursive query. And then it gets repeated until there's an empty result set. So we have the initial CTE with the fields stated, where they're coming from, the Reports_To_ID. We connect this to the recursive element with the same fields with just an alias, and we join it together. And as you can see here, the first line is the Reports_To_ID, where the Employee_ID is 4, then Employee 6 reports to 4, Employee 8 is reporting to 6, and you can see this in the rows above. So it's simply repeating itself with that nice hierarchy until the result set is empty. As usual, if you liked this content, please feel free to like, comment, subscribe, and share.

Frequently asked questions

What does a NULL in the Reports_To_ID column signify?