join_* session settings

These settings are available in system.settings and are autogenerated from source.

​

join_algorithm

Specifies which JOIN algorithm is used. Several algorithms can be specified, and an available one would be chosen for a particular query based on kind/strictness and table engine. Most algorithms affect a query only when they are the one selected for it. Some, however, change planning merely by being listed — even as a lower-priority fallback that is not ultimately selected — because the decision is made before the algorithm is picked. There are two such effects:

• Join-key type inference becomes stricter (a merge join cannot join keys of different types, for example String and Nullable(String)). This can change the result types of USING columns, and can make a join into a Join-engine table fail with TYPE_MISMATCH. Triggered by full_sorting_merge and parallel_full_sorting_merge.
• ORDER BY ... LIMIT on the preserved side of a join gets an explicit sort instead of a read in primary-key order, because the join is assumed to break the ordered read (a merge join inserts its own pre-join sort; a partial merge join re-sorts the left blocks; a join that can produce delayed blocks does not propagate the ordered read either). The result is the same, the plan is less efficient. Triggered by full_sorting_merge, parallel_full_sorting_merge, partial_merge, prefer_partial_merge, grace_hash and auto, and also by a non-zero max_bytes_before_external_join / max_bytes_ratio_before_external_join.

Both apply even when the query eventually runs with hash or another algorithm. If this is undesirable, do not list the algorithms above in join_algorithm for the affected queries. Possible values:

• grace_hash

Grace hash join is used. Grace hash provides an algorithm option that provides performant complex joins while limiting memory use. The first phase of a grace join reads the right table and splits it into N buckets depending on the hash value of key columns (initially, N is grace_hash_join_initial_buckets). This is done in a way to ensure that each bucket can be processed independently. Rows from the first bucket are added to an in-memory hash table while the others are saved to disk. If the hash table grows beyond the memory limit (e.g., as set by max_bytes_in_join, the number of buckets is increased and the assigned bucket for each row. Any rows which don’t belong to the current bucket are flushed and reassigned. Supports INNER/LEFT/RIGHT/FULL ALL/ANY JOIN.

• hash

Hash join algorithm is used. The most generic implementation that supports all combinations of kind and strictness and multiple join keys that are combined with OR in the JOIN ON section. When using the hash algorithm, the right part of JOIN is uploaded into RAM.

• parallel_hash

A variation of hash join that splits the data into buckets and builds several hashtables instead of one concurrently to speed up this process. When using the parallel_hash algorithm, the right part of JOIN is uploaded into RAM.

• partial_merge

A variation of the sort-merge algorithm, where only the right table is fully sorted. The RIGHT JOIN and FULL JOIN are supported only with ALL strictness (SEMI, ANTI, ANY, and ASOF are not supported). When using the partial_merge algorithm, ClickHouse sorts the data and dumps it to the disk. The partial_merge algorithm in ClickHouse differs slightly from the classic realization. First, ClickHouse sorts the right table by joining keys in blocks and creates a min-max index for sorted blocks. Then it sorts parts of the left table by the join key and joins them over the right table. The min-max index is also used to skip unneeded right table blocks.

• direct

The direct (also known as nested loop) algorithm performs a lookup in the right table using rows from the left table as keys. It’s supported by special storages such as Dictionary, EmbeddedRocksDB, and MergeTree tables. For MergeTree tables, the algorithm pushes join key filters directly to the storage layer. This can be more efficient when the key can use the table’s primary key index for lookups, otherwise it performs full scans of the right table for each left table block. Supports INNER and LEFT joins and only single-column equality join keys without other conditions.

• auto

When set to auto, hash join is tried first, and the algorithm is switched on the fly to another algorithm if the memory limit is violated.

• full_sorting_merge

Sort-merge algorithm with full sorting of joined tables before joining.

• parallel_full_sorting_merge

Same as full_sorting_merge, but hash-compatible equality joins are sharded by the hash of the join keys into independent per-shard merge joins that run in parallel (up to max_threads), instead of a single merge join. This keeps the low, streaming memory usage of a merge join while using all threads, and the result is not ordered. Hash-sharding by the join keys is applied only to plain equality joins on key types whose hash agrees with the merge-join comparison, and only when neither side is already sorted. It is skipped in these cases:

• ASOF joins, and floating-point / JSON / Object / Dynamic key types: their hashes are not consistent with the merge-join comparison, so equal keys could land in different shards.
• Sides that are already sorted (a MergeTree read in order, or any pre-sorted input): an order-preserving scatter into the per-shard merges can deadlock the pipeline. The in-order read and its read_in_order_use_virtual_row optimization are kept instead.
• While the initiator builds a distributed plan (make_distributed_plan), because the scattered sort is not serializable for remote execution. The local single-fragment plan and the per-worker fragments re-optimize with that setting disabled, so they can still be sharded.

Skipping it disables only this rewrite, not parallelism in general: the join runs as a single full_sorting_merge, and MergeTree sides read in order can still be sharded at the source by primary-key ranges (which order by the same comparison the join uses, so equal keys stay together) when query_plan_join_shard_by_pk_ranges is enabled.

• prefer_partial_merge

ClickHouse always tries to use partial_merge join if possible, otherwise, it uses hash. Deprecated, same as partial_merge,hash.

• default (deprecated)

Legacy value, please don’t use anymore. Same as direct,hash, i.e. try to use direct join and hash join (in this order).

​

join_any_take_last_row

Changes the behaviour of join operations with ANY strictness when the right table has more than one matching row for a key. Possible values:

• 0 — If the right table has more than one matching row, only the first one found is joined.
• 1 — If the right table has more than one matching row, only the last one found is joined.

See also:

• JOIN clause
• Join table engine
• join_default_strictness

​

join_default_strictness

Sets default strictness for JOIN clauses. Possible values:

• ALL — If the right table has several matching rows, ClickHouse creates a Cartesian product from matching rows. This is the normal JOIN behaviour from standard SQL.
• ANY — If the right table has several matching rows, only the first one found is joined. If the right table has only one matching row, the results of ANY and ALL are the same.
• ASOF — For joining sequences with an uncertain match.
• Empty string — If ALL or ANY is not specified in the query, ClickHouse throws an exception.

​

join_on_disk_max_files_to_merge

Limits the number of files allowed for parallel sorting in MergeJoin operations when they are executed on disk. The bigger the value of the setting, the more RAM is used and the less disk I/O is needed. Possible values:

• Any positive integer, starting from 2.

​

join_output_by_rowlist_perkey_rows_threshold

The lower limit of per-key average rows in the right table to determine whether to output by row list in hash join.

​

join_overflow_mode

Defines what action ClickHouse performs when a join reaches any of the following limits:

• max_bytes_in_join
• max_rows_in_join

This setting is honored only by the hash and parallel_hash join_algorithm values. Other algorithms (for example, partial_merge, grace_hash, auto) handle the limits differently — by spilling to disk, re-partitioning, or switching strategy — see join_algorithm. Possible values:

• THROW — ClickHouse throws an exception and stops the query.
• BREAK — ClickHouse stops the query and does not throw an exception.

Default value: THROW. See Also

• JOIN clause
• Join table engine

​

join_use_nulls

Sets the type of JOIN behaviour. When merging tables, empty cells may appear. ClickHouse fills them differently based on this setting. Possible values:

• 0 — The empty cells are filled with the default value of the corresponding field type.
• 1 — JOIN behaves the same way as in standard SQL. The type of the corresponding field is converted to Nullable, and empty cells are filled with NULL.