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

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:

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

SQL Query:

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

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

 
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.