JOIN clause reference. For prescriptive optimization guidance and worked examples, see Minimize and optimize JOINs.
Choosing a JOIN algorithm
ClickHouse supports a number of join algorithms. These algorithms typically trade memory usage for performance. The following provides an overview of the ClickHouse join algorithms based on their relative memory consumption and execution time:These algorithms dictate the manner in which a join query is planned and executed. By default, ClickHouse uses the direct or the hash join algorithm based on the used join type and strictness and engine of the joined tables. Alternatively, ClickHouse can be configured to adaptively choose and dynamically change the join algorithm to use at runtime, depending on resource availability and usage: When
join_algorithm=auto, ClickHouse tries the hash join algorithm first, and if that algorithm’s memory limit is violated, the algorithm is switched on the fly to partial merge join. You can observe which algorithm was chosen via trace logging. ClickHouse also allows you to specify the desired join algorithm themselves via the join_algorithm setting.
The supported JOIN types for each join algorithm are shown below and should be considered prior to optimization:
A full detailed description of each
JOIN algorithm can be found here, including their pros, cons, and scaling properties.
Selecting the appropriate join algorithms depends on whether you’re looking to optimize for memory or performance.