JOIN Settings - ClickHouse Documentation

This page lists the session settings that affect the semantics, planning, execution, and memory use of JOINs. Each setting links to its entry in the session settings reference, which lists its default value and accepted values. The syntax and semantics of JOINs are described in the JOIN clause reference.

Semantics and syntax

Setting	Description
`join_default_strictness`	Sets the strictness of a JOIN that is written without an explicit strictness.
`any_join_distinct_right_table_keys`	Enables the legacy behavior of `ANY INNER JOIN` and `ANY LEFT JOIN`, in which `t1 ANY LEFT JOIN t2` and `t2 ANY RIGHT JOIN t1` can return different results.
`join_any_take_last_row`	Selects whether a JOIN with `ANY` strictness takes the first or the last matching row when the right table has several matching rows for a key.
`join_use_nulls`	Controls whether the columns of rows without a match in an outer JOIN are filled with default values or with `NULL`.
`semi_join_include_columns_from_both_sides`	Controls whether the result of a `SEMI JOIN` exposes the columns of both tables or only the columns of the preserved table.
`anti_join_include_columns_from_both_sides`	Controls whether the result of an `ANTI JOIN` exposes the columns of both tables or only the columns of the preserved table.
`cross_to_inner_join_rewrite`	Controls whether a comma-separated list of tables or a `CROSS JOIN` with equality conditions in `WHERE` is rewritten as an `INNER JOIN`.
`allow_block_nested_loop_join`	Allows executing a JOIN whose `ON` section contains no equality between the tables as a block nested loop JOIN.
`enable_lateral_join`	Enables the experimental `LATERAL JOIN` syntax, in which a subquery on the right side of a JOIN can reference columns of the left side.
`single_join_prefer_left_table`	Resolves an identifier that is ambiguous between the two tables of a single JOIN to the left table.
`joined_subquery_requires_alias`	Requires subqueries and table functions in a JOIN to have aliases.
`allow_dynamic_type_in_join_keys`	Allows JOIN keys of the `Dynamic` type.
`analyzer_compatibility_join_using_top_level_identifier`	Resolves a column name in `USING` from an alias of the same name in the query, such as an alias in the `SELECT` list.
`analyzer_compatibility_multiple_joins_qualify_column_names`	Qualifies the names of result columns with table names or aliases in a query with two or more JOINs, as the old analyzer did.
`analyzer_compatibility_apply_final_to_all_joined_tables`	Applies a `FINAL` modifier on the leftmost table of a JOIN to all joined tables, as in versions before 26.6.

Algorithm selection and tuning

Setting	Description
`join_algorithm`	Lists the JOIN algorithms that ClickHouse can use, in order of priority.
`parallel_hash_join_threshold`	Sets the estimated number of rows in the right table at which a hash JOIN splits its hash table into parts that several threads fill concurrently.
`hash_join_layout`	Forces a hash JOIN to build either one hash table on one thread or a split hash table on several threads, for testing and debugging.
`max_threads`	Sets the maximum number of query processing threads, which also limits the number of threads that fill a split hash table.
`grace_hash_join_initial_buckets`	Sets the initial number of buckets of a `grace_hash` JOIN.
`grace_hash_join_max_buckets`	Limits the number of buckets of a `grace_hash` JOIN.
`partial_merge_join_rows_in_right_blocks`	Limits the number of rows in each sorted block of the right table in a `partial_merge` JOIN.
`partial_merge_join_left_table_buffer_bytes`	Sets the size up to which a `partial_merge` JOIN groups blocks of the left table into larger blocks.
`join_on_disk_max_files_to_merge`	Limits the number of files that are sorted in parallel when a merge JOIN runs on disk.
`max_rows_in_set_to_optimize_join`	Sets the maximum size of the key set that is used to filter each input of a JOIN by the keys of the other input before joining.
`query_plan_join_shard_by_pk_ranges`	Executes a JOIN of two `MergeTree` tables as independent JOINs over primary key ranges when the JOIN keys contain a prefix of the primary key of both tables.
`join_output_by_rowlist_perkey_rows_threshold`	Sets the average number of rows per key in the right table at which a hash JOIN outputs matches by row list.
`allow_join_right_table_sorting`	Allows reordering the right table by key in a `LEFT` or `INNER` hash JOIN when the conditions of `join_to_sort_minimum_perkey_rows` and `join_to_sort_maximum_table_rows` are met.
`join_to_sort_minimum_perkey_rows`	Sets the minimum average number of rows per key in the right table for reordering the right table by key.
`join_to_sort_maximum_table_rows`	Sets the maximum number of rows in the right table for reordering the right table by key.
`enable_hash_join_row_store`	Enables converting the payload of a hash JOIN to a row-major layout.
`min_rows_ratio_for_hash_join_row_store`	Sets the minimum estimated ratio of output rows to build-side rows at which the payload of a hash JOIN is converted to a row-major layout.
`enable_join_fixed_hash_table_conversion`	Enables converting the hash table of a JOIN to a flat array when the key is a single integer with a small range of values.
`enable_join_key_only_hash_tables`	Enables hash tables that store only the JOIN keys, for `LEFT ANTI` JOINs and for `LEFT SEMI` JOINs that select no column of the right table.
`enable_software_prefetch_in_join`	Enables software prefetching in the probe phase of a hash JOIN.
`collect_hash_table_stats_during_joins`	Enables collecting hash table statistics that later runs of the same JOIN use to size their hash tables.
`max_size_to_preallocate_for_joins`	Limits the total number of elements for which space is preallocated in the hash tables of a JOIN.
`max_joined_block_size_rows`	Limits the number of rows in a block of the JOIN result.
`max_joined_block_size_bytes`	Limits the size in bytes of a block of the JOIN result.
`min_joined_block_size_rows`	Sets the number of rows below which input and output blocks of a JOIN are squashed into larger blocks.
`min_joined_block_size_bytes`	Sets the size in bytes below which input and output blocks of a JOIN are squashed into larger blocks.
`joined_block_split_single_row`	Allows splitting the hash JOIN result for one row of the left table with many matches into several blocks.
`parallel_non_joined_rows_processing`	Allows several threads to output the rows of the right table without a match in a `RIGHT` or `FULL` JOIN.
`enable_lazy_columns_replication`	Enables lazy replication of columns in the result of a JOIN, which avoids copying the same rows several times in memory.

Query planner

Setting	Description
`query_plan_join_swap_table`	Controls which input of a JOIN the planner uses as the build side.
`query_plan_optimize_join_order_limit`	Sets the maximum number of tables in one subquery for which the planner optimizes the JOIN order.
`query_plan_optimize_join_order_algorithm`	Lists the JOIN order algorithms that the planner tries, in order.
`query_plan_optimize_join_order_conflict_detector`	Selects the conflict detector that the `dpsub` JOIN order algorithm uses to decide which reorderings are valid.
`query_plan_optimize_join_order_max_searched_plans`	Limits the number of partial plans that the JOIN order optimizer enumerates before it falls back to the next algorithm.
`use_statistics`	Allows the optimizer to use column statistics, including for the estimates that JOIN reordering and build-side selection use.
`allow_statistics`	Allows defining columns with statistics and manipulating statistics.
`materialize_statistics_on_insert`	Controls whether column statistics are built for newly inserted parts.
`use_hash_table_stats_for_join_reordering`	Enables using collected hash table statistics for cardinality estimation during JOIN reordering.
`use_iceberg_manifest_statistics`	Enables estimating an Iceberg read for JOIN reordering from the statistics in its manifest files.
`allow_general_join_planning`	Allows a more general JOIN planning algorithm that handles more complex conditions and applies only to hash JOINs.
`query_plan_filter_push_down`	Enables moving filters down in the query plan, including below a JOIN.
`query_plan_convert_outer_join_to_inner_join`	Allows converting an `OUTER JOIN` to an `INNER JOIN` when a filter after the JOIN always removes the default values of rows without a match.
`query_plan_convert_outer_join_to_inner_join_transitively`	Extends `query_plan_convert_outer_join_to_inner_join` to filters further up the plan and to conditions of an enclosing JOIN.
`query_plan_convert_any_join_to_semi_or_anti_join`	Allows converting an `ANY JOIN` to a `SEMI JOIN` or an `ANTI JOIN` when a filter after the JOIN always rejects either the rows without a match or the rows with a match.
`query_plan_convert_join_to_in`	Allows converting a JOIN to a subquery with `IN` when the output columns come only from the left table.
`rewrite_in_to_join`	Rewrites expressions of the form `x IN subquery` as JOINs.
`query_plan_merge_filter_into_join_condition`	Allows merging a filter into the JOIN condition and converting a `CROSS JOIN` to an `INNER JOIN`.
`query_plan_merge_expression_into_join`	Allows merging expressions into the JOIN step during JOIN reordering.
`query_plan_short_circuit_constant_false_join`	Replaces each input of a JOIN that cannot contribute rows with an empty source when the `ON` condition folds to a constant false.
`query_plan_read_in_order_through_join`	Keeps reading the left table in order through a JOIN, so that later steps can use the order.
`query_plan_top_k_through_join`	Pushes `ORDER BY ... LIMIT n` below a `LEFT` or `RIGHT` JOIN when the sort key references only columns of the preserved side.
`query_plan_propagate_predicate_across_join`	Copies filter conditions from one side of an equality JOIN to the other side through the JOIN keys.
`enable_join_transitive_predicates`	Infers transitive equality conditions from the existing JOIN conditions for the JOIN order optimizer.
`use_join_disjunctions_push_down`	Enables pushing `OR`-connected parts of JOIN conditions down to the corresponding inputs.
`query_plan_lazy_materialization_for_join`	Enables lazy materialization for `LIMIT n` queries over JOINs, so that columns are read only for the rows that remain after `LIMIT`.
`query_plan_max_limit_for_join_lazy_indexing`	Sets the maximum `LIMIT` value for which the lazy indexing optimization of a JOIN is used.
`query_plan_min_columns_for_join_lazy_indexing`	Sets the minimum number of payload columns from the left side for which the lazy indexing optimization of a JOIN is used.
`enable_join_runtime_filters`	Enables filtering the probe side of a JOIN by the set of JOIN keys collected from the build side at runtime.
`enable_join_runtime_filters_index_analysis`	Enables pruning granules on the probe side of a JOIN with the runtime filter.
`enable_join_seal_gated_reading`	Delays reading the probe side of a hash JOIN until the runtime filter of the build side is complete.
`join_runtime_filter_exact_values_limit`	Sets the maximum number of values that a JOIN runtime filter stores as a set before it switches to a bloom filter.
`join_runtime_bloom_filter_bytes`	Sets the size in bytes of a bloom filter used as a JOIN runtime filter.
`join_runtime_bloom_filter_hash_functions`	Sets the number of hash functions of a bloom filter used as a JOIN runtime filter.
`join_runtime_bloom_filter_max_ratio_of_set_bits`	Sets the ratio of set bits above which a runtime bloom filter is disabled.
`join_runtime_filter_from_fixed_hash_table`	Uses a hash table that was converted to a flat array directly as the JOIN runtime filter.
`join_runtime_filter_size_from_hash_table_stats`	Sizes the JOIN runtime filter from hash table statistics collected in previous executions.
`join_runtime_filter_min_probe_rows`	Sets the estimated number of probe-side rows at or below which no JOIN runtime filter is created.
`join_runtime_filter_pass_ratio_threshold_for_disabling`	Sets the ratio of passed rows to checked rows above which a JOIN runtime filter is temporarily disabled.
`join_runtime_filter_blocks_to_skip_before_reenabling`	Sets the number of blocks after which a temporarily disabled JOIN runtime filter is tried again.
`correlated_subqueries_default_join_kind`	Controls whether the decorrelated plan of a correlated subquery, which includes a `LATERAL JOIN`, uses `LEFT` or `RIGHT` JOINs.
`correlated_subqueries_substitute_equivalent_expressions`	Allows substituting equivalent expressions derived from filters instead of creating a `CROSS JOIN` when a correlated subquery is decorrelated.
`correlated_subqueries_use_in_memory_buffer`	Uses an in-memory buffer for the input of a correlated subquery, which includes a `LATERAL JOIN`, to avoid evaluating the input repeatedly.

Memory limits and spilling

Setting	Description
`max_rows_in_join`	Limits the number of rows in the data structure of the right table of a JOIN.
`max_bytes_in_join`	Limits the size in bytes of the data structure of the right table of a JOIN.
`join_overflow_mode`	Defines the action when a JOIN reaches `max_rows_in_join` or `max_bytes_in_join`.
`default_max_bytes_in_join`	Sets the maximum size of the right table when a limit is required and `max_bytes_in_join` is not set.
`max_bytes_before_external_join`	Sets the size in bytes of the right table at which a hash-based JOIN spills to disk.
`max_bytes_ratio_before_external_join`	Sets the spill threshold of a hash-based JOIN as a ratio of the available memory.
`enable_adaptive_memory_spill_scheduler`	Enables spilling eligible processors, including JOINs that can spill, to disk under query memory pressure.
`legacy_join_size_limits_trigger_spilling`	Restores the behavior in which `max_rows_in_join` and `max_bytes_in_join` make the on-disk part of a JOIN spill further instead of stopping the query.
`max_bytes_before_external_sort`	Sets the memory usage at which sorting, including the sort of a `full_sorting_merge` JOIN, spills to disk.
`max_bytes_ratio_before_external_sort`	Sets the ratio of the available memory at which sorting, including the sort of a `full_sorting_merge` JOIN, spills to disk.
`cross_join_min_rows_to_compress`	Sets the number of rows at which a block of a `CROSS JOIN` or of a block nested loop JOIN is compressed in memory.
`cross_join_min_bytes_to_compress`	Sets the size in bytes at which a block of a `CROSS JOIN` or of a block nested loop JOIN is compressed in memory.
`temporary_files_codec`	Sets the compression codec of the temporary files that sorting and JOIN operations write to disk.

Distributed JOINs

Setting	Description
`distributed_product_mode`	Controls how ClickHouse executes `IN` and JOIN subqueries over distributed tables inside a query over a distributed table.
`prefer_global_in_and_join`	Enables replacing `IN` and JOIN with `GLOBAL IN` and `GLOBAL JOIN`.
`max_rows_to_transfer`	Limits the number of rows that a `GLOBAL IN` or `GLOBAL JOIN` passes to remote servers or saves in a temporary table.
`max_bytes_to_transfer`	Limits the number of uncompressed bytes that a `GLOBAL IN` or `GLOBAL JOIN` passes to remote servers or saves in a temporary table.
`transfer_overflow_mode`	Defines the action when `max_rows_to_transfer` or `max_bytes_to_transfer` is reached.
`parallel_replicas_for_queries_with_multiple_tables`	Allows parallel replicas for queries with JOINs.
`parallel_replicas_prefer_local_join`	Uses a local JOIN instead of a `GLOBAL JOIN` with parallel replicas when each replica can read the materialized side of the JOIN by itself.
`distributed_plan_default_shuffle_join_bucket_count`	Sets the default number of buckets of a shuffle hash JOIN in the rule-based distributed planner.
`distributed_plan_join_runtime_filters`	Sends JOIN runtime filters to the stages that read the probe side in a distributed query plan.
`distributed_plan_max_rows_to_broadcast`	Sets the maximum number of rows for which the rule-based distributed planner uses a broadcast JOIN instead of a shuffle JOIN.