Common Table Expressions

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

CTEs can be used in combination with SELECTclauses and INSERT,DELETE, UPDATE andUPSERT statements.

Synopsis

Parameters

Overview

A query or statement of the form WITH x AS y IN z creates thetemporary table name x for the results of the subquery y, to bereused 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 forthe subquery over orders, and that name becomes a valid table namefor use in any table expression of thesubsequent 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 expressionssimultaneously with a single WITH clause, separated by commas. Latersubqueries can refer to earlier subqueries by name. For example, thefollowing 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 oby 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 thatmention a CTE name, CockroachDB will choose the CTE definition that isclosest 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 fromtable 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 atthe top level of a SQL statement can contain data-modifyingstatements. 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 INSERTcommon table expression is not at the top level of the query.

Note:

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

Known limitations

Note:

The following limitations may be liftedin 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 isreferred to twice:

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

See also

• Subqueries
• Selection Queries
• Table Expressions
• EXPLAIN

Was this page helpful?
YesNo