WITH (common_table_expression)
Valid in: SQL, ESQL, DBProc, OpenAPI, ODBC, JDBC, .NET
A Common Table Expression (CTE) is a temporary named result set, derived from a simple query and defined within the execution scope of a SELECT or INSERT statement. It is like a derived table or inline view.
CTEs can appear in the following contexts:
• SELECT
• CREATE TABLE…AS SELECT
• DECLARE GLOBAL TEMPORARY TABLE…AS SELECT
• INSERT INTO…SELECT
• CREATE VIEW
For more information and examples, see
Common Table Expressions (CTEs).
A common table expression has the following format:
[ WITH { common_table_expression },... ]
where the common_table_expression format is:
expr_name [ ( { column_name },... ) ] AS ( CTE_query_def )
expr_name
Is an identifier for the common table expression. The expr_name must be unique among other common table expression names defined in the same WITH common_table_expression clause, but expr_name can be the same as the name of a base table or view. Any reference to expr_name in the query uses the common table expression and not the base object.
column_name
Specifies a column name in the common table expression. Duplicate names within a single CTE definition are not allowed. The number of column names specified must match the number of columns in the result set of the CTE_query_def. The list of column names is optional only if distinct names for all resulting columns are supplied in the query expression.
CTE_query_def
Specifies a SELECT statement whose result set populates the common table expression. The SELECT statement for CTE_query_def must meet the same requirements as for creating a view, except a CTE cannot define another CTE.
If more than one CTE_query_def is defined, the queries must be joined by either UNION or UNION ALL.