CTE in ClickHouse: macro instead of optimization

CTE in ClickHouse: Why It's a Macro, Not an Optimization

Developers switching to ClickHouse from OLTP systems often carry over their habit of using Common Table Expressions (CTE) with WITH. ClickHouse supports this syntax, but it acts as a parser macro: the CTE is inlined and executed every time it's referenced. This leads to repeated subquery execution, increased load, and unpredictable results.

Consider this typical query that highlights the issue:

WITH cte_numbers AS
(
    SELECT
        num
    FROM generateRandom('num UInt64', NULL)
    LIMIT 1000000
)
SELECT
    count()
FROM cte_numbers
WHERE num IN (SELECT num FROM cte_numbers)

You'd expect it to count all 1,000,000 rows, but the result is around 600,000 and varies on each run. The culprit? generateRandom runs twice, generating different datasets. EXPLAIN reveals two separate reads from the source.

In reality, the parser expands the CTE into:

SELECT
    count()
FROM
    (
        SELECT
            num
        FROM
            generateRandom('num UInt64', NULL)
        LIMIT 1000000
    )
WHERE
    num IN (
        SELECT
            num
        FROM
            generateRandom('num UInt64', NULL)
        LIMIT 1000000
    )

Alternatives to CTE for Reliable Performance

The go-to solution is CREATE TEMPORARY TABLE. These in-memory tables materialize data once:

CREATE TEMPORARY TABLE cte_numbers AS
(
    SELECT
        num
    FROM generateRandom('num UInt64', NULL)
    LIMIT 1000000
)
;

SELECT
    count()
FROM cte_numbers
WHERE num IN (SELECT num FROM cte_numbers)
;

Now the result is a steady 1,000,000. Temporary tables live for the session, skip disk storage, and shine in complex ETL workflows.

Other options:

• Subqueries with materialization: Use ARRAY JOIN or MATERIALIZED views for repeated computations.
• Experimental CTE materialization: Newer versions (as of April 2026) offer MATERIALIZED for WITH, but its experimental status means thorough production testing.

| Approach | Materialization | Performance | Stability |

|---------|-----------------|-------------|-----------|

| CTE WITH | No | N-fold increase | Low |

| TEMP TABLE | Yes | O(1) | High |

| Subquery | Partial | Medium | Medium |

Query Optimization Without the Pitfalls

For mid- and senior-level developers, the key to efficiency is mastering the execution plan. Always run EXPLAIN PIPELINE before production:

• Check the number of Read operations.
• Avoid unmaterialized subqueries in WHERE/JOIN.
• For large datasets, pair with GROUP BY and aggregates.

Here's an optimized version using a temporary table and indexing:

• Create the table sorted by num.
• Add ORDER BY num in the CTE to speed up IN.
• Scale on a cluster with DISTRIBUTED.

In production, steer clear of CTEs in hot queries: they're fine only for one-off substitutions without repeats.

Key Takeaways

• CTE as macro: Inlined on every reference, runs multiple times — always check EXPLAIN.
• Temporary tables: The reliable way to materialize for reuse.
• Experimental features: Test MATERIALIZED WITH carefully and monitor load.
• Performance: 2x+ difference in time and resources with repeated CTEs.
• Best practice: Use explicit tables for ETL, subqueries for simple cases.

— Editorial Team