SQL Notes

Hub Advantages and Disadvantages of SQL Features of SQL History of SQL SQL vs NoSQL What is SQL?SQL Comments SQL Data Types SQL Keywords SQL Operators SQL Syntax CHECK Constraint in SQL Composite Keys in SQL DEFAULT Constraint in SQL FOREIGN KEY Constraint in SQL NOT NULL Constraint in SQL PRIMARY KEY Constraint in SQL SQL Constraints UNIQUE Key Constraint in SQL DELETE Data INSERT Data SELECT Data SQL Aliases UPDATE Data BETWEEN Operator DISTINCT Keyword GROUP BY Clause HAVING Clause IN Operator LIKE Operator LIMIT Clause ORDER BY Clause TOP Clause WHERE Clause Wildcard Characters EXCEPT Operator in SQL INTERSECT Operator in SQL MINUS Operator in SQL UNION Operator in SQL UNION ALL Operator in SQL CROSS JOIN in SQL FULL OUTER JOIN INNER JOIN JOIN vs UNION LEFT JOIN RIGHT JOIN SELF JOIN SQL Joins Overview ANY and ALL Operators CASE Expression in SQL Correlated Subquery EXISTS Operator Nested Subqueries in SQL Scalar Subqueries Subquery Introduction Aggregate Functions AVG Function Conversion Functions COUNT Function Date Functions MIN and MAX Functions Numeric Functions String Functions SUM Function ALTER TABLE Create Table DROP TABLE Rename Table Temporary Table TRUNCATE TABLE Backup Database Create Database Drop Database Restore Database CREATE VIEW in SQL DROP VIEW in SQL Materialized Views in SQL Update and Modify Views in SQL Create Stored Procedure in SQL Execute Stored Procedures in SQL Parameters in Stored Procedures Stored Procedure vs Function in SQL Stored Procedure Introduction AFTER Trigger BEFORE Trigger INSTEAD OF Trigger Trigger Examples Trigger Introduction ACID Properties in SQL COMMIT in SQL Isolation Levels in SQL ROLLBACK in SQL SAVEPOINT in SQL Introduction to SQL Transactions Boyce-Codd Normal Form (BCNF)Database Normalization Denormalization First Normal Form (1NF)Second Normal Form (2NF)Third Normal Form (3NF)Clustered Index in SQL Index Performance Non-Clustered Index Introduction to SQL Indexes Unique Index SQL Execution Plans EXPLAIN Plan Indexing Strategies Partitioning Query Optimization SQL Performance Tips Common Table Expressions (CTE)Dynamic SQL Functions in SQL Materialized Views MERGE Statement PIVOT and UNPIVOT RANK(), DENSE_RANK(), and ROW_NUMBER()Recursive CTE Stored Procedures Triggers in SQL Views in SQL Window Functions SQL Challenges SQL Exercises (Advanced)SQL Exercises (Beginner)SQL Exercises (Intermediate)SQL Projects SQL Interview Questions (Advanced)SQL Interview Questions (Beginner)SQL Interview Questions (Intermediate)SQL Output-Based Interview Questions SQL Scenario-Based Interview Questions Create Your First Database Install MySQL on Your Computer Install PostgreSQL Install SQLite SQL Online Compilers SQL Roadmap

Recursive CTE

Learn Recursive Common Table Expressions (Recursive CTEs) in SQL in detail, understand recursion, hierarchical queries, organizational charts, tree structures, and real-world examples.

In the previous lesson, you learned about:

text example

Common Table Expressions (CTEs)

A normal CTE creates a temporary result set and lets you clean up complex queries.

But here's the thing—some data is *hierarchical*. You've got:

text example

Hierarchies
Parent-Child Relationships
Tree Structures
Recursive Relationships

Real-world examples:

text example

Employee → Manager
Category → Subcategory
Folder → Subfolder
Course → Prerequisite

When you need to query these, SQL gives you:

text example

Recursive CTE

A Recursive CTE is a CTE that calls *itself* during execution, looping until it hits a stop condition.

Simple Definition

A Recursive CTE is a CTE that calls itself to build hierarchical or recursive query results.

Why Are Recursive CTEs Needed?

Picture a company org chart:

Example

CEO ├─ Manager A │ ├─ Employee 1 │ └─ Employee 2 │ └─ Manager B ├─ Employee 3 └─ Employee 4

You want to:

text example

Show Entire Organization Hierarchy

A basic SELECT won't cut it. Recursive CTEs solve this.

How Recursive CTE Works

A Recursive CTE has two parts:

text example

Anchor Query
      +
Recursive Query

Anchor Query

The starting point. It returns your initial rows.

Example:

text example

CEO

Recursive Query

Calls the CTE itself. Finds the next batch of rows.

Example:

text example

Managers
Employees

Execution Flow

text example

Anchor Query
      ↓
Result Generated
      ↓
Recursive Query Executes
      ↓
More Rows Found
      ↓
Repeat
      ↓
Stop Condition Met

Recursive CTE Syntax

sql example

WITH CTE_Name AS
(
    Anchor Query

    UNION ALL

    Recursive Query
)

SELECT *
FROM CTE_Name;

Understanding the Syntax

WITH

Kicks off the CTE.

Anchor Query

Your starting result set.

UNION ALL

Combines results from both parts.

Recursive Query

The part that calls the CTE itself.

Final SELECT

Shows all generated rows.

Simple Number Generation Example

Let's generate:

Output

1 To 5

Recursive CTE

sql example

WITH Numbers AS
(
    SELECT 1 AS Num

    UNION ALL

    SELECT Num + 1

    FROM Numbers

    WHERE Num < 5
)

SELECT *
FROM Numbers;

Output

Step-by-Step Execution

First pass (anchor):

sql example

SELECT 1

Output:

Output

Second pass (recursive):

sql example

1 + 1

Output:

Output

Third pass:

sql example

2 + 1

Output:

Output

Keeps going until:

sql example

Num < 5

becomes false.

Employee Hierarchy Example

Employees Table:

EmployeeID	EmployeeName	ManagerID
1	CEO	NULL
2	Rahul	1
3	Priya	1
4	Amit	2
5	Neha	2

Goal

Get the entire hierarchy.

Recursive CTE

sql example

WITH EmployeeHierarchy AS
(
    SELECT
        EmployeeID,
        EmployeeName,
        ManagerID

    FROM Employees

    WHERE ManagerID IS NULL

    UNION ALL

    SELECT
        E.EmployeeID,
        E.EmployeeName,
        E.ManagerID

    FROM Employees E

    JOIN EmployeeHierarchy EH

    ON E.ManagerID =
       EH.EmployeeID
)

SELECT *
FROM EmployeeHierarchy;

Output

CEO
Rahul
Priya
Amit
Neha

Boom. Hierarchy pulled automatically.

Organization Chart Example

Your structure:

Example

CEO ├─ HR Manager │ ├─ HR Staff │ └─ IT Manager ├─ Developer └─ Tester

A Recursive CTE pulls the entire reporting structure in one query.

Folder Structure Example

Your directory tree:

Example

Root ├─ Documents │ ├─ Reports │ └─ Images ├─ Photos

Recursive CTE traverses all nested folders.

Product Category Example

Your category tree:

Example

Electronics ├─ Mobile │ ├─ Android │ └─ iPhone │ └─ Laptop

Recursive query pulls the complete tree.

University Example

Course prerequisites:

text example

Programming Basics
      ↓
Data Structures
      ↓
Algorithms
      ↓
Advanced Algorithms

Recursive CTE retrieves prerequisite chains.

Banking Example

Branch structure:

text example

Head Office
      ↓
Regional Office
      ↓
Branch Office

Recursive queries display the hierarchy.

Understanding UNION ALL

Recursive CTEs almost always use:

sql example

UNION ALL

Why? Because:

Output

Faster
Preserves Duplicates
Required For Recursion

Recursive Depth

Each execution creates a new level.

Example:

Output

Level 1
CEO

Level 2
Managers

Level 3
Employees

Level 4
Interns

Adding Level Information

sql example

WITH EmployeeHierarchy AS
(
    SELECT
        EmployeeID,
        EmployeeName,
        ManagerID,
        1 AS Level

    FROM Employees

    WHERE ManagerID IS NULL

    UNION ALL

    SELECT
        E.EmployeeID,
        E.EmployeeName,
        E.ManagerID,
        EH.Level + 1

    FROM Employees E

    JOIN EmployeeHierarchy EH

    ON E.ManagerID =
       EH.EmployeeID
)

SELECT *
FROM EmployeeHierarchy;

Output Example

Employee	Level
CEO	1
Rahul	2
Priya	2
Amit	3
Neha	3

Stop Condition

Every recursive CTE *must* have:

Output

Termination Condition

Without it:

text example

Infinite Loop

Wrong Example

sql example

WITH Numbers AS
(
    SELECT 1

    UNION ALL

    SELECT Num + 1
    FROM Numbers
)

No stop condition. Result:

Output

Infinite Recursion

Your DB will hate you.

Correct Example

sql example

WHERE Num < 5

Stops it safely.

Recursive CTE vs Loop

Traditional programming:

Output

FOR LOOP
WHILE LOOP

SQL equivalent:

text example

Recursive CTE

Recursive CTE vs Self Join

Self Join:

text example

Limited Levels
Complex Queries

Recursive CTE:

text example

Unlimited Hierarchy Levels
Cleaner Queries

Advantages of Recursive CTEs

Handles Hierarchical Data

Your main use case.

Cleaner Queries

Way more readable than multiple joins.

Dynamic Depth

Works with unknown hierarchy levels.

Reusable Logic

Easy to maintain and tweak.

Powerful Reporting

Great for analytics.

Disadvantages of Recursive CTEs

Performance Cost

Large hierarchies can get expensive.

Infinite Loop Risk

Missing stop conditions = bad time.

Memory Usage

Deep recursion eats resources.

Complexity

Harder if you're just starting out.

Common Mistakes

Missing Stop Condition

The biggest mistake.

Using UNION Instead of UNION ALL

Slower and unnecessary.

Deep Recursion

Might exceed your database's limits.

Poor Indexing

Hierarchy queries can drag if not indexed.

Best Practices

Always Include Stop Condition

Prevents infinite loops.

Use UNION ALL

Better performance.

Index Parent Columns

Makes recursive joins faster.

Limit Recursion Depth

Protect your server.

Test Large Hierarchies

See how it performs before going live.

Common Interview Questions

What is a Recursive CTE?

A CTE that calls itself.

What are the two parts of a Recursive CTE?

text example

Anchor Query
Recursive Query

Why is UNION ALL used?

Combines recursive results efficiently.

What is the purpose of the stop condition?

Prevents infinite recursion.

Where are Recursive CTEs commonly used?

Hierarchies, org charts, category trees, and folder structures.

Summary

Recursive CTEs are powerful for processing hierarchical and recursive data. They have an anchor query that starts the process, and a recursive query that keeps going until you tell it to stop.

In this lesson, you learned:

What Recursive CTEs are
Recursive CTE syntax
Anchor Query basics
Recursive Query basics
Employee hierarchy examples
Folder structure examples
Product category trees
Stop conditions
Advantages and disadvantages
Best practices

Mastering Recursive CTEs matters because hierarchical data shows up everywhere in real applications—org structures, reporting systems, nested categories, and analytics platforms.

Next Step

Continue to the next lesson:

Window Functions →

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for Recursive CTE.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.

Search Terms

sql-complete-guide, sql complete guide, sql, complete, guide, advanced, recursive, cte

Continue learning this concept

Advanced SQLTriggers in SQLLearn SQL Triggers in detail, understand automatic database actions, BEFORE and AFTER triggers, event-driven execution, auditing, validation, and real-world applications.Advanced SQLCommon Table Expressions (CTE)Learn Common Table Expressions (CTE) in SQL in detail, understand CTE syntax, recursive CTEs, advantages, performance considerations, and real-world examples.Advanced SQLDynamic SQLLearn Dynamic SQL in detail, understand runtime query generation, EXEC, sp_executesql, dynamic filtering, dynamic tables, and real-world enterprise use cases.Advanced SQLFunctions in SQLLearn SQL Functions in detail, understand built-in and user-defined functions, scalar and table-valued functions, parameters, return values, and real-world applications.Advanced SQLMaterialized ViewsLearn SQL Materialized Views in detail, understand how they store data physically, improve query performance, differ from normal views, and are used in large-scale reporting and analytics systems.Advanced SQLMERGE StatementLearn SQL MERGE Statement in detail, understand UPSERT operations, INSERT, UPDATE, DELETE in a single statement, synchronization, data warehousing, and real-world examples.

PreviousRANK(), DENSE_RANK(), and ROW_NUMBER()NextStored Procedures

SQL Notes

Recursive CTE

Learn Recursive Common Table Expressions (Recursive CTEs) in SQL in detail, understand recursion, hierarchical queries, organizational charts, tree structures, and real-world examples.

In the previous lesson, you learned about:

text example

Common Table Expressions (CTEs)

A normal CTE creates a temporary result set and lets you clean up complex queries.

But here's the thing—some data is *hierarchical*. You've got:

text example

Hierarchies
Parent-Child Relationships
Tree Structures
Recursive Relationships

Real-world examples:

text example

Employee → Manager
Category → Subcategory
Folder → Subfolder
Course → Prerequisite

When you need to query these, SQL gives you:

text example

Recursive CTE

A Recursive CTE is a CTE that calls *itself* during execution, looping until it hits a stop condition.

Simple Definition

A Recursive CTE is a CTE that calls itself to build hierarchical or recursive query results.

Why Are Recursive CTEs Needed?

Picture a company org chart:

Example

CEO ├─ Manager A │ ├─ Employee 1 │ └─ Employee 2 │ └─ Manager B ├─ Employee 3 └─ Employee 4

You want to:

text example

Show Entire Organization Hierarchy

A basic SELECT won't cut it. Recursive CTEs solve this.

How Recursive CTE Works

A Recursive CTE has two parts:

text example

Anchor Query
      +
Recursive Query

Anchor Query

The starting point. It returns your initial rows.

Example:

text example

CEO

Recursive Query

Calls the CTE itself. Finds the next batch of rows.

Example:

text example

Managers
Employees

Execution Flow

text example

Anchor Query
      ↓
Result Generated
      ↓
Recursive Query Executes
      ↓
More Rows Found
      ↓
Repeat
      ↓
Stop Condition Met

Recursive CTE Syntax

sql example

WITH CTE_Name AS
(
    Anchor Query

    UNION ALL

    Recursive Query
)

SELECT *
FROM CTE_Name;

Understanding the Syntax

WITH

Kicks off the CTE.

Anchor Query

Your starting result set.

UNION ALL

Combines results from both parts.

Recursive Query

The part that calls the CTE itself.

Final SELECT

Shows all generated rows.

Simple Number Generation Example

Let's generate:

Output

1 To 5

Recursive CTE

sql example

WITH Numbers AS
(
    SELECT 1 AS Num

    UNION ALL

    SELECT Num + 1

    FROM Numbers

    WHERE Num < 5
)

SELECT *
FROM Numbers;

Output

Step-by-Step Execution

First pass (anchor):

sql example

SELECT 1

Output:

Output

Second pass (recursive):

sql example

1 + 1

Output:

Output

Third pass:

sql example

2 + 1

Output:

Output

Keeps going until:

sql example

Num < 5

becomes false.

Employee Hierarchy Example

Employees Table:

EmployeeID	EmployeeName	ManagerID
1	CEO	NULL
2	Rahul	1
3	Priya	1
4	Amit	2
5	Neha	2

Goal

Get the entire hierarchy.

Recursive CTE

sql example

WITH EmployeeHierarchy AS
(
    SELECT
        EmployeeID,
        EmployeeName,
        ManagerID

    FROM Employees

    WHERE ManagerID IS NULL

    UNION ALL

    SELECT
        E.EmployeeID,
        E.EmployeeName,
        E.ManagerID

    FROM Employees E

    JOIN EmployeeHierarchy EH

    ON E.ManagerID =
       EH.EmployeeID
)

SELECT *
FROM EmployeeHierarchy;

Output

CEO
Rahul
Priya
Amit
Neha

Boom. Hierarchy pulled automatically.

Organization Chart Example

Your structure:

Example

CEO ├─ HR Manager │ ├─ HR Staff │ └─ IT Manager ├─ Developer └─ Tester

A Recursive CTE pulls the entire reporting structure in one query.

Folder Structure Example

Your directory tree:

Example

Root ├─ Documents │ ├─ Reports │ └─ Images ├─ Photos

Recursive CTE traverses all nested folders.

Product Category Example

Your category tree:

Example

Electronics ├─ Mobile │ ├─ Android │ └─ iPhone │ └─ Laptop

Recursive query pulls the complete tree.

University Example

Course prerequisites:

text example

Programming Basics
      ↓
Data Structures
      ↓
Algorithms
      ↓
Advanced Algorithms

Recursive CTE retrieves prerequisite chains.

Banking Example

Branch structure:

text example

Head Office
      ↓
Regional Office
      ↓
Branch Office

Recursive queries display the hierarchy.

Understanding UNION ALL

Recursive CTEs almost always use:

sql example

UNION ALL

Why? Because:

Output

Faster
Preserves Duplicates
Required For Recursion

Recursive Depth

Each execution creates a new level.

Example:

Output

Level 1
CEO

Level 2
Managers

Level 3
Employees

Level 4
Interns

Adding Level Information

sql example

WITH EmployeeHierarchy AS
(
    SELECT
        EmployeeID,
        EmployeeName,
        ManagerID,
        1 AS Level

    FROM Employees

    WHERE ManagerID IS NULL

    UNION ALL

    SELECT
        E.EmployeeID,
        E.EmployeeName,
        E.ManagerID,
        EH.Level + 1

    FROM Employees E

    JOIN EmployeeHierarchy EH

    ON E.ManagerID =
       EH.EmployeeID
)

SELECT *
FROM EmployeeHierarchy;

Output Example

Employee	Level
CEO	1
Rahul	2
Priya	2
Amit	3
Neha	3

Stop Condition

Every recursive CTE *must* have:

Output

Termination Condition

Without it:

text example

Infinite Loop

Wrong Example

sql example

WITH Numbers AS
(
    SELECT 1

    UNION ALL

    SELECT Num + 1
    FROM Numbers
)

No stop condition. Result:

Output

Infinite Recursion

Your DB will hate you.

Correct Example

sql example

WHERE Num < 5

Stops it safely.

Recursive CTE vs Loop

Traditional programming:

Output

FOR LOOP
WHILE LOOP

SQL equivalent:

text example

Recursive CTE

Recursive CTE vs Self Join

Self Join:

text example

Limited Levels
Complex Queries

Recursive CTE:

text example

Unlimited Hierarchy Levels
Cleaner Queries

Advantages of Recursive CTEs

Handles Hierarchical Data

Your main use case.

Cleaner Queries

Way more readable than multiple joins.

Dynamic Depth

Works with unknown hierarchy levels.

Reusable Logic

Easy to maintain and tweak.

Powerful Reporting

Great for analytics.

Disadvantages of Recursive CTEs

Performance Cost

Large hierarchies can get expensive.

Infinite Loop Risk

Missing stop conditions = bad time.

Memory Usage

Deep recursion eats resources.

Complexity

Harder if you're just starting out.

Common Mistakes

Missing Stop Condition

The biggest mistake.

Using UNION Instead of UNION ALL

Slower and unnecessary.

Deep Recursion

Might exceed your database's limits.

Poor Indexing

Hierarchy queries can drag if not indexed.

Best Practices

Always Include Stop Condition

Prevents infinite loops.

Use UNION ALL

Better performance.

Index Parent Columns

Makes recursive joins faster.

Limit Recursion Depth

Protect your server.

Test Large Hierarchies

See how it performs before going live.

Common Interview Questions

What is a Recursive CTE?

A CTE that calls itself.

What are the two parts of a Recursive CTE?

text example

Anchor Query
Recursive Query

Why is UNION ALL used?

Combines recursive results efficiently.

What is the purpose of the stop condition?

Prevents infinite recursion.

Where are Recursive CTEs commonly used?

Hierarchies, org charts, category trees, and folder structures.

Summary

Recursive CTEs are powerful for processing hierarchical and recursive data. They have an anchor query that starts the process, and a recursive query that keeps going until you tell it to stop.

In this lesson, you learned:

What Recursive CTEs are
Recursive CTE syntax
Anchor Query basics
Recursive Query basics
Employee hierarchy examples
Folder structure examples
Product category trees
Stop conditions
Advantages and disadvantages
Best practices

Mastering Recursive CTEs matters because hierarchical data shows up everywhere in real applications—org structures, reporting systems, nested categories, and analytics platforms.

Next Step

Continue to the next lesson:

Window Functions →

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for Recursive CTE.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.

Search Terms

sql-complete-guide, sql complete guide, sql, complete, guide, advanced, recursive, cte

Continue learning this concept

PreviousRANK(), DENSE_RANK(), and ROW_NUMBER()NextStored Procedures