Recursive Common Table Expressions

Recursion (in SQL) is a process in which a query executes itself repeatedly for a given number of time to obtain a result set.

Recursive Query: When a query references a recursive CTE, it is referred to as Recursive Query. A common use of recursive queries is to return hierarchical data.

Recursive Common Table Expression: When an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained, it becomes a recursive Common Table Expression.

The code required to run a recursive query within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement can be greatly simplified by the use of recursive CTE.

Structure of a Recursive CTE

The structure of a recursive CTE is similar to recursive routines in other programming languages. A recursive CTE can return multiple rows whereas a recursive routine in other languages returns a scalar value.

A recursive CTE consists of three elements:

• Invocation – The invocation of the recursive CTE consists of one or more CTE_query_definitions joined by UNION ALL, UNION, EXCEPT, or INTERSECT operators.
  These query definitions are referred to as anchor members because they form the base result set of the CTE structure.
  CTE_query_definitions are considered anchor members unless they reference the CTE itself. All anchor-member query definitions must be positioned before the first recursive member definition, and a UNION ALL operator must be used to join the last anchor member with the first recursive member.
• Recursive Invocation – Recursive invocation includes one or more CTE_query_definitions joined by UNION ALL operators that reference the CTE itself. These query definitions are referred to as recursive members.
• Termination check – The termination check is not explicit, it is implicit. When no rows are returned from the previous invocation, recursion stops.