# 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

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.

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:

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

## See also

- Subqueries
- Selection Queries
- Table Expressions
- `EXPLAIN`