v.20.10New Feature

Support for Named Subqueries with WITH Syntax

Official CH changelog text
Now we support WITH <identifier> AS (subquery) ... to introduce named subqueries in the query context. This closes #2416. This closes #4967. #14771 (Amos Bird).
ClickHouse now supports the WITH <identifier> AS (subquery) syntax to define named subqueries within query contexts.

Why it matters

This feature introduces the ability to declare named subqueries, improving query readability and reusability by allowing users to reference complex subqueries multiple times without repeating them. It enhances query structuring and supports better organization of SQL code.

How to use it

Users can define named subqueries using the WITH clause followed by an identifier and a subquery in parentheses. For example:

WITH subquery_name AS (
    SELECT ...
)
SELECT ... FROM subquery_name

This named subquery can then be used within the main query as a table or source.

Related resources

PreviousNext