Junior — Middle

How do recursive and non-recursive expressions of a common table expression (CTE) differ in SQL?

sobes.tech AI

Answer from AI

A Common Table Expression (CTE) in SQL is a temporary result that can be used within the main query. There are two types of CTEs: recursive and non-recursive.

Non-recursive CTE is simply a named temporary data set defined by a single query. It can be used to simplify complex queries or reuse subqueries.
Recursive CTE is a CTE that references itself, allowing recursive operations such as traversing hierarchies or graphs.

Example of a non-recursive CTE:

WITH RecentOrders AS (
    SELECT * FROM Orders WHERE OrderDate > '2024-01-01'
)
SELECT * FROM RecentOrders WHERE CustomerID = 123;

Example of a recursive CTE (finding all subordinate employees):

WITH RECURSIVE Subordinates AS (
    SELECT EmployeeID, ManagerID, Name FROM Employees WHERE EmployeeID = 1 -- boss
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.Name
    FROM Employees e
    INNER JOIN Subordinates s ON e.ManagerID = s.EmployeeID
)
SELECT * FROM Subordinates;

Thus, recursive CTEs allow building chains and hierarchies, while non-recursive ones simply simplify the query structure.

Previous Next