ClickHouse JOINs

ClickHouse supports the full range of SQL join types, plus modifiers and algorithms that other engines do not have. It is also an OLAP engine, which means joins are not its first instinct: denormalizing or using a dictionary is often faster. This guide covers the join types, the strictness modifiers, how to pick an algorithm, the distributed-table trap that GLOBAL JOIN solves, and when to skip joins entirely.

Sample Schema

CREATE TABLE orders
(
    order_id   UInt64,
    user_id    UInt32,
    amount     Decimal(10, 2),
    ts         DateTime
)
ENGINE = MergeTree ORDER BY ts;
 
CREATE TABLE users
(
    user_id  UInt32,
    name     String,
    country  String
)
ENGINE = MergeTree ORDER BY user_id;

Join Types

The syntax mirrors standard SQL with extra keywords:

SELECT [GLOBAL] [INNER|LEFT|RIGHT|FULL|CROSS]
       [OUTER|SEMI|ANTI|ANY|ALL|ASOF] JOIN ...

• INNER, LEFT, RIGHT, FULL, CROSS behave as you expect.
• SEMI returns rows from one side that have a match, without duplicating for multiple matches. LEFT SEMI JOIN is the efficient form of WHERE user_id IN (SELECT ...).
• ANTI returns rows that have no match. LEFT ANTI JOIN is the "find orphans" query.
• ANY returns at most one matching row from the right side per left row, instead of the Cartesian product. Use it for lookups where you only need a single match.
• ALL (the default for most types) returns every matching combination.
• ASOF joins on the closest preceding value rather than an exact match, which is built for time-series alignment.

A basic inner join:

SELECT o.order_id, o.amount, u.name, u.country
FROM orders AS o
INNER JOIN users AS u ON o.user_id = u.user_id;

ASOF JOIN

ASOF JOIN matches each left row to the right row whose key is the nearest one that is less than or equal to it. The inequality column comes last in the ON clause:

SELECT q.symbol, q.ts, q.price, t.ts AS trade_ts
FROM quotes AS q
ASOF LEFT JOIN trades AS t
  ON q.symbol = t.symbol AND q.ts >= t.ts;

This answers "what was the most recent trade at or before each quote" in one pass, which would otherwise need a correlated subquery or window function.

Join Algorithms

By default ClickHouse uses the direct or hash join algorithm depending on the join type, strictness, and the engine of the right-hand table. You can change the strategy with the join_algorithm setting:

• hash (and parallel_hash): fast, builds an in-memory hash table from the right side. Memory-bound, so it can fail on very large right tables.
• grace_hash: spills to disk in buckets, trading speed for the ability to join sets larger than RAM.
• partial_merge / full_sorting_merge: sort-based, not memory-bound, can exploit existing row order.
• direct: a key-value lookup that works when the right side is a dictionary or a Join/Dictionary-engine table. The fastest option when it applies.
• auto: lets ClickHouse pick and switch at runtime based on available resources.

SELECT o.order_id, u.name
FROM orders AS o
INNER JOIN users AS u ON o.user_id = u.user_id
SETTINGS join_algorithm = 'grace_hash';

A practical rule: always put the smaller table on the right, because that side is loaded into memory. As of v24.12 the query planner reorders this automatically in many cases, but being explicit still helps on older versions and complex queries.

GLOBAL JOIN and Distributed Tables

This is the trap that catches people moving from single-node to a cluster. When you join against a Distributed table, a plain JOIN is executed independently on each shard, and each shard only sees its own local data on the right side. The result is silently incomplete.

GLOBAL JOIN fixes this: the right-hand subquery is evaluated once on the initiating node, and the result is broadcast to every shard before the join runs.

SELECT o.order_id, u.name
FROM distributed_orders AS o
GLOBAL INNER JOIN (
    SELECT user_id, name FROM distributed_users
) AS u ON o.user_id = u.user_id;

The cost is the broadcast: if the right side is large, you ship a lot of data to every node. Keep the right side small, or use a dictionary instead.

When a Dictionary Beats a Join

For small, mostly static lookup tables (countries, product catalogs, user metadata), a dictionary is usually the better tool. Dictionaries are held in memory in a lookup-optimized structure and queried with dictGet, avoiding the join machinery entirely. They also work cleanly across distributed queries because each node loads the same dictionary.

SELECT
    order_id,
    dictGet('users_dict', 'name', user_id)    AS name,
    dictGet('users_dict', 'country', user_id) AS country
FROM orders;

This sidesteps the GLOBAL JOIN broadcast problem and is typically faster for high-frequency lookups. See the dictionaries guide for defining and refreshing them.

Common Mistakes

• Plain JOIN on a distributed table. Without GLOBAL, each shard joins only its local right-side rows, producing undercounted results. Use GLOBAL JOIN or a dictionary.
• Large table on the right. The right side is loaded into memory for hash joins. Swap the order or switch to grace_hash if you hit memory limits.
• Reaching for JOIN by default. ClickHouse rewards denormalization. If you join the same dimension table constantly, consider a dictionary or a flattened table instead.
• Expecting NULL-friendly outer-join semantics everywhere. Unmatched cells are filled with type defaults (0, empty string) unless join_use_nulls = 1 is set, which makes them real NULLs.

When you are testing which join type or algorithm gives the right result and speed, Mako's AI autocomplete can scaffold the ON clause and suggest the SETTINGS line as you write the query.

Related Guides

• ClickHouse Aggregate Functions
• ClickHouse Window Functions
• ClickHouse JSON Queries

Mako connects to ClickHouse with AI-powered autocomplete for queries like these. Try it free at mako.ai.