WITH (Common Table Expressions) Recursive Common Table Expressions A recursive common table expression is one having a subquery that refers to its own name. For example: When executed, the statement produces this result, a single column containing a simple linear sequence: A recursive CTE has this structure: If you forget  RECURSIVE  for a recursive CTE, this error is a likely result: ERROR 1146 (42S02): Table ’ cte_name ’ doesn’t exist 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 recursive CTE subquery shown earlier has this nonrecursive part that retrieves a single row to produce the initial row set: SELECT 1 2711 •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. •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. •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. •The recursive CTE subquery has two parts, separated by  UNION [ALL]  or  UNION DISTINCT : •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.) WITH RECURSIVE cte (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 5 ) SELECT * FROM cte; +------+ | n | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+ SELECT ... -- return initial row set UNION ALL SELECT ... -- return additional row sets