PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteA ClickHouse CTE is a named subquery declared in WITH. It can make a query easier to read and reuse, but an ordinary CTE is not a cache: ClickHouse substitutes its definition at each reference, which can repeat the work. Use WITH RECURSIVE for supported hierarchy and graph traversal patterns; consider the separate, experimental MATERIALIZED form when repeated evaluation is costly or must yield shared results.
How do I write a CTE in ClickHouse?
Declare a named subquery in a WITH clause, then use its name as a table expression in the query. For example:
WITH recent_events AS (
SELECT user_id, event_time
FROM events
WHERE event_time >= now() - INTERVAL 1 DAY
)
SELECT user_id, count()
FROM recent_events
GROUP BY user_id;Here, recent_events names the result of the subquery. The name can be used where a table expression is allowed in the SELECT query and in child query scopes. The official ClickHouse WITH reference describes ordinary CTEs as substituted from their definitions wherever referenced.
CTEs and scalar aliases are different
A WITH clause can also define a scalar expression, such as WITH 10 AS limit_value. That is a scalar alias, not a relation-valued CTE. When scalar expressions refer to identifiers, ClickHouse resolves names in the closest scope; an unbound name may resolve unexpectedly. The documentation recommends binding identifiers in a lambda when predictable name resolution matters.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Are ClickHouse CTEs materialized?
Ordinary CTEs are inlined at each reference; ClickHouse does not promise one shared, cached result. If the same CTE is referenced more than once, its subquery may execute repeatedly. This affects both cost and results: a CTE involving a nondeterministic expression such as generateRandom can produce different values at different references.
So use an ordinary CTE primarily to name and organize query logic. If you need one computed result reused across references, evaluate the materialized option separately, subject to its experimental status and setting requirements below.
How do I use a recursive CTE in ClickHouse?
A recursive CTE starts with a seed query, combines it with a recursive term using UNION ALL, and has that term refer to the CTE’s output. ClickHouse evaluates the seed into a working table, then repeatedly evaluates the recursive term against the current working table. Processing stops when the next working table is empty or an abort condition applies.
WITH RECURSIVE numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;This example starts at 1 and adds 1 until it reaches 10. Recursive CTEs can also support tree traversal, reachability, and graph-like relationships. The ClickHouse 24.4 release article demonstrates finding stations reachable from Oxford Circus and describes transitive closure.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteCheck analyzer support for your server version
Recursive CTEs require the query analyzer. The current ClickHouse documentation says the analyzer was introduced in 24.3, became the default in 24.3, and has been mandatory since 26.9. On older configurations where it was disabled, the documentation notes that recursive queries can fail with UNKNOWN_TABLE or UNSUPPORTED_METHOD; its stated remedies are enabling enable_analyzer or upgrading. Check your deployed version and configuration rather than assuming recursive syntax is available in every older setup.
Make traversal order and termination explicit
For ordered traversal, the current documentation shows carrying a path array for depth-first ordering or a depth value for breadth-first ordering. In a cyclic graph, track visited nodes or edges and stop expanding a branch when a cycle is detected. An unguarded cycle can continue until the recursive evaluation depth limit is reached; the documented default is 1000, controlled by max_recursive_cte_evaluation_depth. Increasing that limit does not replace designing a traversal that terminates.
When should I use a materialized CTE?
A materialized CTE is a separate choice from an ordinary CTE. ClickHouse documents this form:
SET enable_materialized_cte = 1;
WITH per_user AS MATERIALIZED (
SELECT user_id, count() AS events
FROM events
GROUP BY user_id
)
SELECT ...With enable_materialized_cte enabled, ClickHouse computes the subquery once and stores its result in a temporary table for its references. The feature is labeled experimental in the documentation. If the setting is off, MATERIALIZED is ignored and the CTE is inlined with a warning. See the WITH reference and the ClickHouse 26.3 release article for the documented behavior.
Constraints to account for
- Materialized CTEs cannot be combined with
RECURSIVE. - A materialized CTE cannot refer to columns from outer query scopes.
- Materialized CTEs can reference other materialized CTEs; the documentation also describes dependency resolution and forward references.
- For a CTE referenced only once, ClickHouse may inline it to avoid materialization overhead.
What the published performance example does—and does not—show
In the UK property-price query example reported in ClickHouse’s 2026 release article, one run without materialization took 2.590 seconds, processed 91.36 million rows and 892.55 MB, and used 1.50 GiB peak memory. The materialized version of that example took 1.243 seconds, processed 60.91 million rows and 679.63 MB, and used 87.40 MiB peak memory. ClickHouse characterized that example as a little over twice as fast with materialization. These are reported results for that example and its query and dataset, not typical results or a guarantee for another workload, schema, or server version.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.How should I choose between ordinary and materialized CTEs?
| Situation | Pattern to consider | Reason and qualification |
|---|---|---|
| One reference, or inexpensive work | Ordinary CTE | It names query logic without introducing a temporary materialized result. |
| Multiple references to a costly scan, aggregation, or join | Test a materialized CTE | It can compute the subquery once, but requires enable_materialized_cte and remains experimental in the documentation. |
| Multiple references that must see the same nondeterministic rows | Test a materialized CTE | Ordinary references may re-execute and return different results; confirm behavior on the server version in use. |
| Hierarchy or graph traversal | Recursive CTE | Use a seed and recursive term, with cycle detection and a suitable termination condition; recursion requires the query analyzer and cannot be combined with materialized CTEs. |
For a performance decision, compare both forms on the target server with representative data. Review elapsed time, rows and bytes processed, and peak memory; temporary-result storage and computation overhead can change the outcome. ClickHouse’s 26.9 release presentation also notes a recursive CTE chunk-processing optimization, which is another reason to consider server version when evaluating behavior.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
