## Recursive Common Table Expressions 

A recursive common table expression is one having a subquery that refers to its own name. For example: 

WITH RECURSIVE cte (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 5 ) SELECT * FROM cte; 

When executed, the statement produces this result, a single column containing a simple linear sequence: 

+------+ | n | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+ 

A recursive CTE has this structure: 

•The WITH  clause must begin with WITH RECURSIVE  if any CTE in the WITH  clause refers to itself. (If no CTE refers to itself, RECURSIVE  is permitted but not required.) 

If you forget RECURSIVE  for a recursive CTE, this error is a likely result: 

ERROR 1146 (42S02): Table ’ cte_name ’ doesn’t exist 

•The recursive CTE subquery has two parts, separated by UNION [ALL]  or UNION DISTINCT : 

SELECT ... -- return initial row set UNION ALL SELECT ... -- return additional row sets 

The first SELECT  produces the initial row or rows for the CTE and does not refer to the CTE name. The second SELECT  produces additional rows and recurses by referring to the CTE name in its FROM  clause. Recursion ends when this part produces no new rows. Thus, a recursive CTE consists of a nonrecursive SELECT  part followed by a recursive SELECT  part. 

Each SELECT  part can itself be a union of multiple SELECT  statements. 

•The types of the CTE result columns are inferred from the column types of the nonrecursive SELECT  part only, and the columns are all nullable. For type determination, the recursive SELECT  part is ignored. 

•If the nonrecursive and recursive parts are separated by UNION DISTINCT , duplicate rows are eliminated. This is useful for queries that perform transitive closures, to avoid infinite loops. 

•Each iteration of the recursive part operates only on the rows produced by the previous iteration. If the recursive part has multiple query blocks, iterations of each query block are scheduled in unspecified order, and each query block operates on rows that have been produced either by its previous iteration or by other query blocks since that previous iteration’s end. 

The recursive CTE subquery shown earlier has this nonrecursive part that retrieves a single row to produce the initial row set: 

SELECT 1 

