New in v2.0: 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:
> 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.
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
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:
> WITH a AS (VALUES (1), (2), (3))
SELECT * FROM a, a;
Using CTEs with data-modifying statements
Resolved as of v2.1.
If a common table expression containing data-modifying statement is not referred to by the top level query, either directly or indirectly, the data-modifying statement will not be executed at all.
For example, the following query does not insert any row, because the CTE a
is not used:
> WITH a AS (INSERT INTO t(x) VALUES (1), (2), (3))
SELECT * FROM b;
Also, the following query does not insert any row, even though the CTE a
is used, because
the other CTE that uses a
are themselves not used:
> WITH a AS (INSERT INTO t(x) VALUES (1), (2), (3)),
b AS (SELECT * FROM a)
SELECT * FROM c;
To determine whether a modification will effectively take place, use
EXPLAIN
and check whether the desired data
modification is part of the final plan for the overall query.
Using CTEs with views
Resolved as of v2.1.
It is not yet possible to use a common table expression inside the selection query used to define a view.
Using CTEs with VALUES
clauses
Resolved as of v2.1.
It is not yet possible to use a common table expression define outside of a VALUES
clause in a subquery inside the VALUES
clause, for example:
> WITH a AS (...) VALUES ((SELECT * FROM a));
Using CTEs with Set Operations
Resolved as of v2.1.
It is not yet possible to use a common table expression defined outside of a set expression in the right operand of a set operator, for example:
> WITH a AS (SELECT 1)
SELECT * FROM users UNION SELECT * FROM a; -- "a" used on the right, not yet supported.
For UNION
, you can work around this limitation by swapping the operands. For the other set operators, you can inline the definition of the CTE inside the right operand.