ClickHouse #5: JOIN Algorithms -A Practical Guide
As we know from standard SQL, data is not always stored in a single table. Sometimes the data we need is stored in different tables.In such cases, we can use JOIN to combine related data from different tables.ClickHouse supports the standard JOIN types defined in ANSI SQL.Also there are additional join types as semi, anti, any,asof join.
SEMI JOIN returns rows from the left table that have a matching row in the right table, without returning columns from the right table.
ANTI JOIN does the opposite: it returns rows from the left table that do not have a matching row in the right table.
ANY JOIN returns only one matching row when multiple matches exist.
Get Fidan Alizada’s stories in your inbox
Join Medium for free to get updates from this writer.
ASOF JOIN is designed for joining data based on the closest matching value, which makes it especially useful for time-series data.
That’s why, in this article, instead of focusing on JOIN syntax, we will focus on JOIN algorithms and performance.When we join two tables, ClickHouse needs to find matching rows between them. The way ClickHouse performs this operation depends on the JOIN algorithm.In Clickhouse there 6 different join alghorithms.ClickHouse can use different JOIN algorithms depending on the query and data characteristics. The choice of algorithm can have a significant impact on memory usage and query performance. ClickHouse can automatically choose an algorithm when join_alghortihm is set to auto, or we can explicitly specify one when needed.Now in order to compare different alghoritms let us create two tables.
CREATE TABLE customers
(
customer_id UInt32,
customer_name String,
country LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY customer_id;
INSERT INTO customers
SELECT
number AS customer_id,
concat('Customer_', toString(number)) AS customer_name,
arrayElement(
['Azerbaijan', 'Turkey', 'Georgia', 'Germany', 'USA'],
(number % 5) + 1
) AS country
FROM numbers(1000000);
CREATE TABLE orders
(
order_id UInt64,
customer_id UInt32,
order_date Date,
amount Decimal(10, 2)
)
ENGINE = MergeTree
ORDER BY order_id;
INSERT INTO orders
SELECT
number AS order_id,
number % 1000000 + 1 AS customer_id,
toDate('2026-01-01') + (number % 30) AS order_date,
round(randUniform(10, 1000), 2) AS amount
FROM numbers(10000000);Firstly, we will review hash algorithm.
SET join_algorithm = 'hash';
SELECT
o.order_id,
o.customer_id,
c.customer_name,
c.country,
o.amount
FROM orders AS o
INNER JOIN customers AS c
ON o.customer_id = c.customer_id;In general,three of the ClickHouse join algorithms are based on in-memory hash tables.
1.Hash JOIN is a fast and general-purpose JOIN algorithm. It supports different JOIN types and strictness settings. However, it can consume a significant amount of memory because it builds an in-memory hash table from the right-hand side table.Another limitation is that building the hash table is single-threaded. If the right-hand side table is very large, building the hash table can become a bottleneck and increase the overall query execution time.In my example, I first used the smaller customerstable on the right side of the JOIN. When I switched the tables, the query became much more resource-intensive and eventually reached the time limit. This shows why the size of the right-hand side table can be important when using Hash JOIN.
2.Parallel Hash JOIN is similar to Hash JOIN, but it builds several hash tables in parallel instead of building a single hash table.This can make the JOIN faster, especially when the right-hand side table is large. However, because multiple hash tables are created, Parallel Hash JOIN requires more memory than the regular Hash JOIN.
In my experiment, the same JOIN query reached the time limit when using Hash JOIN. When I switched to Parallel Hash JOIN, the query completed successfully. This shows how parallel hash table construction can improve JOIN performance, especially when working with large tables.
3.Grace Hash JOIN is useful when a regular Hash JOIN cannot efficiently handle the data in memory. It partitions the data into smaller buckets and processes these partitions separately, reducing memory pressure.The main advantage of Grace Hash JOIN is lower memory usage, while its main disadvantage is additional disk I/O and lower performance compared with in-memory hash joins.
4.Full Sorting Merge JOIN uses a different approach compared with hashh join.Instead of building a hash table ClickHouse sorts both tables by the key and then merges the sorted data to find matching rows.For example, in our query the join key is customer_id. ClickHouse sorts the data by customer_id and then compares the sorted values from both tables.If the data is already sorted by the join key, ClickHouse may be able to skip the sorting step. This can make Full Sorting Merge JOIN more efficient.
Partial Merge JOIN is mainly used when we want to reduce memory usage during a join operatoon.Firstly ClickHouse sorts the right-hand side table and stores the sorted data on disk. Then, it sorts the left-hand side table block by block in memory and joins the data.This approach uses less memory, but it can be slower because sorting and disk operations take extra time.
As we can see, there is no single join algorithm that is always the best choice. The right algorithm depends on the table size, data sorting, available memory, and query requirements.In this article, I looked at the main join types in ClickHouse and focused on the different join algorithms and their performance. I also tested algorithms using my example tables to better understand how they work in practice.
