ClickHouse Join Algorithms: Performance

Tip: ALL Join

SELECT * FROM t1 ALL JOIN t2 ON t1.id = t2.id;

Returns all matching rows. If t2 has duplicates, t1 rows are duplicated too.

Gotcha: ANY Join

SELECT * FROM t1 ANY JOIN t2 ON t1.id = t2.id;

Returns only one matching row from the right table. Faster than ALL.

Tip: ASOF Join for Time Series

SELECT * FROM trades ASOF JOIN quotes
    ON trades.symbol = quotes.symbol
    AND trades.time >= quotes.time;

Joins on nearest match. Perfect for matching trades with latest quotes.

Gotcha: JOIN Uses Right Table as Dictionary

ClickHouse loads the right table into memory. Keep the smaller table on the right.

Tip: join_use_nulls

SET join_use_nulls = 1;

Fills missing values with NULL instead of defaults.

Gotcha: GLOBAL JOIN

SELECT * FROM t1 GLOBAL JOIN t2 ON t1.id = t2.id;

For distributed tables, broadcasts the right table to all shards.

Tip: Order of Columns in ORDER BY Matters Massively

ClickHouse's primary key is defined by ORDER BY. Put high-cardinality columns first for better data skipping. ORDER BY (timestamp, user_id) is very different from ORDER BY (user_id, timestamp) in query performance.

Tip: Use LowCardinality for Enum-Like Strings

Strings like status, country, browser benefit from LowCardinality(String) — it's stored as a dictionary internally, reducing storage 10x and speeding up scans.

Gotcha: Mutations Are Heavy

ALTER TABLE ... UPDATE and DELETE in ClickHouse create new parts instead of modifying in place. A single mutation on a large table can take hours and block merges. Design for append-only from day one.

Senior Insight

ClickHouse JOINs use different algorithms depending on the table sizes. For small tables (fits in memory), the default hash join is fast. For large tables, ClickHouse uses partial merging or grace hash joins, which spill to disk and are slow. I avoid JOINs on large tables entirely — I pre-join data using materialized views or dictionaries. When JOINs are unavoidable, I use join_algorithm = 'partial_merge' for the best performance on large right-side tables.

Source: ClickHouse Blog (https://clickhouse.com/blog), Altinity Blog (https://altinity.com/blog), Altinity Knowledge Base (https://kb.altinity.com/)