JOINs in ClickHouse: kinds, table order and memory

ClickHouse joins are fully supported, and fast when you respect how they work: the engine loads the right-hand table into memory (by default as a hash table), then streams the left-hand table past it. Almost every join problem is either “the right side is too big” or “I joined more than I needed”.

Join kinds

Two small tables show the semantics. a has ids 1, 2, 3; b has id 2 twice and id 4.

CREATE TABLE a (id UInt32, x String) ENGINE = Memory;
CREATE TABLE b (id UInt32, y String) ENGINE = Memory;
INSERT INTO a VALUES (1,'a1'),(2,'a2'),(3,'a3');
INSERT INTO b VALUES (2,'b2'),(2,'b2bis'),(4,'b4');

Query	Result rows (id, x, y)
a INNER JOIN b USING (id)	2 a2 b2, 2 a2 b2bis
a LEFT JOIN b USING (id)	1 a1 '', 2 a2 b2, 2 a2 b2bis, 3 a3 ''
a LEFT ANY JOIN b USING (id)	1 a1 '', 2 a2 b2, 3 a3 '' (one match at most per left row)
a LEFT SEMI JOIN b USING (id)	2 a2 b2 (left rows that have a match, once)
a LEFT ANTI JOIN b USING (id)	1 a1 '', 3 a3 '' (left rows with no match)
a FULL JOIN b ON a.id = b.id	all of the above, plus the unmatched b row 4 b4

Notes:

• Unmatched rows get default values, not NULL. In a LEFT JOIN the missing y is the empty string, and a missing number would be 0. If you need a real NULL, set join_use_nulls = 1 (then y becomes Nullable(String), and the missing ones print as \N). Without it you cannot distinguish “no match” from “matched an empty value”.
• ANY is the join to use for lookups where the right side is supposed to be unique. It keeps the first match per key and avoids the row multiplication a plain join gives when the right side has duplicates (the doubled 2 rows above). It is also cheaper, since the hash table needs one row per key.
• SEMI and ANTI answer “does a match exist?” and “does no match exist?”. They return only left-side columns’ rows, and never multiply them. They are the efficient form of EXISTS / NOT EXISTS, and WHERE key IN (subquery) is close to a semi join.
• Join conditions use ON with equality (plus other conditions) or USING. Equality on the key is what makes a hash join possible.

The right table is the one in memory

For a hash join, put the smaller table on the right. The left table is streamed and its size does not matter for memory.

Older releases did exactly what you wrote, so putting the big table on the right was a classic mistake. Current releases have a query-plan optimizer that can swap the sides for you (query_plan_join_swap_table, auto by default), and reorder multi-table joins (query_plan_optimize_join_order_limit). You can see it working. With a 100 MB memory limit and a 3-million-row events table against a 1000-row small_users table, written in the bad order:

SELECT count() FROM small_users AS u INNER JOIN events AS e ON e.user_id = u.user_id
SETTINGS max_memory_usage = 100000000, join_algorithm = 'hash';
-- 1000

It works, because the optimizer builds the hash table from small_users. Switch the optimizer off and the same query fails:

SELECT count() FROM small_users AS u INNER JOIN events AS e ON e.user_id = u.user_id
SETTINGS max_memory_usage = 100000000, join_algorithm = 'hash',
         query_plan_join_swap_table = 'false';
-- Code: 241. Query memory limit exceeded ... While executing FillingRightJoinSide.

Do not rely on the optimizer in code you care about: it works from estimates, and it does not exist for every shape of query (subqueries, distributed joins). Write the small table on the right yourself and treat the swap as a safety net.

Join only what you need

Memory is proportional to the right table’s columns you actually use, times its rows.

• Select only needed columns. SELECT * over a join drags every right column into the hash table. List the columns you use.
• Filter and aggregate the right side first, in a subquery, so that only the necessary rows and columns are built:

SELECT e.country, count() AS pro_events
FROM events AS e
INNER JOIN (SELECT user_id FROM users WHERE plan = 'pro') AS u ON e.user_id = u.user_id
GROUP BY e.country;

• If you only need existence, use SEMI / ANTI or IN, which store only keys.
• If you need only one attribute per key, a dictionary with dictGet is often better than a join: it is built once and shared across queries.
• Denormalise when a dimension is joined on every query. Joins on hot paths are a sign the data model should carry the column.

When the right table does not fit in memory

Default behaviour is to throw MEMORY_LIMIT_EXCEEDED. The setting join_algorithm chooses a different approach. The default is direct,parallel_hash,hash: a list of algorithms tried in order, using whichever the query supports.

Algorithm	Memory	Speed	Notes
hash, parallel_hash	highest (whole right table)	fastest	the default; parallel_hash builds the table with several threads
grace_hash	bounded by splitting into buckets that spill to disk	medium	tune with grace_hash_join_initial_buckets
partial_merge	low: sorts and merges right-side blocks	slower	spills to disk, the classic “low memory” choice
full_sorting_merge	low	fast if both sides are already sorted by the key	sorts both sides; uses the primary key order when possible
direct	tiny	very fast	only for key-value engines and dictionaries as the right side
auto	adaptive		starts as a hash join and switches to a merge-based one when max_bytes_in_join or max_rows_in_join is exceeded

With two 3-million-row tables (a users table with an e-mail string column and an events table) and a memory limit of 150 MB, the same query was tried with each algorithm:

SELECT count() FROM events AS e INNER JOIN users AS u ON e.user_id = u.user_id
SETTINGS max_memory_usage = 150000000, join_algorithm = '<algorithm>';

join_algorithm	Outcome
hash, parallel_hash, direct	MEMORY_LIMIT_EXCEEDED
auto (with only max_memory_usage set)	MEMORY_LIMIT_EXCEEDED
grace_hash (default 1 initial bucket, and also 16)	MEMORY_LIMIT_EXCEEDED
partial_merge	3000000
full_sorting_merge	3000000

So at this size the merge-based algorithms are the ones that finish within the limit. The exact threshold depends on your row width, but the shape holds: hash algorithms need the whole right table in RAM, merge algorithms trade speed for a bounded footprint. grace_hash also failed here; it is meant for tables that are only somewhat too large, and needs enough buckets for each one to fit. Test the options with your data under the limit you actually run with.

auto reacts to the join-specific caps, not to max_memory_usage. With max_bytes_in_join = 50000000 and a roomier memory limit, join_algorithm = 'auto' finished the same join (3000000), while 'hash' stopped with SET_SIZE_LIMIT_EXCEEDED.

Switching algorithm is a per-query setting:

SELECT ...
FROM big AS l INNER JOIN also_big AS r ON l.key = r.key
SETTINGS join_algorithm = 'partial_merge';

The old boolean partial_merge_join setting no longer exists; use join_algorithm.

Limits as guard rails

max_rows_in_join and max_bytes_in_join cap the right-hand table, and join_overflow_mode decides what happens at the cap (throw, the default, or break, which truncates the right side and returns a wrong answer, so avoid it for real results). The query-level max_memory_usage still applies on top. For the GROUP BY that often follows a join, max_bytes_before_external_group_by lets aggregation spill to disk.

Rewrite instead of tuning

Before turning knobs, ask whether the join is needed at all:

• Pre-join once into a table (a refreshable materialized view fits).
• Filter both sides by the same time range or key range so that each query joins a slice.
• Swap a join for IN, SEMI, a dictionary or a GROUP BY with argMax/any that carries the extra column through.
• Join aggregated results rather than raw rows.

Cheat sheet

1. Small table on the right, always.
2. List columns; never SELECT * over a join.
3. Use ANY when the right side should be unique, SEMI/ANTI for existence.
4. Remember join_use_nulls if you need real NULLs from outer joins.
5. Out of memory: filter the right side, then try join_algorithm = 'partial_merge' or 'full_sorting_merge', and compare timings.

Reference: JOIN clause in the docs.