--- title: "WITH clause—SQL syntax and semantics" url: "https://docs.yugabyte.com/stable/api/ysql/the-sql-language/with-clause/with-clause-syntax-semantics/" --- # WITH clause—SQL syntax and semantics This section specifies the syntax and semantics of the WITH clause ## Syntax The [with\_clause](/stable/api/ysql/syntax_resources/grammar_diagrams/#with-clause "with_clause") and [`common_table_expression`](/stable/api/ysql/syntax_resources/grammar_diagrams/#common-table-expression "common_table_expression") diagrams are reproduced from the section that describes the [`SELECT` statement](/stable/api/ysql/the-sql-language/statements/dml_select/ "SELECT statement"). - [![Grammar Icon](/icons/file-lines.svg)Grammar](#grammar-2) - [![Diagram Icon](/icons/diagram.svg)Diagram](#diagram-2) ```output.ebnf with_clause ::= WITH [ RECURSIVE ] { common_table_expression [ , ... ] } common_table_expression ::= cte_name [ ( column_name [ , ... ] ) ] AS [ [ NOT ] MATERIALIZED ] ( { select | values | insert | update | delete } ) ``` #### with\_clause WITHRECURSIVE,[common\_table\_expression](#common-table-expression) #### common\_table\_expression [cte\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#cte-name)(,[column\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#column-name))ASNOTMATERIALIZED([select](/stable/api/ysql/syntax_resources/grammar_diagrams#select)[values](/stable/api/ysql/syntax_resources/grammar_diagrams#values)[insert](/stable/api/ysql/syntax_resources/grammar_diagrams#insert)[update](/stable/api/ysql/syntax_resources/grammar_diagrams#update)[delete](/stable/api/ysql/syntax_resources/grammar_diagrams#delete)) ## Semantics The `WITH` clause lets you name a SQL statement which might be one of [`SELECT`](/stable/api/ysql/the-sql-language/statements/dml_select/ "SELECT"), [`VALUES`](/stable/api/ysql/the-sql-language/statements/dml_values/ "VALUES"), [`INSERT`](/stable/api/ysql/the-sql-language/statements/dml_insert/ "INSERT"), [`UPDATE`](/stable/api/ysql/the-sql-language/statements/dml_update/ "UPDATE"), or [`DELETE`](/stable/api/ysql/the-sql-language/statements/dml_delete/ "DELETE"). You can then refer to the statement by name (just as if it were a schema-level view that names a `SELECT` statement) in a subsequent CTE definition or in the statement's final section. A very common use of the data-changing statements in the `WITH` clause is when they have a `RETURNING` clause. Then when you later refer to that statement by name, it behaves the same as if it were a named `SELECT` statement. The uniqueness scope for the name of the CTE is the relation names defined in a particular `WITH` clause. You can define column aliases compactly in the optional parenthesized list that follows the name of the CTE, just as you can with a schema-level view. See the section [Example where a CTE defined in the WITH clause itself has a WITH clause](#example-where-a-cte-defined-in-the-with-clause-itself-has-a-with-clause "Example where a CTE defined in the WITH clause itself has a WITH clause"). Notice that the `WITH` clause is legal in the `SELECT` statement and in each of the kinds of data-changing statements, but not in the `VALUES` statement. See the syntax diagrams for [`SELECT`](/stable/api/ysql/syntax_resources/grammar_diagrams/#select "SELECT"), [`VALUES`](/stable/api/ysql/syntax_resources/grammar_diagrams/#values "VALUES"), [`INSERT`](/stable/api/ysql/syntax_resources/grammar_diagrams/#insert "INSERT"), [`UPDATE`](/stable/api/ysql/syntax_resources/grammar_diagrams/#update "UPDATE"), and [`DELETE`](/stable/api/ysql/syntax_resources/grammar_diagrams/#delete "DELETE"). The recursive CTE is explained in a [dedicated section](/stable/api/ysql/the-sql-language/with-clause/recursive-cte/ "dedicated section"). ### Control CTE materialization You can add the `MATERIALIZED` or `NOT MATERIALIZED` keyword after `AS` in a CTE definition to control whether the CTE is treated as an optimization fence. By default, if a side-effect-free CTE is used only once, the planner may inline it into the outer statement. This can allow outer query restrictions to be pushed down into the CTE. Use `NOT MATERIALIZED` to allow the planner to inline a side-effect-free CTE even when it is used more than once. Use `MATERIALIZED` to force the CTE to be evaluated once and stored before the outer statement uses it. This can be useful when the CTE contains an expensive computation and is used in more than one place in the query. CTEs that contain data-changing statements (`INSERT`, `UPDATE`, or `DELETE`) are always executed exactly once and run to completion. For example, this query forces the CTE result to be materialized before the outer query applies its predicate: ```sql WITH regional_sales(region, total_sales) AS MATERIALIZED ( VALUES ('west', 12000), ('east', 9000)) SELECT region, total_sales FROM regional_sales WHERE total_sales > 10000; ``` And this query allows the planner to inline the CTE: ```sql WITH regional_sales(region, total_sales) AS NOT MATERIALIZED ( VALUES ('west', 12000), ('east', 9000)) SELECT region, total_sales FROM regional_sales WHERE total_sales > 10000; ``` ### Example that uses three data-changing CTEs and a SELECT CTE in the WITH clause First, create some test tables and inspect the contents. ```plpgsql set client_min_messages = warning; do $body$ declare table_names constant text[] := array['t1', 't2', 't3']; drop_table constant text := ' drop table if exists ? cascade'; create_table constant text := ' create table ?(k int primary key, v int)'; insert_table constant text := ' insert into ?(k, v) select g.v, g.v*2 from generate_series($1, $2) as g(v)'; t text not null := ''; i int not null := 1; n constant int := 2; begin foreach t in array table_names loop execute replace(drop_table, '?', t); execute replace(create_table, '?', t); execute replace(insert_table, '?', t) using i, i + n; i := i + n + 1; end loop; end; $body$; select 't1'::text as name, k, v from t1 union all select 't2'::text as name, k, v from t2 union all select 't3'::text as name, k, v from t3 order by 1, 2; ``` This is the result: ``` name | k | v ------+---+---- t1 | 1 | 2 t1 | 2 | 4 t1 | 3 | 6 t2 | 4 | 8 t2 | 5 | 10 t2 | 6 | 12 t3 | 7 | 14 t3 | 8 | 16 t3 | 9 | 18 ``` Now execute the example query. Notice that the `WITH` clause defines an `INSERT` CTE, an `UPDATE` CTE, a `DELETE` CTE, and a `SELECT` CTE. Each of the data-changing CTEs has a `RETURNING` clause; and the `SELECT` CTE accesses the unions returned by each of these. ```plpgsql with i as ( insert into t1(k, v) values (21, 17), (31, 42) returning k, v), u as ( update t2 set v = 99 where k in (4, 5) returning k, v), d as ( delete from t3 where k in (8, 9) returning k, v), s as ( select 'inserted into t1'::text as action, k, v from i union all select 'udated in t2'::text as action, k, v from u union all select 'deleted from t3'::text as action, k, v from d) select action, k, v from s order by k; ``` This is the result: ``` action | k | v ------------------+----+---- udated in t2 | 4 | 99 udated in t2 | 5 | 99 deleted from t3 | 8 | 16 deleted from t3 | 9 | 18 inserted into t1 | 21 | 17 inserted into t1 | 31 | 42 ``` Check that the result is as expected in the affected tables, *"t1"*, *"t2"*, and *"t3"*: ```plpgsql select 't1'::text as name, k, v from t1 union all select 't2'::text as name, k, v from t2 union all select 't3'::text as name, k, v from t3 order by 1, 2; ``` This is the result: ``` name | k | v ------+----+---- t1 | 1 | 2 t1 | 2 | 4 t1 | 3 | 6 t1 | 21 | 17 t1 | 31 | 42 t2 | 4 | 99 t2 | 5 | 99 t2 | 6 | 12 t3 | 7 | 14 ``` ### Example that uses a VALUES CTE and a SELECT CTE in the WITH clause First, clear out the data from table *"t1"* from the previous demonstration. ```plpgsql truncate table t1; ``` Now use a `WITH` clause as part of an `INSERT` statement. ```plpgsql with a1 as ( select g.v as k, g.v*2 as v from generate_series(1, 3) as g(v)), a2 as ( values (10, 17), (11, 42), (12, 99)) insert into t1(k, v) select k, v from a1 union all select column1 as k, column2 as v from a2; select k, v from t1 order by k; ``` This is the result: ``` k | v ----+---- 1 | 2 2 | 4 3 | 6 10 | 17 11 | 42 12 | 99 ``` ### Example where a CTE defined in the WITH clause itself has a WITH clause The following SQL statement uses *two* `WITH` clauses. Each defines its own scope for the names of the CTEs that they define. Notice that the name *"colliding"*, as the name of a relation, is defined in three different scopes: schema scope; the scope of the outer `WITH` clause; and the scope of the inner `WITH` clause that the outer one begins with. For good measure, it's also used in a different namespace: as the name of a column, or column alias. The outer `WITH` clause simply cannot see the names that are defined in the inner `WITH` clause. But each of these `WITH` clauses can see a schema-level relation with a name that collides with a name defined in that `WITH` clause by qualifying it with the schema name. Column names are always defined within the scope of the relation that contains them. The example assumes that a user called *"u1"* exists and that a database called *"demo"* exists. Change these names in the example to match the names that are available to you. ```plpgsql \c demo u1 set client_min_messages = warning; drop table if exists colliding cascade; create table u1.colliding(colliding text primary key); insert into u1.colliding(colliding) values ('goodbye'), ('world'); with colliding(colliding) as ( with a(colliding) as (select 'Hello'), colliding(colliding) as (select colliding from u1.colliding where colliding like 'w%') select ((select colliding from a)||' '||(select colliding from colliding)) ) select (select colliding from u1.colliding where colliding like 'g%')||'—'||colliding as colliding from colliding; ``` This is the result: ``` colliding --------------------- goodbye—Hello world ```