Skip to main content
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. 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.
  • 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.
This setting applies to Join engine tables and hash-based join algorithms.If a join is built in parallel, the order of rows can be non-deterministic. This means that join_any_take_last_row = 1 can return a non-deterministic row for ANY JOIN queries.
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_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: 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_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.
Last modified on July 23, 2026