JOIN

The JOIN clause produces a new table by combining columns from one or multiple tables by using values common to each. It is a common operation in databases with SQL support, which corresponds to relational algebra join. The special case of one table join is often referred to as a “self-join”. Syntax

SELECT <expr_list>
FROM <left_table>
[GLOBAL] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI|ANY|ALL|ASOF] JOIN <right_table>
(ON <expr_list>)|(USING <column_list>) ...

Expressions from the ON clause and columns from the USING clause are called “join keys”. Unless otherwise stated, a JOIN produces a Cartesian product from rows with matching “join keys”, which might produce results with many more rows than the source tables.

Supported types of JOIN

All standard SQL JOIN types are supported:

Type	Description
`INNER JOIN`	only matching rows are returned.
`LEFT OUTER JOIN`	non-matching rows from left table are returned in addition to matching rows.
`RIGHT OUTER JOIN`	non-matching rows from right table are returned in addition to matching rows.
`FULL OUTER JOIN`	non-matching rows from both tables are returned in addition to matching rows.
`CROSS JOIN`	produces cartesian product of whole tables, “join keys” are not specified.
`NATURAL JOIN`	automatically joins on all columns with the same name in both tables; each common column appears once in the result. Supports `INNER` (default), `LEFT`, `RIGHT`, and `FULL` variants. Equivalent to `JOIN ... USING (col1, col2, ...)` where the column list is derived automatically.

JOIN without a type specified implies INNER.
The keyword OUTER can be safely omitted.
An alternative syntax for CROSS JOIN is specifying multiple tables in the FROM clause separated by commas.
If there are no matching columns for a NATURAL JOIN, it functions like a CROSS JOIN.

Additional join types available in ClickHouse are:

Type	Description
`LEFT SEMI JOIN`, `RIGHT SEMI JOIN`	An allowlist on “join keys”, without producing a cartesian product.
`LEFT ANTI JOIN`, `RIGHT ANTI JOIN`	A denylist on “join keys”, without producing a cartesian product.
`LEFT ANY JOIN`, `RIGHT ANY JOIN`, `INNER ANY JOIN`	Partially (for opposite side of `LEFT` and `RIGHT`) or completely (for `INNER` and `FULL`) disables the cartesian product for standard `JOIN` types.
`ASOF JOIN`, `LEFT ASOF JOIN`	Joining sequences with a non-exact match. `ASOF JOIN` usage is described below.
`PASTE JOIN`	Performs a horizontal concatenation of two tables.

When using the analyzer, disabling the semi_join_include_columns_from_both_sides or anti_join_include_columns_from_both_sides setting makes the corresponding join expose only its preserved side to expressions resolved after the join result is formed.

LEFT SEMI JOIN and LEFT ANTI JOIN expose only left-side columns.
RIGHT SEMI JOIN and RIGHT ANTI JOIN expose only right-side columns.
This affects clauses such as SELECT, PREWHERE, WHERE, GROUP BY, HAVING, QUALIFY, ORDER BY, and LIMIT BY, including qualified wildcards like t1.*.
The ON expression of the same JOIN can still reference both sides.

When these settings are enabled (the default), ClickHouse keeps the legacy behavior, where both sides remain accessible and SELECT * expands columns from both tables.

When join_algorithm is set to partial_merge, RIGHT JOIN and FULL JOIN are supported only with ALL strictness (SEMI, ANTI, ANY, and ASOF are not supported).

LATERAL JOIN

JOIN LATERAL lets the subquery on the right side of a join reference columns of the table expressions on its left side; the subquery is evaluated for each distinct combination of the left-side column values it references, and its result is joined to every left row with that combination:

SELECT ...
FROM <left_table>
[INNER|LEFT] JOIN LATERAL (SELECT ... WHERE <expr referencing left_table>) AS <alias> ON true

The ON true predicate is mandatory, as for any other INNER or LEFT JOIN; omitting it is a syntax error. It is experimental and disabled by default; enable it with the allow_experimental_lateral_join setting. Only the following subset is supported so far; anything else is rejected with an error:

INNER JOIN LATERAL and LEFT JOIN LATERAL only; RIGHT, FULL, PASTE and NATURAL joins are not supported, and LATERAL cannot be combined with a CROSS or comma join at all.
The default ALL strictness only; ANY, SEMI, ANTI and ASOF are not supported.
No join predicate other than ON true (ON 1 is also accepted); USING is not supported, and the predicate cannot be omitted. Put the filters that relate the two sides into the WHERE clause of the lateral subquery.
The GLOBAL and LOCAL join modifiers are not supported.
The lateral subquery must reference at least one column of the left side. Use a regular join for a non-correlated subquery.
The lateral subquery is evaluated once per distinct value of the left-side columns it references, not once per left row, so it must not contain functions that are non-deterministic within a query, such as rand or generateUUIDv4, or table functions that generate random rows, such as generateRandom. Functions that are constant within a query, such as now, are allowed.
Only a subquery is supported as the lateral table expression. The PostgreSQL table-source forms LATERAL unnest(...) and CROSS JOIN UNNEST(...) are not supported - use the ARRAY JOIN clause instead.
The GROUP BY and ORDER BY of the lateral subquery run once over all evaluations together, so the max_rows_to_group_by, max_rows_to_sort and max_bytes_to_sort limits count the rows of all evaluations, not of one. They are only supported with the throw overflow mode; any and break are rejected.
The rows of all evaluations are matched to the left rows by a single join. As for any hash join, max_rows_in_join and max_bytes_in_join limit the side of this join that is kept in memory, and the planner chooses that side: with the default settings (correlated_subqueries_use_in_memory_buffer = 1) it is the left side of JOIN LATERAL, because the left rows must be fully read before the lateral subquery is evaluated; otherwise it can be the results of all evaluations together. The limits are always enforced as if join_overflow_mode were throw: with break, the join would silently drop unrelated left rows.

Example

SELECT u.id, o.total
FROM users AS u
LEFT JOIN LATERAL
(
    SELECT total
    FROM orders
    WHERE orders.user_id = u.id
    ORDER BY total DESC
    LIMIT 1
) AS o ON true
SETTINGS allow_experimental_lateral_join = 1;

Settings

The default join type can be overridden using join_default_strictness setting. The behavior of the ClickHouse server for ANY JOIN operations depends on the any_join_distinct_right_table_keys setting. See also

Use the cross_to_inner_join_rewrite setting to define the behavior when ClickHouse fails to rewrite a CROSS JOIN as an INNER JOIN. The default value is 1, which allows the join to continue but it will be slower. Set cross_to_inner_join_rewrite to 0 if you want an error to be thrown, and set it to 2 to not run the cross joins but instead force a rewrite of all comma/cross joins. If the rewriting fails when the value is 2, you will receive an error message stating “Please, try to simplify WHERE section”.

ON section conditions

An ON section can contain several conditions combined using the AND and OR operators. Conditions specifying join keys must:

reference both left and right tables
use the equality operator

Other conditions may use other operators and may reference either the left or the right table of a query, or both. Rows are joined if the whole complex condition is met. If the conditions are not met, rows may still be included in the result depending on the JOIN type. Note that if the same conditions are placed in a WHERE section and they are not met, then rows are always filtered out from the result. The OR operator inside the ON clause works using the hash join algorithm — for each OR argument with join keys for JOIN, a separate hash table is created, so memory consumption and query execution time grow linearly with an increase in the number of expressions OR of the ON clause.

Only the equality operator (=) makes a condition a join key. Other operators between columns of different tables are supported, but without any equality the join has to examine every pair of rows and is much slower; see JOIN with an arbitrary ON condition.

Example Consider table_1 and table_2:

┌─Id─┬─name─┐     ┌─Id─┬─text───────────┬─scores─┐
│  1 │ A    │     │  1 │ Text A         │     10 │
│  2 │ B    │     │  1 │ Another text A │     12 │
│  3 │ C    │     │  2 │ Text B         │     15 │
└────┴──────┘     └────┴────────────────┴────────┘

Query with one join key condition and an additional condition for table_2:

Query

SELECT name, text FROM table_1 LEFT OUTER JOIN table_2
    ON table_1.Id = table_2.Id AND startsWith(table_2.text, 'Text');

Note that the result contains the row with the name C and the empty text column. It is included into the result because an OUTER type of a join is used.

Response

┌─name─┬─text───┐
│ A    │ Text A │
│ B    │ Text B │
│ C    │        │
└──────┴────────┘

Query with INNER type of a join and multiple conditions:

Query

SELECT name, text, scores FROM table_1 INNER JOIN table_2
    ON table_1.Id = table_2.Id AND table_2.scores > 10 AND startsWith(table_2.text, 'Text');

Response

┌─name─┬─text───┬─scores─┐
│ B    │ Text B │     15 │
└──────┴────────┴────────┘

Query with INNER type of a join and condition with OR:

Query

CREATE TABLE t1 (`a` Int64, `b` Int64) ENGINE = MergeTree() ORDER BY a;

CREATE TABLE t2 (`key` Int32, `val` Int64) ENGINE = MergeTree() ORDER BY key;

INSERT INTO t1 SELECT number as a, -a as b from numbers(5);

INSERT INTO t2 SELECT if(number % 2 == 0, toInt64(number), -number) as key, number as val from numbers(5);

SELECT a, b, val FROM t1 INNER JOIN t2 ON t1.a = t2.key OR t1.b = t2.key;

Response

┌─a─┬──b─┬─val─┐
│ 0 │  0 │   0 │
│ 1 │ -1 │   1 │
│ 2 │ -2 │   2 │
│ 3 │ -3 │   3 │
│ 4 │ -4 │   4 │
└───┴────┴─────┘

Query with INNER type of a join and conditions with OR and AND:

A non-equal condition that uses columns from a single table, such as t1.a = t2.key AND t1.b > 0 AND t2.b > t2.c, is applied to that table alone: t1.b > 0 uses columns only from t1 and t2.b > t2.c uses columns only from t2. A non-equal condition that compares columns of different tables, such as t1.a = t2.key AND t1.b > t2.key, is also supported; check out the sections below for more details.

Query

SELECT a, b, val FROM t1 INNER JOIN t2 ON t1.a = t2.key OR t1.b = t2.key AND t2.val > 3;

Response

┌─a─┬──b─┬─val─┐
│ 0 │  0 │   0 │
│ 2 │ -2 │   2 │
│ 4 │ -4 │   4 │
└───┴────┴─────┘

JOIN with inequality conditions for columns from different tables

ClickHouse supports ALL/ANY/SEMI/ANTI INNER/LEFT/RIGHT/FULL JOIN with inequality conditions in addition to equality conditions. When the ON section contains an equality between the two tables next to the inequality, the equality is the join key and the inequality is checked on the rows it matched. Such a mixed condition is executed only by the hash, parallel_hash and grace_hash join algorithms. When the ON section contains no equality between the two tables, there is no join key to match on. Such a join is executed by ie_join when the condition is a pair of inequalities, or otherwise as a block nested loop join. A condition evaluated during the join may not contain arrayJoin, because it must preserve the number of rows; use ARRAY JOIN in a subquery instead. Example Table t1:

┌─key──┬─attr─┬─a─┬─b─┬─c─┐
│ key1 │ a    │ 1 │ 1 │ 2 │
│ key1 │ b    │ 2 │ 3 │ 2 │
│ key1 │ c    │ 3 │ 2 │ 1 │
│ key1 │ d    │ 4 │ 7 │ 2 │
│ key1 │ e    │ 5 │ 5 │ 5 │
│ key2 │ a2   │ 1 │ 1 │ 1 │
│ key4 │ f    │ 2 │ 3 │ 4 │
└──────┴──────┴───┴───┴───┘

Table t2

┌─key──┬─attr─┬─a─┬─b─┬─c─┐
│ key1 │ A    │ 1 │ 2 │ 1 │
│ key1 │ B    │ 2 │ 1 │ 2 │
│ key1 │ C    │ 3 │ 4 │ 5 │
│ key1 │ D    │ 4 │ 1 │ 6 │
│ key3 │ a3   │ 1 │ 1 │ 1 │
│ key4 │ F    │ 1 │ 1 │ 1 │
└──────┴──────┴───┴───┴───┘

SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.key = t2.key AND (t1.a < t2.a) ORDER BY (t1.key, t1.attr, t2.key, t2.attr);

key1    a    1    1    2    key1    B    2    1    2
key1    a    1    1    2    key1    C    3    4    5
key1    a    1    1    2    key1    D    4    1    6
key1    b    2    3    2    key1    C    3    4    5
key1    b    2    3    2    key1    D    4    1    6
key1    c    3    2    1    key1    D    4    1    6
key1    d    4    7    2            0    0    \N
key1    e    5    5    5            0    0    \N
key2    a2    1    1    1            0    0    \N
key4    f    2    3    4            0    0    \N

JOIN with an arbitrary ON condition

The ON section may be an arbitrary boolean expression over the columns of both tables, such as a range check, an arithmetic comparison, or a call to a scalar function. When it contains no equality between the two tables, there is no join key to match rows on. A pair of inequality conditions is executed by ie_join when that algorithm is listed in join_algorithm, as it is by default. Any other condition is executed as a block nested loop join: the right table is materialized and the condition is evaluated on every pair of rows. An ALL INNER JOIN takes the equivalent form of a CROSS JOIN with the condition as a filter. The block nested loop join supports every join type and strictness except ASOF JOIN, PASTE JOIN and ANY FULL JOIN. It is not one of the join_algorithm values: it is the last resort, used only when no other algorithm can execute the condition. In EXPLAIN output it appears as a BlockNestedLoopJoin step. When allow_block_nested_loop_join is disabled, a query that would need it is rejected with INVALID_JOIN_ON_EXPRESSION. Example

Query

CREATE TABLE orders (id UInt32, amount UInt32) ENGINE = Memory;
CREATE TABLE discounts (min_amount UInt32, max_amount UInt32, pct UInt32) ENGINE = Memory;

INSERT INTO orders VALUES (1, 50), (2, 150), (3, 300);
INSERT INTO discounts VALUES (100, 199, 5), (200, 1000, 10);

SELECT o.id, o.amount, d.pct
FROM orders AS o
LEFT JOIN discounts AS d ON o.amount BETWEEN d.min_amount AND d.max_amount
ORDER BY o.id;

Response

┌─id─┬─amount─┬─pct─┐
│  1 │     50 │   0 │
│  2 │    150 │   5 │
│  3 │    300 │  10 │
└────┴────────┴─────┘

The row that matched nothing is padded according to join_use_nulls, as in any other join: with a default value above, and with NULL when the setting is enabled. Strictness ANY and SEMI normally keep one row per group of rows that share a join key. There is no such group here, so they keep one row per row of the table that drives the join: LEFT ANY and LEFT SEMI emit each left row at most once, RIGHT ANY and RIGHT SEMI each right row at most once. ANY INNER limits both sides at once — each row of either table is used at most once, so the result has at most as many rows as the smaller table. Which pairs make up such a result is arbitrary, exactly as ANY implies, and it may differ between two runs of the same query. For ANY INNER this extends to the number of rows: pairing each row of both sides greedily, in whatever order the rows are examined, may leave a different number of rows unpaired, so the result of count() over an ANY INNER join with no join key is not reproducible and depends on the number of threads and on the physical order of the right table. Performance Every pair of rows is examined, so the work grows with the product of the two tables’ row counts rather than with their sum. A block nested loop join is therefore orders of magnitude more expensive than a hash join on the same data, and the gap widens as the tables grow. If a query can be written with at least one equality in its ON section, write it that way. LEFT ANY, ANY INNER, LEFT SEMI and LEFT ANTI joins stop scanning the right table at a left row’s first matching row, which usually makes them cheaper than the corresponding ALL join. Their right-driven counterparts (RIGHT ANY, RIGHT SEMI, RIGHT ANTI) examine every pair, because the result depends on which right rows matched. join_any_take_last_row has no effect here: with no join key there is no group of matching rows to take the last one of. Memory is bounded by the materialized right table and does not grow with the size of the result. The right table is subject to the same settings as in other join algorithms, listed under Memory limitations: max_rows_in_join, max_bytes_in_join and join_overflow_mode limit it, and max_bytes_before_external_join makes it spill to disk.

NULL and NaN values in JOIN keys

NULL is not equal to any value, including itself. This means that if a JOIN key has a NULL value in one table, it won’t match a NULL value in the other table. Example Table A:

┌───id─┬─name────┐
│    1 │ Alice   │
│    2 │ Bob     │
│ ᴺᵁᴸᴸ │ Charlie │
└──────┴─────────┘

Table B:

┌───id─┬─score─┐
│    1 │    90 │
│    3 │    85 │
│ ᴺᵁᴸᴸ │    88 │
└──────┴───────┘

SELECT A.name, B.score FROM A LEFT JOIN B ON A.id = B.id

┌─name────┬─score─┐
│ Alice   │    90 │
│ Bob     │     0 │
│ Charlie │     0 │
└─────────┴───────┘

Notice that the row with Charlie from table A and the row with score 88 from table B are not in the result because of the NULL value in the JOIN key. In case you want to match NULL values, use the isNotDistinctFrom function to compare the JOIN keys.

SELECT A.name, B.score FROM A LEFT JOIN B ON isNotDistinctFrom(A.id, B.id)

┌─name────┬─score─┐
│ Alice   │    90 │
│ Bob     │     0 │
│ Charlie │    88 │
└─────────┴───────┘

NaN values in float JOIN keys do not follow the NULL rule above. A scalar comparison of two NaN values (NaN = NaN) is 0, however, JOIN keys are not compared using scalar semantics - NaN keys may match. Whether they actually do is an implementation detail and depends on the join algorithm, the key type, and the session settings. Do not rely on a specific behavior. If you require that NaN rows do not match, map them to NULL values: ON if(isNaN(A.id), NULL, A.id) = B.id. In an ASOF JOIN, the closest-match column is compared by ordering, which NaN does not support — filter such rows out on both sides.

ASOF JOIN usage

ASOF JOIN is useful when you need to join records that have no exact match. This JOIN algorithm requires a special column in tables. This column:

Must contain an ordered sequence.
Can be one of the following types: Int, UInt, Float, Date, DateTime, Decimal.
For the hash join algorithm it can’t be the only column in the JOIN clause.

Syntax ASOF JOIN ... ON:

SELECT expressions_list
FROM table_1
ASOF LEFT JOIN table_2
ON equi_cond AND closest_match_cond

You can use any number of equality conditions and exactly one closest match condition. For example, SELECT count() FROM table_1 ASOF LEFT JOIN table_2 ON table_1.a == table_2.b AND table_2.t <= table_1.t. Conditions supported for the closest match: >, >=, <, <=. Syntax ASOF JOIN ... USING:

SELECT expressions_list
FROM table_1
ASOF JOIN table_2
USING (equi_column1, ... equi_columnN, asof_column)

ASOF JOIN uses equi_columnX for joining on equality and asof_column for joining on the closest match with the table_1.asof_column >= table_2.asof_column condition. The asof_column column is always the last one in the USING clause. For example, consider the following tables:

         table_1                           table_2
      event   | ev_time | user_id       event   | ev_time | user_id
    ----------|---------|----------   ----------|---------|----------
                  ...                               ...
    event_1_1 |  12:00  |  42         event_2_1 |  11:59  |   42
                  ...                 event_2_2 |  12:30  |   42
    event_1_2 |  13:00  |  42         event_2_3 |  13:00  |   42
                  ...                               ...

ASOF JOIN can take the timestamp of a user event from table_1 and find an event in table_2 where the timestamp is closest to the timestamp of the event from table_1 corresponding to the closest match condition. Equal timestamp values are the closest if available. Here, the user_id column can be used for joining on equality and the ev_time column can be used for joining on the closest match. In our example, event_1_1 can be joined with event_2_1 and event_1_2 can be joined with event_2_3, but event_2_2 can’t be joined.

ASOF JOIN is supported only by hash and full_sorting_merge join algorithms. It’s not supported in the Join table engine.

PASTE JOIN usage

The result of PASTE JOIN is a table that contains all columns from left subquery followed by all columns from the right subquery. The rows are matched based on their positions in the original tables (the order of rows should be defined). If the subqueries return a different number of rows, extra rows will be cut. Example:

SELECT *
FROM
(
    SELECT number AS a
    FROM numbers(2)
) AS t1
PASTE JOIN
(
    SELECT number AS a
    FROM numbers(2)
    ORDER BY a DESC
) AS t2

┌─a─┬─t2.a─┐
│ 0 │    1 │
│ 1 │    0 │
└───┴──────┘

Note: in this case result can be nondeterministic if the reading is parallel. For example:

SELECT *
FROM
(
    SELECT number AS a
    FROM numbers_mt(5)
) AS t1
PASTE JOIN
(
    SELECT number AS a
    FROM numbers(10)
    ORDER BY a DESC
) AS t2
SETTINGS max_block_size = 2;

┌─a─┬─t2.a─┐
│ 2 │    9 │
│ 3 │    8 │
└───┴──────┘
┌─a─┬─t2.a─┐
│ 0 │    7 │
│ 1 │    6 │
└───┴──────┘
┌─a─┬─t2.a─┐
│ 4 │    5 │
└───┴──────┘

Distributed JOIN

There are two ways to execute a JOIN involving distributed tables:

When using a normal JOIN, the query is sent to remote servers. Subqueries are run on each of them in order to make the right table, and the join is performed with this table. In other words, the right table is formed on each server separately.
When using GLOBAL ... JOIN, first the requestor server runs a subquery to calculate one side of the join and collects the result into a temporary table. This temporary table is then passed to each remote server, and queries are run on them using the temporary data that was transmitted. For LEFT and INNER joins, the right table is calculated as the subquery. For RIGHT joins, the left table is calculated instead, since the right table is the one being preserved and should be read from shards.

Be careful when using GLOBAL. For more information, see the Distributed subqueries section.

Implicit type conversion

INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN queries support the implicit type conversion for “join keys”. However the query can not be executed, if join keys from the left and the right tables cannot be converted to a single type (for example, there is no data type that can hold all values from both UInt64 and Int64, or String and Int32). Example Consider the table t_1:

┌─a─┬─b─┬─toTypeName(a)─┬─toTypeName(b)─┐
│ 1 │ 1 │ UInt16        │ UInt8         │
│ 2 │ 2 │ UInt16        │ UInt8         │
└───┴───┴───────────────┴───────────────┘

and the table t_2:

┌──a─┬────b─┬─toTypeName(a)─┬─toTypeName(b)───┐
│ -1 │    1 │ Int16         │ Nullable(Int64) │
│  1 │   -1 │ Int16         │ Nullable(Int64) │
│  1 │    1 │ Int16         │ Nullable(Int64) │
└────┴──────┴───────────────┴─────────────────┘

The query

SELECT a, b, toTypeName(a), toTypeName(b) FROM t_1 FULL JOIN t_2 USING (a, b);

returns the set:

┌──a─┬────b─┬─toTypeName(a)─┬─toTypeName(b)───┐
│  1 │    1 │ Int32         │ Nullable(Int64) │
│  2 │    2 │ Int32         │ Nullable(Int64) │
│ -1 │    1 │ Int32         │ Nullable(Int64) │
│  1 │   -1 │ Int32         │ Nullable(Int64) │
└────┴──────┴───────────────┴─────────────────┘

Usage recommendations

Processing of empty or NULL cells

While joining tables, the empty cells may appear. The setting join_use_nulls define how ClickHouse fills these cells. If the JOIN keys are Nullable fields, the rows where at least one of the keys has the value NULL are not joined.

Syntax

The columns specified in USING must have the same names in both subqueries, and the other columns must be named differently. You can use aliases to change the names of columns in subqueries. The USING clause specifies one or more columns to join, which establishes the equality of these columns. The list of columns is set without brackets. More complex join conditions are not supported.

Syntax Limitations

For multiple JOIN clauses in a single SELECT query:

Taking all the columns via * is available only if tables are joined, not subqueries.
The PREWHERE clause is not available.
The USING clause is not available.

For ON, WHERE, and GROUP BY clauses:

Arbitrary expressions cannot be used in ON, WHERE, and GROUP BY clauses, but you can define an expression in a SELECT clause and then use it in these clauses via an alias.

Performance

When running a JOIN, there is no optimization of the order of execution in relation to other stages of the query. The join (a search in the right table) is run before filtering in WHERE and before aggregation. Each time a query is run with the same JOIN, the subquery is run again because the result is not cached. To avoid this, use the special Join table engine, which is a prepared array for joining that is always in RAM. In some cases, it is more efficient to use IN instead of JOIN. If you need a JOIN for joining with dimension tables (these are relatively small tables that contain dimension properties, such as names for advertising campaigns), a JOIN might not be very convenient due to the fact that the right table is re-accessed for every query. For such cases, there is a “dictionaries” feature that you should use instead of JOIN. For more information, see the Dictionaries section.

Memory limitations

By default, ClickHouse uses the hash join algorithm. ClickHouse takes the right_table and creates a hash table for it in RAM. If join_algorithm = 'auto' is enabled, then after some threshold of memory consumption, ClickHouse falls back to merge join algorithm. For JOIN algorithms description see the join_algorithm setting. If you need to restrict JOIN operation memory consumption use the following settings:

max_rows_in_join — Limits number of rows in the hash table.
max_bytes_in_join — Limits size of the hash table.

When any of these limits is reached, ClickHouse acts as the join_overflow_mode setting instructs. These two are hard caps and never make a join spill to disk, so setting them at or below the spill threshold normally stops the query before it can spill. Two settings change that: enable_adaptive_memory_spill_scheduler can still spill a join that the threshold below made spill-capable, and legacy_join_size_limits_trigger_spilling turns the two caps back into spill triggers on disk. To let a join keep running by spilling the right side to disk instead of failing, use:

max_bytes_before_external_join — Absolute spill threshold.
max_bytes_ratio_before_external_join — Spill threshold as a ratio of available memory.

These are the threshold-based spill trigger for every hash-based algorithm, including grace_hash; under memory pressure enable_adaptive_memory_spill_scheduler can spill earlier than they ask for — but only once one of them is non-zero, since a join with no threshold at all never spills. The join_algorithm you pick decides how a join spills — grace_hash partitions the right table from the first block, hash and parallel_hash collect it in memory and switch over when the threshold is crossed — not whether these settings apply. The one exception is legacy_join_size_limits_trigger_spilling: with it on, standalone grace_hash ignores both thresholds and spills on the two hard caps instead.

Examples

Example:

SELECT
    CounterID,
    hits,
    visits
FROM
(
    SELECT
        CounterID,
        count() AS hits
    FROM test.hits
    GROUP BY CounterID
) ANY LEFT JOIN
(
    SELECT
        CounterID,
        sum(Sign) AS visits
    FROM test.visits
    GROUP BY CounterID
) USING CounterID
ORDER BY hits DESC
LIMIT 10

┌─CounterID─┬───hits─┬─visits─┐
│   1143050 │ 523264 │  13665 │
│    731962 │ 475698 │ 102716 │
│    722545 │ 337212 │ 108187 │
│    722889 │ 252197 │  10547 │
│   2237260 │ 196036 │   9522 │
│  23057320 │ 147211 │   7689 │
│    722818 │  90109 │  17847 │
│     48221 │  85379 │   4652 │
│  19762435 │  77807 │   7026 │
│    722884 │  77492 │  11056 │
└───────────┴────────┴────────┘

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

Supported types of JOIN

LATERAL JOIN

Settings

ON section conditions

JOIN with inequality conditions for columns from different tables

JOIN with an arbitrary ON condition

NULL and NaN values in JOIN keys

ASOF JOIN usage

PASTE JOIN usage

Distributed JOIN

Implicit type conversion

Usage recommendations

Processing of empty or NULL cells

Syntax

Syntax Limitations

Performance

Memory limitations

Examples

​Supported types of JOIN

​LATERAL JOIN

​Settings

​ON section conditions

​JOIN with inequality conditions for columns from different tables

​JOIN with an arbitrary ON condition

​NULL and NaN values in JOIN keys

​ASOF JOIN usage

​PASTE JOIN usage

​Distributed JOIN

​Implicit type conversion

​Usage recommendations

​Processing of empty or NULL cells

​Syntax

​Syntax Limitations

​Performance

​Memory limitations

​Examples

​Related content

Supported types of JOIN

LATERAL JOIN

Settings

ON section conditions

JOIN with inequality conditions for columns from different tables

JOIN with an arbitrary ON condition

NULL and NaN values in JOIN keys

ASOF JOIN usage

PASTE JOIN usage

Distributed JOIN

Implicit type conversion

Usage recommendations

Processing of empty or NULL cells

Syntax

Syntax Limitations

Performance

Memory limitations

Examples

Related content