What is the difference between a regular CTE and a recursive CTE?

A regular CTE (Common Table Expression) is a temporary named result set used to simplify complex queries, improve readability, and enable reuse within a single SQL query. It defines a set of rows that can be used in the main query.

A recursive CTE is an extension of a regular CTE that allows for recursive queries, where the result of one iteration is used for the next. This is useful for working with hierarchical data, such as traversing a tree or graph.

Example of a recursive CTE for traversing an employee hierarchy:

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT EmployeeID, ManagerID, Name
    FROM Employees
    WHERE ManagerID IS NULL  -- root level
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.Name
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

Thus, the main difference is that a recursive CTE allows building iterative queries that refer to themselves, whereas a regular CTE does not.