CTE - PolarDB - Alibaba Cloud Documentation Center

This topic describes the usage and syntax of common table expressions (CTEs).

Overview

A CTE is a named temporary result set that exists within the scope of a single SQL statement to simplify the statement. A CTE is defined by using the WITH keyword. CTEs can be classified into the following two types:

Recursive CTE: A recursive CTE references itself in the SELECT statement. Recursive CTEs can be used to perform recursive queries.
Non-recursive CTE: A non-recursive CTE does not reference itself in the statement.

Syntax

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

Recursive CTE

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

Non-recursive CTE

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;

Constraint

The recursive part of a CTE cannot contain aggregate functions, window functions and the following clauses: GROUP BY, ORDER BY, and DISTINCT.
In the recursive part of a CTE, the CTE can be referenced only in the FROM clause but not subqueries. A CTE can be referenced by itself only once.
In the recursive part of a CTE, the CTE cannot be used as the right table in a LEFT JOIN clause.

Usage notes

A recursive CTE must start with the WITH RECURSIVE keyword. Otherwise, the following error is returned: ERROR 1146 (42S02): Table 'cte_name' doesn't exist.
You can also use WITH RECURSIVE to define a non-recursive CTE. In this case, the RECURSIVE keyword does not take effect.
A recursive CTE contains the two clauses that are concatenated by the UNION ALL or UNION keyword, as shown by the following example:
```
SELECT ...      -- non recursive part, return initial row set
UNION [ALL]
SELECT ...      -- recursive part, return additional row sets				
```
The first clause is also called the non-recursive part of the CTE. This clause generates the initial data and cannot reference the CTE itself. The second clause is also called the recursive part of the CTE. This clause generates additional rows and references the CTE itself. The query is iteratively executed until the recursive part of the CTE cannot return new data.
The type of date returned by a recursive CTE is specified as nullable by the non-recursive part of the CTE.
In each iteration, the recursive part of the CTE can reference only data generated by the last iteration.
The values of variables in a CTE are specified based on the names and positions of the variables in each iteration. The following CTE provides an example:
```
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 preceding CTE, the values of the n, p, and q variables are specified in the recursive part based on their names after their values are configured in the non-recursive part. After the clauses in the recursive part are executed, the values of the variables are specified based on their positions in the project. Therefore, the following result is returned:
```
+------+------+------+
| n    | p    | q    |
+------+------+------+
|    1 |    1 |   -1 |
|    2 |   -2 |    2 |
|    3 |    4 |   -4 |
|    4 |   -8 |    8 |
|    5 |   16 |  -16 |
+------+------+------+
```
You can use the LIMIT keyword after the UNION clause in the recursive part of a CTE to specify the times for which the CTE is executed.

Parameters

The cte_max_recursion_depth parameter specifies the maximum number of iterations of the recursive part of a recursive CTE. The default value of this parameter is 500.