A Common Table Expression (CTE) is a named temporary result set that is valid for the current statement. It is not stored as an object but is used only during query execution. Unlike derived tables, CTEs can be self-referencing and can be referenced multiple times within the same query.
Application scenarios:
- CTEs can be used in multiple places to reuse the same subquery, avoiding the need to duplicate the same logic.
- It can be used to simplify recursive queries, such as those for finding tree-structured data.
- Complex queries can be split into smaller parts, making the query logic clearer and easier to understand.
OceanBase AI Database supports non-recursive and recursive CTEs.
CTE syntax
Common table expressions are an optional part of DML statement syntax, defined using the WITH clause. If a WITH clause contains multiple clauses, they can be separated by commas. Each clause provides a subquery to generate a result set and associates the subquery with a name. The syntax is as follows:
WITH [RECURSIVE]
cte_name [(column_name [, column_name] ...)] AS (subquery)
[, cte_name [(column_name [, column_name] ...)] AS (subquery)] ...
Parameters
Parameter |
Description |
|---|---|
[RECURSIVE] |
Optional keywords for specifying whether to create a recursive CTE.
|
cte_name |
A named common table expression that can be referenced by tables within a WITH clause. |
column_name |
The list of selected column names is used to assign aliases to the columns in the CTE. This allows you to use more readable column names in the main query. |
AS(subquery) |
That is, a CTE subquery is used to generate the CTE result set. Parentheses are required after AS. |
If a list of names in parentheses follows the CTE name, those names are the column names. The number of column names must match the number of columns in the SELECT statement within the CTE. If no column names are specified, the column names will be taken from the first SELECT list in the AS(subquery) part.
Scenarios where the WITH clause is applicable
The WITH clause is allowed in the following scenarios:
At the beginning of a
SELECTstatement.WITH ... SELECT ...At the beginning of a subquery (including derived table subqueries).
SELECT ... WHERE id IN (WITH ... SELECT ...) ... SELECT * FROM (WITH ... SELECT ...) AS dt ...For statements containing a
SELECTstatement, it is placed immediately before theSELECT.INSERT ... WITH ... SELECT ... REPLACE ... WITH ... SELECT ... CREATE TABLE ... WITH ... SELECT ... CREATE VIEW ... WITH ... SELECT ...
Only one WITH clause is allowed at the same level. If a WITH clause contains multiple clauses, you can separate them with commas.
WITH cte1 AS (...), cte2 AS (...) SELECT ...
The WITH clause can define one or more common table expressions, but each CTE name must be unique within that clause. The following example is invalid:
WITH cte1 AS (...), cte1 AS (...) SELECT ...
Structure of a recursive CTE
A recursive CTE has the following structure:
If a CTE in the
WITHclause references itself, theWITHclause must start withWITH RECURSIVE. Otherwise,RECURSIVEis optional.A recursive CTE subquery consists of two parts, separated by
UNION [ALL | DISTINCT]:Note
UNIONorUNION DISTINCT: Combines the result sets of two or moreSELECTstatements into one set and removes duplicate rows.UNION ALL: Combines the result sets of two or moreSELECTstatements into one set without removing duplicate rows.
SELECT ... -- Return the initial row set UNION ALL SELECT ... -- Return an additional row setThe first
SELECTstatement generates one or more initial rows for the CTE without referencing the CTE name. The secondSELECTstatement generates additional rows by recursively referencing the CTE name in itsFROMclause. The recursion terminates when no new rows are generated. Therefore, a recursive CTE consists of a non-recursiveSELECTpart and a recursiveSELECTpart. EachSELECTpart can be a union of multipleSELECTstatements.The data types of the columns in a CTE result set are inferred only from the column types in the non-recursive
SELECTpart, and all columns can be NULL. The recursiveSELECTpart is ignored when determining the data types.Each iteration of the recursive part operates only on the rows generated by the previous iteration. If there are multiple query blocks in the recursive part, their iterations are scheduled in an unspecified order, and each query block operates on the rows generated by its previous iteration or by other query blocks since the last iteration ended.
An example is as follows:
WITH RECURSIVE cte1 (n) AS
(
SELECT 1 /*The non-recursive part retrieves individual rows to generate the initial row set.*/
UNION ALL
SELECT n + 2 FROM cte1 WHERE n < 10 /*For the recursive part, generate a new value that is 2 greater than the nth value in the previous row, until n is not less than 10.*/
)
SELECT * FROM cte1;
Limitations
The following syntax constraints apply to recursive CTE subqueries:
The recursive
SELECTpart must not contain the following structure:Aggregate functions, such as
SUM().Window functions
GROUP BYORDER BYDISTINCT
The recursive
SELECTportion must be referenced only once in a subquery within itsFROMclause. It can reference tables other than the CTE and join with the CTE. If used in such a join, the CTE must not be on the right side of aLEFT JOIN.
For a recursive CTE, the cost estimate displayed by EXPLAIN represents the cost for each iteration and may differ significantly from the total cost. The optimizer cannot predict the number of iterations because it cannot determine when the WHERE clause will evaluate to False.
- Recursive
SELECTstatements and non-recursiveSELECTstatements do not support theLIMITclause.
Examples
The following is a simple example. We will create a student profile table that contains students' IDs, names, and their mentor IDs to demonstrate the difference between recursive and non-recursive CTEs.
First, we create a student profile table and insert some data:
obclient> CREATE TABLE student (
student_id INT PRIMARY KEY,
name VARCHAR(100),
mentor_id INT,
FOREIGN KEY (mentor_id) REFERENCES student(student_id)
);
Query OK, 0 rows affected
obclient> INSERT INTO student (student_id, name, mentor_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 3);
Query OK, 5 rows affected
In this data model, Alice is a top-level student without a mentor. Bob and Charlie are students of Alice, David is a student of Bob, and Eve is a student of Charlie.
Non-recursive CTE example
A non-recursive CTE does not reference itself. For example, if we want to select all direct students of Alice (i.e., first-level students), we can use a non-recursive CTE like this:
WITH Alice_Students AS (
SELECT * FROM student WHERE mentor_id = 1
)
SELECT * FROM Alice_Students;
The execution result will be:
+------------+---------+-----------+
| student_id | name | mentor_id |
+------------+---------+-----------+
| 2 | Bob | 1 |
| 3 | Charlie | 1 |
+------------+---------+-----------+
2 rows in set
Recursive CTE example
Now, if we want to find all direct and indirect students of Alice (i.e., all levels of students), we need to use a recursive CTE. A recursive CTE references itself to query down level by level.
WITH RECURSIVE Student_Hierarchy AS (
-- Anchor Member: Select Alice as the starting point
SELECT student_id, name, mentor_id FROM student WHERE mentor_id IS NULL
UNION ALL
-- Recursive member: selects direct students based on the IDs of their immediate predecessors.
SELECT s.student_id, s.name, s.mentor_id
FROM student s
INNER JOIN Student_Hierarchy sh ON s.mentor_id = sh.student_id
)
SELECT * FROM Student_Hierarchy;
This recursive CTE first selects Alice as the starting point, then recursively selects each student's direct students, until no more students are available.
The execution result will be:
+------------+---------+-----------+
| student_id | name | mentor_id |
+------------+---------+-----------+
| 1 | Alice | NULL |
| 3 | Charlie | 1 |
| 2 | Bob | 1 |
| 5 | Eve | 3 |
| 4 | David | 2 |
+------------+---------+-----------+
5 rows in set
In this example, the recursive CTE allows us to traverse the hierarchical structure of students, whereas the non-recursive CTE only selects students at a fixed level.