Common Table Expressions

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

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:

> 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:

> 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:

> 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:

> 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:

> 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:

> 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:

> 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.

{{site.data.alerts.callout_info}} 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. {{site.data.alerts.end}}

Known limitations

{{site.data.alerts.callout_info}} The following limitations may be lifted in a future version of CockroachDB. {{site.data.alerts.end}}

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:

> WITH a AS (VALUES (1), (2), (3))
  SELECT * FROM a, a;

See also

• Subqueries
• Selection Queries
• Table Expressions
• EXPLAIN