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 ​

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 ​

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;

"East",30

A recursive sequence ​

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

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;

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.