v.20.11Improvement

Subqueries in CTE can reference previous subqueries by name

Official CH changelog text
Subqueries in WITH section (CTE) can reference previous subqueries in WITH section by their name. #16575 (Amos Bird).
Subqueries defined in the WITH clause (Common Table Expressions) can now reference previously defined subqueries within the same WITH section by their name.

Why it matters

This feature improves query modularity and readability by allowing chained CTEs where each subquery can build upon the results of earlier subqueries in the same WITH clause. It solves the problem of inability to reuse intermediate results without duplicating code.

How to use it

Define multiple subqueries inside a single WITH clause, referencing earlier subqueries by their names within subsequent subqueries. For example:

WITH
    subquery1 AS (SELECT ...),
    subquery2 AS (SELECT  FROM subquery1 WHERE ...)
SELECT  FROM subquery2

Related resources

PreviousNext