Recursive common table expressions

Common table expressions are recursive when they are executed repeatedly, with each execution returning additional rows until the complete result set is retrieved. You can make a common table expression recursive by inserting the RECURSIVE keyword immediately following WITH in the WITH clause. A single WITH clause may contain multiple recursive expressions that can be both recursive and non-recursive.

Recursion provides an easier way of traversing tables that represent tree or tree-like data structures. Without using recursive expressions, the only way to traverse such a structure in a single statement is to join the table to itself once for each possible level.

Restrictions on recursive common table expressions

References to other recursive common table expressions cannot appear within the definition of recursive common table expressions as recursive common table expressions cannot be mutually recursive. However, non-recursive common table expressions can contain references to recursive table expressions, and recursive common table expressions can contain references to non-recursive common table expressions.
The only set operator supported between the initial subquery and the recursive subquery is UNION ALL.
Within the definition of a recursive subquery, a self-reference to the recursive common table expression can appear only within the FROM clause of the recursive subquery and cannot appear on the null-supplying side of an outer join.
The recursive subquery cannot contain a DISTINCT, GROUP BY, or ORDER BY clause.
The recursive subquery cannot use an aggregate function.
To prevent runaway recursive queries, an error is generated if the number of levels of recursion exceeds the current setting of the max_recursive_iterations option. The default value of this option is 100.

Example

Given a table that represents the reporting relationships within a company, you can write a query that returns all the employees that report to one particular person.

Depending on how you write the query, you may want to limit the number of levels of recursion. For example, limiting the number of levels allows you to return only the top levels of management, but may exclude some employees if the chains of command are longer than you anticipated. Providing no restriction on the number of levels ensures no employees are excluded, but can introduce infinite recursion should the execution require any cycles, such as an employee directly or indirectly reporting to her or himself. This situation could arise within a company's management hierarchy if an employee within the company also sits on the board of directors.

The following query demonstrates how to list the employees by management level. Level 0 represents employees with no managers. Level 1 represents employees who report directly to one of the level 0 managers, level 2 represents employees who report directly to a level 1 manager, and so on.

WITH RECURSIVE
  manager ( EmployeeID, ManagerID,
            GivenName, Surname, mgmt_level ) AS
( ( SELECT EmployeeID, ManagerID,       -- initial subquery
           GivenName, Surname, 0
    FROM Employees AS e
    WHERE ManagerID = EmployeeID )
  UNION ALL
  ( SELECT e.EmployeeID, e.ManagerID,   -- recursive subquery
           e.GivenName, e.Surname, m.mgmt_level + 1
    FROM Employees AS e JOIN manager AS m
     ON   e.ManagerID =  m.EmployeeID
      AND e.ManagerID <> e.EmployeeID
      AND m.mgmt_level < 20 ) )
SELECT * FROM manager
ORDER BY mgmt_level, Surname, GivenName;

The condition within the recursive query that restricts the management level to less than 20 (m.mgmt leve < 20) is called a stop condition, and is an important precaution. It prevents infinite recursion if the table data contains a cycle.

The max_recursive_iterations option can also be used to catch runaway recursive queries. The default value of this option is 100 and recursive queries that exceed this number of iterations end, but cause an error. Although this option may seem to diminish the importance of a stop condition, this is not usually the case. The number of rows selected during each iteration may grow exponentially, seriously impacting performance before the maximum is reached. Stop conditions within recursive queries provide a means of setting appropriate limits in each situation.

Recursive common table expressions contain an initial subquery, or seed, and a recursive subquery that, during each iteration, appends additional rows to the result set. The two parts can be connected only with the operator UNION ALL. The initial subquery is an ordinary non-recursive query and is processed first. The recursive portion contains a reference to the rows added during the previous iteration. Recursion stops automatically whenever an iteration generates no new rows. There is no way to reference rows selected before the previous iteration.

The SELECT list of the recursive subquery must match that of the initial subquery in number and data type. If automatic translation of data types cannot be performed, explicitly cast the results of one subquery so that they match those in the other subquery.