Common table expressions-OpenSearch(Open Search)-阿里云帮助中心

A common table expression (CTE) is a named subquery defined in a WITH clause. Think of it as a temporary view — it exists only for the duration of a single query, and the SELECT statement that follows can reference it by name. CTEs simplify complex SQL by letting you define a result set once and reuse it within the same statement.

OpenSearch Retrieval Engine Edition V3.7.5 and later support CTEs in SELECT statements only.

Syntax

WITH expression_name[(column_name [,...])]
AS
   (CTE_definition)
SQL_statement;

Parameter	Description
`expression_name`	The name of the CTE. Use this name to reference the CTE in `SQL_statement`.
`column_name`	(Optional) Output fields for the CTE, separated by commas. The count and data types must match those in the `AS` clause. If omitted, the CTE uses the output fields defined in the `AS` clause.
`CTE_definition`	A `SELECT` statement that defines the CTE's result set.
`SQL_statement`	The `SELECT` statement that references the CTE by its `expression_name`.

Examples

Basic usage

Define a CTE and query its result directly:

WITH T_CTE (i1_cte, i2_cte) AS (SELECT i1, i2 FROM t1)
SELECT * FROM T_CTE

The SELECT returns rows from T_CTE, which holds the i1 and i2 columns from t1.

Use a CTE in a JOIN clause

Reference the CTE as a join target:

WITH T_CTE (i1_cte, i2_cte) AS (SELECT i1, i2 FROM t1)
SELECT * FROM t2 JOIN T_CTE ON t2.i1 = T_CTE.i1_cte AND t2.i2 = T_CTE.i2_cte

Use a CTE in a subquery

Reference the CTE inside a WHERE EXISTS subquery:

WITH T_CTE (i1_cte, i2_cte) AS (SELECT i1, i2 FROM t1)
SELECT * FROM t2 WHERE EXISTS (SELECT * FROM T_CTE WHERE t2.i1 = i1_cte AND t2.i2 = i2_cte)

FAQ

Does a CTE execute before the main SELECT statement?

No. The system does not pre-execute the CTE to produce a result set before running the SELECT. Instead, the query optimizer incorporates the CTE definition when building the execution plan, then optimizes the full query as a unit. A statement that uses a CTE produces a less complex execution plan than an equivalent statement without one, and query performance is the same.