> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# join_* session settings

> ClickHouse session settings in the join_* generated group.

export const SettingsInfoBlock = ({type, default_value, changeable_without_restart}) => {
  return <div className="not-prose" style={{
    display: "flex",
    flexWrap: "wrap",
    alignItems: "baseline",
    columnGap: "0.5rem",
    rowGap: "0.125rem",
    margin: "0.375rem 0",
    fontSize: "0.8125rem",
    lineHeight: "1.125rem"
  }}>
      <div style={{
    fontWeight: 600,
    opacity: 0.72
  }}>Type</div>
      <div style={{
    overflowWrap: "anywhere"
  }}>{type}</div>
      <div style={{
    fontWeight: 600,
    opacity: 0.72,
    marginInlineStart: "0.5rem"
  }}>Default</div>
      <div style={{
    overflowWrap: "anywhere"
  }}>{default_value}</div>
      {changeable_without_restart && <div style={{
    fontWeight: 600,
    opacity: 0.72,
    marginInlineStart: "0.5rem"
  }}>
          Changeable without restart
        </div>}
      {changeable_without_restart && <div style={{
    overflowWrap: "anywhere"
  }}>
          {changeable_without_restart}
        </div>}
    </div>;
};

These settings are available in [system.settings](/docs/reference/system-tables/settings) and are autogenerated from [source](https://github.com/ClickHouse/ClickHouse/blob/master/src/Core/Settings.cpp).

<h2 id="join_algorithm">
  join\_algorithm
</h2>

<SettingsInfoBlock type="JoinAlgorithm" default_value="direct,parallel_hash,hash" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "24.12"},{"label": "direct,parallel_hash,hash"},{"label": "'default' was deprecated in favor of explicitly specified join algorithms, also parallel_hash is now preferred over hash"}]}]} />

Specifies which [JOIN](/docs/reference/statements/select/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](https://en.wikipedia.org/wiki/Hash_join#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`](/docs/reference/settings/session-settings/max-bytes#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](https://en.wikipedia.org/wiki/Hash_join) 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](https://en.wikipedia.org/wiki/Sort-merge_join), 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](/docs/reference/engines/table-engines/special/dictionary), [EmbeddedRocksDB](/docs/reference/engines/table-engines/integrations/embedded-rocksdb), and [MergeTree](/docs/reference/engines/table-engines/mergetree-family/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](https://en.wikipedia.org/wiki/Sort-merge_join) 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).

<h2 id="join_any_take_last_row">
  join\_any\_take\_last\_row
</h2>

<SettingsInfoBlock type="Bool" default_value="0" />

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

<Note>
  This setting applies to [`Join`](/docs/reference/engines/table-engines/special/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.
</Note>

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](/docs/reference/statements/select/join)
* [Join table engine](/docs/reference/engines/table-engines/special/join)
* [join\_default\_strictness](/docs/reference/settings/session-settings/join#join_default_strictness)

<h2 id="join_default_strictness">
  join\_default\_strictness
</h2>

<SettingsInfoBlock type="JoinStrictness" default_value="ALL" />

Sets default strictness for [JOIN clauses](/docs/reference/statements/select/join).

Possible values:

* `ALL` — If the right table has several matching rows, ClickHouse creates a [Cartesian product](https://en.wikipedia.org/wiki/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.

<h2 id="join_on_disk_max_files_to_merge">
  join\_on\_disk\_max\_files\_to\_merge
</h2>

<SettingsInfoBlock type="UInt64" default_value="64" />

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.

<h2 id="join_output_by_rowlist_perkey_rows_threshold">
  join\_output\_by\_rowlist\_perkey\_rows\_threshold
</h2>

<SettingsInfoBlock type="UInt64" default_value="5" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "24.9"},{"label": "5"},{"label": "The lower limit of per-key average rows in the right table to determine whether to output by row list in hash join."}]}]} />

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

<h2 id="join_overflow_mode">
  join\_overflow\_mode
</h2>

<SettingsInfoBlock type="OverflowMode" default_value="throw" />

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

* [max\_bytes\_in\_join](/docs/reference/settings/session-settings/max-bytes#max_bytes_in_join)
* [max\_rows\_in\_join](/docs/reference/settings/session-settings/max-rows#max_rows_in_join)

This setting is honored only by the `hash` and `parallel_hash`
[`join_algorithm`](/docs/reference/settings/session-settings/join#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`](/docs/reference/settings/session-settings/join#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](/docs/reference/statements/select/join)
* [Join table engine](/docs/reference/engines/table-engines/special/join)

<h2 id="join_use_nulls">
  join\_use\_nulls
</h2>

<SettingsInfoBlock type="Bool" default_value="0" />

Sets the type of [JOIN](/docs/reference/statements/select/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](/docs/reference/data-types/nullable), and empty cells are filled with [NULL](/docs/reference/syntax).