# WITH

`WITH` defines named subqueries, called common table expressions or CTEs, for one statement. A CTE can make a multi-stage query easier to read and can be referenced as a table source.

## Syntax

```text
WITH [RECURSIVE]
    name [(column, ...)] AS [MATERIALIZED | NOT MATERIALIZED] (query)
    [, ...]
query
```

The optional column list gives names to the CTE's result columns. Each definition is scoped to the statement. `WITH` can also precede supported data-changing statements.

`MATERIALIZED` and `NOT MATERIALIZED` are accepted but currently do not change execution behavior. CoordinatorDB uses subquery CTE syntax; ClickHouse-style scalar expressions in `WITH` are not supported.

## Examples

### Summarizing a grouped result

```sql
WITH orders(region, amount) AS
(
    VALUES ('East', 10), ('East', 20), ('West', 5)
),
region_totals AS
(
    SELECT region, sum(amount) AS total
    FROM orders
    GROUP BY region
)
SELECT region, total
FROM region_totals
WHERE total >= 10
ORDER BY region
FORMAT csv;
```

```text
"East",30
```

### A recursive sequence

A recursive CTE combines an initial result with a term that refers to the CTE itself:

```sql
WITH RECURSIVE sequence(number) AS
(
    VALUES (1)
    UNION ALL
    SELECT number + 1
    FROM sequence
    WHERE number < 5
)
SELECT number
FROM sequence
ORDER BY number
FORMAT csv;
```

```text
1
2
3
4
5
```

The recursive term needs a termination condition. Without one, the query can continue until a request resource limit is reached. Use an outer `ORDER BY` to define the final result order.