Joins

Joins are the part of SQL ClickHouse is honest about being weaker at. A mature OLTP engine like Postgres has decades of join-order planning, persistent indexes on both sides, and statistics-driven cost estimation. ClickHouse's planner is younger and simpler, and its storage engine has no concept of a join index — every join has to build one from scratch, at query time.

How a join actually runs

The default algorithm is a hash join: the right-hand table (or subquery) is read in full and loaded into an in-memory hash table, keyed on the join columns. Then the left-hand table is streamed row by row, probing that hash table for matches.

The consequence follows directly from the mechanism: the right-hand side has to fit comfortably in memory. There is no equivalent of a Postgres merge join over two pre-sorted, indexed tables that never materializes either side in full.

SELECT e.user_id, e.event, u.plan
FROM events AS e
JOIN users AS u ON e.user_id = u.id
WHERE e.event_time >= today() - 1;

GLOBAL JOIN: the distributed correctness trap

This is the single most common way to silently get wrong answers out of a ClickHouse cluster. When a query against a Distributed table runs a join, each shard executes the join independently, against its own local data. If the right-hand table is itself sharded (not fully present on every node), each shard only ever sees its own slice of it — rows on other shards silently never match, and you get fewer results than you should, with no error raised.

-- Risky on a cluster if 'users_distributed' is sharded:
SELECT e.user_id, u.plan
FROM events_distributed AS e
JOIN users_distributed AS u ON e.user_id = u.id;

-- Correct: GLOBAL JOIN evaluates the right-hand side once on the
-- initiator node, then broadcasts the full result to every shard
-- before each shard joins against it locally.
SELECT e.user_id, u.plan
FROM events_distributed AS e
GLOBAL JOIN users_distributed AS u ON e.user_id = u.id;

Keep the right-hand side small

Because the right-hand table is built into an in-memory hash table in full before any matching happens, join performance and memory usage scale with the size of the right-hand side, almost independently of the left. Practical guidance:

• Put the smaller table on the right — ClickHouse doesn't reliably reorder this for you the way a cost-based OLTP planner would.
• For small, slow-changing reference data (country codes, plan tiers, product catalogs), prefer a dictionary and dictGet() over a join entirely — same lookup, no per-query hash table build, no GLOBAL broadcast to worry about.
• Filter both sides down with WHERE before the join runs wherever possible, rather than joining first and filtering after.