what is a common table expression, how to use it-OpenSearch(Open Search)-阿里云帮助中心

A Common Table Expression (CTE) is a named subquery scoped to a single query. Think of it as a temporary view defined in the WITH clause — it lets you name an intermediate result set and reference it by name in the same statement. CTEs make complex queries easier to read and maintain by breaking them into named, reusable building blocks.

Syntax

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

The WITH clause has four components:

Component	Required	Description
CTE name	Yes	A name you assign to the CTE. Reference this name later in the same SQL statement.
Column list	No	Output column names in parentheses, separated by commas. The count and data types must match the columns returned by the AS clause. If omitted, the CTE inherits column names from the AS clause.
AS clause	Yes	The query that produces the CTE's result set. The CTE outputs whatever this query returns.
Main SQL statement	Yes	The outer query that references the CTE by name.

Limitations

CTEs are currently supported only in SELECT statements.

Examples

Simple CTE

Define a CTE named T_CTE and select all rows from it directly:

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

CTE in a JOIN clause

Reference the CTE in a JOIN to combine it with another table:

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

CTE in a WHERE EXISTS subquery

Use the CTE as a filter inside a WHERE EXISTS clause:

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 using a CTE slow down my query?

No. A CTE defines a temporary result set but is not executed as a separate step. The database expands the CTE definition when it builds the execution plan, so the CTE and the main query are optimized together. The resulting execution plan is no more complex than the equivalent query written without a CTE, and performance is the same.