Common Table Expressions

A Common Table Expression (CTE) is a temporary result set that is used to retrieve data in a database. A CTE is not an object and cannot be saved. It is only valid within the current statement that executes it. OceanBase Database supports both recursive and non-recursive CTEs.

CTE syntax

A common table expression is an optional part of the DML statement syntax. It is defined by using the WITH clause. Multiple clauses can be separated by commas. Each clause provides a name and a subquery to generate a result set. The syntax is as follows:

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

The parameters are described as follows:

• cte_name is the name of the common table expression, which can be referenced by the table in the WITH clause.

• AS(subquery) is referred to as the "CTE subquery," which is used to generate the result set of the CTE. There must be parentheses after the AS keyword.

If the CTE subquery references its own name, the common table expression is recursive. If all CTEs in the WITH clause are recursive, the RECURSIVE keyword must be specified.

In the following example, the WITH clause is used to define common table expressions named cte1 and cte2. Then, the SELECT statement after the WITH clause is used to reference the common table expressions:

WITH
   cte1 AS (SELECT col1, col2 FROM tbl1),
   cte2 AS (SELECT col3, col4 FROM tbl2)
SELECT col2, col4 FROM cte1 JOIN cte2
WHERE cte1.col1 = cte2.col3;

If a name of a CTE is followed by a list of column names in parentheses, the list of column names is the name of the columns of the CTE. The number of names in the list must be equal to the number of columns in the result set. Otherwise, the column names in the list are used as the column names of the result set. Column names in the first select list of the CTE subquery are used as the default column names of the result set.

Scenarios for the WITH clause

The WITH clause can be used in the following scenarios:

• At the beginning of a SELECT statement.

  WITH ... SELECT ...
  

• At the beginning of a subquery (including a derived table subquery).

  SELECT ... WHERE id IN (WITH ... SELECT ...) ...
  SELECT * FROM (WITH ... SELECT ...) AS dt ...
  

• Immediately before the SELECT clause in a statement that contains a SELECT clause.

  INSERT ... WITH ... SELECT ...
  REPLACE ... WITH ... SELECT ...
  CREATE TABLE ... WITH ... SELECT ...
  CREATE VIEW ... WITH ... SELECT ...
  

Only one WITH clause is allowed at the same level. Multiple clauses can be separated by commas.

WITH cte1 AS (...), cte2 AS (...) SELECT ...

The WITH clause can define one or more common table expressions. However, the names of the CTEs must be unique within the same WITH clause. The following example is illegal:

WITH cte1 AS (...), cte1 AS (...) SELECT ...

Recursive CTEs

A recursive common table expression (CTE) is a query that references itself.

Structure of a recursive CTE

A recursive CTE has the following structure:

• If the CTE in the WITH clause references itself, the WITH clause must start with WITH RECURSIVE. Otherwise, RECURSIVE is not required.

• The recursive CTE contains two SELECT statements that are separated by UNION [ALL] or UNION DISTINCT:

  SELECT ...      -- Returns an initial set of rows.
  UNION ALL
  SELECT ...      -- Returns additional sets of rows.
  

  The first SELECT statement generates one or more initial rows for the CTE and does not reference the CTE name. The second SELECT statement uses a FROM clause that contains the CTE name to recursively generate additional rows. The recursion ends when this part does not generate new rows. Therefore, a recursive CTE consists of a non-recursive SELECT part and a recursive SELECT part. Each SELECT part can be a combination of multiple SELECT statements.

• The data types of the result columns of a recursive CTE are inferred from the column types of the non-recursive SELECT part. All columns can be null. The types of the recursive SELECT part are ignored. If UNION DISTINCT is used to separate the non-recursive and recursive parts, duplicate rows are eliminated. This can prevent infinite loops in queries that compute transitive closures.

• The recursion operates only on the rows generated in the previous iteration. If the recursive CTE contains multiple query blocks, the iterations of each query block are scheduled in an unspecified order, and a query block operates on the rows generated by the previous iteration of itself or other query blocks since the last iteration ended.

Here is an example:

WITH RECURSIVE cte1 (n) AS
(
  SELECT 1                /*Non-recursive part. It selects a single row to generate the initial row set.*/
  UNION ALL
  SELECT n + 2 FROM cte1 WHERE n < 10     /*Recursive part. It selects a value that is 2 greater than the n values in the previous row set and generates a new row set until n is greater than or equal to 10.*/
)
SELECT * FROM cte1;

Prerequisites

The following syntax constraints apply to the recursive CTE:

• The recursive SELECT part must not contain the following elements:

  • Aggregate functions such as SUM()

  • Window functions

  • GROUP BY

  • ORDER BY

  • DISTINCT

• The recursive SELECT part must reference only once the subquery in the FROM clause. It can reference tables other than the CTE and join with the CTE. If used in such a join, the CTE cannot be located on the right side of a LEFT JOIN.

The cost estimates displayed in the EXPLAIN statement for a recursive CTE represent the cost of each iteration and can differ significantly from the total cost. The optimizer cannot predict the number of iterations because it cannot predict when the WHERE condition becomes false.