Common Table Expression in ClickHouse

It is convenient to use CTE in the following cases:

• When one request can get data, and its size fits in memory space
• Multiple uses of the results of this query are required
• Creating recursive queries

A bonus would be the improved readability of your SQL query.

What is the difference between CTE and temporary tables and nested queries?

• If the subquery is correlated, then its call is repeated for each row from the selection, dramatically increasing the cost of executing this query.
• Filling the temporary table with a large amount of data creates a load on the disk.
• Due to the peculiarities of storing temporary tables, executing queries using them increases the execution time.

Syntax

ClickHouse supports both the WITH <expression> AS <identifier> as well as the WITH <identifier> AS <subquery expression> syntaxes.

• Initiate a CTE using WITH.
• Provide a name for a query.
• Follow with AS.
• Define the query.
• If multiple CTEs are required, separate them with a comma.

WITH locations AS
    (
        SELECT location
        FROM table
        WHERE date > (today() - 10)
    )
SELECT *
FROM locations

• Initiate a CTE using WITH.
• Define an expression.
• Follow with AS.
• Provide a name for the expression.
• If multiple CTEs are required, separate them with a comma.

WITH ('USA', 'BRA') AS locations
SELECT 'ARG' IN (locations)

Example

Create:

CREATE TABLE SpareParts
(
    `id` UInt32,
    `partName` String,
    `partOrigin` String,
    `storeID` UInt32
)
ENGINE = MergeTree()
ORDER BY id

Insert:

INSERT INTO SpareParts VALUES (1, 'headlight', 'USA', 1)
INSERT INTO SpareParts VALUES (2, 'hood', 'JPN', 1)
INSERT INTO SpareParts VALUES (3, 'bumper', 'USA', 1)
INSERT INTO SpareParts VALUES (4, 'radiator', 'BRA', 3)
INSERT INTO SpareParts VALUES (5, 'wheel', 'BRA', 2)
INSERT INTO SpareParts VALUES (6, 'stabilizer', 'ARG', 3)
INSERT INTO SpareParts VALUES (7, 'absorber', 'TUR', 2)
INSERT INTO SpareParts VALUES (8, 'cable', 'MEX', 1)
INSERT INTO SpareParts VALUES (9, 'spring', 'MEX', 3)
INSERT INTO SpareParts VALUES (10, 'door', 'USA', 2)

Select:

WITH
    originsByStore AS
    (
        SELECT
            storeID,
            groupArray(partOrigin) AS origins
        FROM SpareParts
        GROUP BY storeID
    ),
    partsByStore AS
    (
        SELECT
            storeID,
            groupArray(partName) AS partNames
        FROM SpareParts
        GROUP BY storeID
    ),
    has(origins, 'USA') = 1 AS isUSA
SELECT
    storeID,
    origins,
    partNames,
    isUSA
FROM originsByStore AS t1
LEFT JOIN
(
    SELECT
        storeID,
        partNames
    FROM partsByStore
) AS t2 USING (storeID)

Result: