What’s New ?

The Top 10 favtutor Features You Might Have Overlooked

Read More

Understanding Recursive CTE in SQL (with code)

  • Feb 03, 2024
  • 8 Minutes Read
Understanding Recursive CTE in SQL (with code)

In the world of databases, knowing advanced features like Recursive Common Table Expressions (CTEs) can significantly enhance your SQL capabilities. This article deals with Recursive CTEs in SQL, exploring both recursive and non-recursive types with examples to get a better understanding of their application.

Common Table Expressions (CTEs) in SQL

Common Table Expre­ssions (CTEs) are used in various ways to deal with complex data. They enhance and optimize the use of querie­s, helping the developer to easily extract information. It is a temporary table we can use in a single SELECT, INSERT, UPDATE, or DELETE stateme­nt. Moreover, it is a virtual table, which exists to make the developer's operation easy with context to the original data.

One of the primary uses of CTEs is in recursive queries, where hierarchical data structures, such as organizational charts or tree-like relationships, need to be traversed efficiently. Recursive CTEs enable the creation of elegant and concise queries for tasks like finding parent-child relationships or calculating cumulative values in a tree structure.

CTEs make it easier to reuse code by creating a temporary result set with a new name. This method can be used multiple times in one query, encouraging a flexible approach. This eases the process of understanding and managing complex SQL queries.

Recursive CTE in SQL

In SQL, Recursive­ Common Table Expressions (CTEs) let you proce­ss information in step-by-step ways, using each new output to create a new context or result.

It is similar to a lift which has to go through a certain number of floors to reach its destination. This helps when you have data that is neste­d or in different layers within the category. It comes in very handy when dealing with complex layered or nested data.

Syntax:

WITH RecursiveCTE_table(Column1, Column2) AS (
SELECT Column1, Column2
FROM
tablename
WHERE
UNION ALL

SELECT Column1, Column2
FROM
tablename
JOIN
RecursiveCTE ON
condition
)
SELECT * FROM RecursiveCTE;

 

Let us look at Recursive CTEs through an example:

Table: Employees

Recursive CTE in SQL Employees Table

SQL Query:

WITH RecursiveEmployeeCTE (EmployeeID, Name, ManagerID, Level) AS (
SELECT EmployeeID, Name, ManagerID, 0 AS Level
FROM Employees
WHERE ManagerID IS NULL

UNION ALL

SELECT e.EmployeeID, e.Name, e.ManagerID, cte.Level + 1
FROM Employees e
JOIN RecursiveEmployeeCTE cte ON e.ManagerID = cte.EmployeeID
)
SELECT *
FROM RecursiveEmployeeCTE;

 

Output:

Recursive CTE in SQL Employees Table Output

In this SQL query, we are creating a RecursiveEmployeeCTE to get a clear view of the employee-manager relationship from the original table called Employees. First, we look at the top-level employees, where they don’t have any managers, assigning them “Level 0”. In the next step, we connect employees with their managers in a clear way. The increment in Level checks employees in the hierarchy. 

Ultimately the result table shows us a simple overview of the organization, showcasing details like EmployeeID, Name, ManagerID, and Level.

Non-Recursive CTEs in SQL

Non-Recursive Common Table Expressions (CTEs) are useful tools in SQL that allow you to define a temporary table without involving any recursive or iterative operations.

Unlike their recursive counterparts, non-recursive CTEs do not need to refer to themselves in operations. Moreover, they serve as a simple, temporary view for simplifying complex queries and enhancing code optimization.

Syntax:

WITH Non-RecursiveCTE_table(Column1, Column2) AS (
SELECT column1, aggregate(Column2) AS aggregated_column
FROM tablename
-Or Use WHERE Condition
GROUP BY Column1
)
SELECT *
FROM Non-RecursiveCTE;

 

Let us understand Non-Recursive CTE through an example:

Table: Products

Non Recursive CTE in SQL Products Table

SQL Query:

WITH NonRecursiveProductCTE (ProductID, ProductName, CategoryID) AS (
SELECT ProductID, ProductName, CategoryID
FROM Products
WHERE CategoryID = 1
)
SELECT *
FROM NonRecursiveProductCTE;

 

Output:

Non Recursive CTE in SQL Products Table Output

In this SQL query we will create a Common Table Expression (CTE) named NonRecursiveProductCTE to retrieve data from a table named Products. This CTE extracts products with ProductID, ProductName, and CategoryID where the category is specified as “ID 1”. 

Ultimately the query displays the product details, showcasing a segmented list of products from the given category from the Products table.

Recursive Vs Non- Recursive CTEs

Some of the major differences between Recursive and Non-Recursive are mentioned below:

Feature

Recursive CTE

Non-Recursive CTE

Definition

Has both initial and recursive members

Has only an initial member

Reference

Refers to itself in the recursive member

Does not refer to itself

Use Cases

Suitable for hierarchical structures, such as organizational charts or tree-like relationships.

Useful for creating temporary result sets without recursion

Syntax

Includes UNION ALL and recursive part in the definition

Consists of a straightforward SELECT statement in the definition

Termination

Requires a termination condition to prevent an infinite loop

Nested structure may make code less clean

Example

Hierarchical queries involving parent-child relationships

Filtering, aggregating, or selecting data without involving recursion

 
Conclusion

Recursive and non-recursive Common Table Expressions (CTEs) in SQL have various features such as handling and manipulating data. You now need to understand the differences between CTE vs Subquery vs Temp Table in SQL to understand how it is different.

FavTutor - 24x7 Live Coding Help from Expert Tutors!

About The Author
Tanish Mallik
I am Tanish, a final-year computer science student with extensive expertise in SQL. My background in the field of data science has honed my strong analytical skills, and I also have hands-on experience in developing data integration and optimization pipelines using Python and MSBI. I am eager to contribute innovative ideas and make an impact in the ever-evolving world of technology.