CTE-PolarDB(PolarDB)-阿里云帮助中心

A common table expression (CTE) is a named temporary result set scoped to a single SQL statement. Define a CTE with the WITH keyword to simplify complex queries by breaking them into readable, reusable named blocks.

CTEs come in two forms:

Recursive CTE: references itself in the SELECT statement, enabling recursive queries such as tree traversal or series generation.
Non-recursive CTE: does not reference itself; used to simplify a query by naming an intermediate result set.

Syntax

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

Non-recursive CTE

Join two named result sets without repeating subquery logic:

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

Recursive CTE

Generate a sequence from 1 to 5:

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

Every recursive CTE has two parts joined by UNION ALL or UNION:

SELECT ...      -- non-recursive part: produces the initial row set
UNION [ALL]
SELECT ...      -- recursive part: references cte, produces additional rows

The engine runs the recursive part repeatedly until it returns no new rows.

How it works

Each iteration of a recursive CTE follows these rules:

The recursive part can only reference data produced by the previous iteration.
Column types are determined by the non-recursive part, which also sets them to nullable.
Variable values in the recursive part are resolved by name at the start of each iteration, then by position after the SELECT clause executes.

The following example shows how position-based resolution works after name-based lookup:

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 1 AS p, -1 AS q
  UNION ALL
  SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 5
)
SELECT * FROM cte;

In the recursive part, q * 2 is assigned to p and p * 2 is assigned to q based on their positions in the SELECT list. The result:

+------+------+------+
| n    | p    | q    |
+------+------+------+
|    1 |    1 |   -1 |
|    2 |   -2 |    2 |
|    3 |    4 |   -4 |
|    4 |   -8 |    8 |
|    5 |   16 |  -16 |
+------+------+------+

Control recursion depth

Two mechanisms limit how deep a recursive CTE runs:

System variable — cte_max_recursion_depth sets the maximum number of iterations for the recursive part. The default is 500.

LIMIT clause — You can use the LIMIT keyword after the UNION clause in the recursive part to specify the number of times the CTE is executed.

Usage notes

A recursive CTE must use WITH RECURSIVE. Without it, the engine cannot resolve the self-reference and returns: ERROR 1146 (42S02): Table '_cte_name_' doesn't exist.
WITH RECURSIVE can also define a non-recursive CTE; the RECURSIVE keyword has no effect in that case.

Limitations

The recursive part of a CTE has the following restrictions:

Cannot use aggregate functions or window functions.
Cannot include GROUP BY, ORDER BY, or DISTINCT.
Can reference the CTE only in the FROM clause, not in subqueries, and can reference itself only once.
Cannot appear as the right table in a LEFT JOIN.

Parameters

Parameter	Description	Default
`cte_max_recursion_depth`	Maximum number of iterations for the recursive part of a recursive CTE.	500