A ClickHouse JOIN loads the right-hand table into memory by default

The default join algorithm builds a hash table from the right side and streams the left side through it, so the right table must fit in memory on the node running the query. Exceeding max_bytes_in_join or the query memory limit raises a memory limit exceeded exception rather than spilling.

The practical rule is to put the smaller table on the right, which is the opposite of what many SQL engines arrange automatically, because ClickHouse does not reorder joins for you. Setting join_algorithm to a list including partial_merge or grace_hash lets the server fall back to a disk-capable algorithm for large right sides, and auto picks among them. For a small dimension table, a dictionary queried with dictGet is usually faster than any join, since it keeps the lookup structure in memory permanently and avoids rebuilding it for every query.