what is a common table expression, how to use it - OpenSearch

This topic describes Common Table Expressions (CTEs) and provides simple examples.

Overview

A CTE is a temporary named result set that exists only for the duration of a single query. You can reference the CTE later in the same query, which can help simplify the statement. CTEs are currently supported only in SELECT statements. The standard syntax for a CTE is as follows:

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

Specify a name for the CTE that you want to create. In an SQL statement, you can specify the name to reference the CTE.
Specify output fields in parentheses and separate multiple output fields with commas. Make sure that the number of output fields and the data types of the output fields are the same as the number of output fields and the data types of the output fields that you specify in the AS clause of the CTE. If you do not specify an output field in this expression, the system uses the output fields that you specify in the AS clause.
Then, an AS is used to define the data output clause of the CTE. The final output data of the CTE will be generated by this clause.
After you define a CTE, you can reference the CTE in SQL statements.

Examples

The following examples show how to use a CTE.

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

The preceding example shows a simple CTE where the main SELECT statement directly outputs the result set from the CTE.

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

You can also use a CTE in a JOIN 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)

Or you can use it in your own query.

FAQ

Performance

A CTE defines a temporary result set. However, this does not mean the CTE is executed as a separate step. Instead, the database expands the CTE definition when it creates the execution plan. This process allows the CTE and the main query to be optimized together. As a result, the execution plan is no more complex than an equivalent query without a CTE, and the performance is the same.