Common Table Expressions

On this page Carat arrow pointing down
Warning:
CockroachDB v19.1 is no longer supported. For more details, see the Release Support Policy.

Common Table Expressions, or CTEs, provide a shorthand name to a possibly complex subquery before it is used in a larger query context. This improves readability of the SQL code.

CTEs can be used in combination with SELECT clauses and INSERT, DELETE, UPDATE and UPSERT statements.

Synopsis

Parameters

Parameter Description
table_alias_name The name to use to refer to the common table expression from the accompanying query or statement.
name A name for one of the columns in the newly defined common table expression.
preparable_stmt The statement or subquery to use as common table expression.

Overview

A query or statement of the form WITH x AS y IN z creates the temporary table name x for the results of the subquery y, to be reused in the context of the query z.

For example:

icon/buttons/copy
> WITH o AS (SELECT * FROM orders WHERE id IN (33, 542, 112))
  SELECT *
    FROM customers AS c, o
   WHERE o.customer_id = c.id;

In this example, the WITH clause defines the temporary name o for the subquery over orders, and that name becomes a valid table name for use in any table expression of the subsequent SELECT clause.

This query is equivalent to, but arguably simpler to read than:

icon/buttons/copy
> SELECT *
    FROM customers AS c, (SELECT * FROM orders WHERE id IN (33, 542, 112)) AS o
   WHERE o.customer_id = c.id;

It is also possible to define multiple common table expressions simultaneously with a single WITH clause, separated by commas. Later subqueries can refer to earlier subqueries by name. For example, the following query is equivalent to the two examples above:

icon/buttons/copy
> WITH o       AS (SELECT * FROM orders WHERE id IN (33, 542, 112)),
       results AS (SELECT * FROM customers AS c, o WHERE o.customer_id = c.id)
  SELECT * FROM results;

In this example, the second CTE results refers to the first CTE o by name. The final query refers to the CTE results.

Nested WITH clauses

It is possible to use a WITH clause in a subquery, or even a WITH clause within another WITH clause. For example:

icon/buttons/copy
> WITH a AS (SELECT * FROM (WITH b AS (SELECT * FROM c)
                            SELECT * FROM b))
  SELECT * FROM a;

When analyzing table expressions that mention a CTE name, CockroachDB will choose the CTE definition that is closest to the table expression. For example:

icon/buttons/copy
> WITH a AS (TABLE x),
       b AS (WITH a AS (TABLE y)
             SELECT * FROM a)
  SELECT * FROM b;

In this example, the inner subquery SELECT * FROM a will select from table y (closest WITH clause), not from table x.

Data modifying statements

It is possible to use a data-modifying statement (INSERT, DELETE, etc.) as a common table expression.

For example:

icon/buttons/copy
> WITH v AS (INSERT INTO t(x) VALUES (1), (2), (3) RETURNING x)
  SELECT x+1 FROM v

However, the following restriction applies: only WITH sub-clauses at the top level of a SQL statement can contain data-modifying statements. The example above is valid, but the following is not:

icon/buttons/copy
> SELECT x+1 FROM
    (WITH v AS (INSERT INTO t(x) VALUES (1), (2), (3) RETURNING x)
     SELECT * FROM v);

This is not valid because the WITH clause that defines an INSERT common table expression is not at the top level of the query.

Note:

If a common table expression contains a data-modifying statement (INSERT, DELETE, etc.), the modifications are performed fully even if only part of the results are used, e.g., with LIMIT. See Data Writes in Subqueries for details.

Known limitations

Note:

The following limitations may be lifted in a future version of CockroachDB.

Referring to a CTE by name more than once

It is currently not possible to refer to a common table expression by name more than once.

For example, the following query is invalid because the CTE a is referred to twice:

icon/buttons/copy
> WITH a AS (VALUES (1), (2), (3))
  SELECT * FROM a, a;

See also


Yes No
On this page

Yes No