> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-parallel-read-in-order-multi-part.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > Documentation for JOIN Clause # 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](https://en.wikipedia.org/wiki/Relational_algebra#Joins_and_join-like_operators) join. The special case of one table join is often referred to as a "self-join". **Syntax** ```sql theme={null} SELECT FROM [GLOBAL] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI|ANY|ALL|ASOF] JOIN (ON )|(USING ) ... ``` Expressions from the `ON` clause and columns from the `USING` clause are called "join keys". Unless otherwise stated, a `JOIN` produces a [Cartesian product](https://en.wikipedia.org/wiki/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](https://en.wikipedia.org/wiki/Join_\(SQL\)) 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](/reference/statements/select/from) 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](/reference/settings/session-settings/join#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: ```sql theme={null} SELECT ... FROM [INNER|LEFT] JOIN LATERAL (SELECT ... WHERE ) AS 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`](/reference/settings/session-settings/allow-experimental#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`](/reference/statements/select/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** ```sql theme={null} 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`](/reference/settings/session-settings/join#join_default_strictness) setting. The behavior of the ClickHouse server for `ANY JOIN` operations depends on the [`any_join_distinct_right_table_keys`](/reference/settings/session-settings/other#any_join_distinct_right_table_keys) setting. **See also** * [`join_algorithm`](/reference/settings/session-settings/join#join_algorithm) * [`join_any_take_last_row`](/reference/settings/session-settings/join#join_any_take_last_row) * [`join_use_nulls`](/reference/settings/session-settings/join#join_use_nulls) * [`partial_merge_join_rows_in_right_blocks`](/reference/settings/session-settings/partial-merge#partial_merge_join_rows_in_right_blocks) * [`join_on_disk_max_files_to_merge`](/reference/settings/session-settings/join#join_on_disk_max_files_to_merge) * [`any_join_distinct_right_table_keys`](/reference/settings/session-settings/other#any_join_distinct_right_table_keys) 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](#join-with-an-arbitrary-on-condition). **Example** Consider `table_1` and `table_2`: ```response theme={null} ┌─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`: ```sql title="Query" theme={null} 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 title="Response" theme={null} ┌─name─┬─text───┐ │ A │ Text A │ │ B │ Text B │ │ C │ │ └──────┴────────┘ ``` Query with `INNER` type of a join and multiple conditions: ```sql title="Query" theme={null} 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'); ``` ```sql title="Response" theme={null} ┌─name─┬─text───┬─scores─┐ │ B │ Text B │ 15 │ └──────┴────────┴────────┘ ``` Query with `INNER` type of a join and condition with `OR`: ```sql title="Query" theme={null} 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 title="Response" theme={null} ┌─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. ```sql title="Query" theme={null} SELECT a, b, val FROM t1 INNER JOIN t2 ON t1.a = t2.key OR t1.b = t2.key AND t2.val > 3; ``` ```response title="Response" theme={null} ┌─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`](/reference/settings/session-settings/join#join_algorithm) when the condition is a pair of inequalities, or otherwise as a [block nested loop join](#join-with-an-arbitrary-on-condition). 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`: ```response theme={null} ┌─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` ```response theme={null} ┌─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 │ └──────┴──────┴───┴───┴───┘ ``` ```sql theme={null} 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); ``` ```response theme={null} 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`](/reference/settings/session-settings/join#join_algorithm), as it is by default. Any other condition is executed as a [block nested loop join](https://en.wikipedia.org/wiki/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`](/reference/settings/session-settings/allow#allow_block_nested_loop_join) is disabled, a query that would need it is rejected with `INVALID_JOIN_ON_EXPRESSION`. **Example** ```sql title="Query" theme={null} 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 title="Response" theme={null} ┌─id─┬─amount─┬─pct─┐ │ 1 │ 50 │ 0 │ │ 2 │ 150 │ 5 │ │ 3 │ 300 │ 10 │ └────┴────────┴─────┘ ``` The row that matched nothing is padded according to [`join_use_nulls`](/reference/settings/session-settings/join#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`](/reference/settings/session-settings/join#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](#memory-limitations): [`max_rows_in_join`](/reference/settings/session-settings/max-rows#max_rows_in_join), [`max_bytes_in_join`](/reference/settings/session-settings/max-bytes#max_bytes_in_join) and [`join_overflow_mode`](/reference/settings/session-settings/join#join_overflow_mode) limit it, and [`max_bytes_before_external_join`](/reference/settings/session-settings/max-bytes#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`: ```response theme={null} ┌───id─┬─name────┐ │ 1 │ Alice │ │ 2 │ Bob │ │ ᴺᵁᴸᴸ │ Charlie │ └──────┴─────────┘ ``` Table `B`: ```response theme={null} ┌───id─┬─score─┐ │ 1 │ 90 │ │ 3 │ 85 │ │ ᴺᵁᴸᴸ │ 88 │ └──────┴───────┘ ``` ```sql theme={null} SELECT A.name, B.score FROM A LEFT JOIN B ON A.id = B.id ``` ```response theme={null} ┌─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. ```sql theme={null} SELECT A.name, B.score FROM A LEFT JOIN B ON isNotDistinctFrom(A.id, B.id) ``` ```markdown theme={null} ┌─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](/reference/data-types/int-uint), [Float](/reference/data-types/float), [Date](/reference/data-types/date), [DateTime](/reference/data-types/datetime), [Decimal](/reference/data-types/decimal). * For the `hash` join algorithm it can't be the only column in the `JOIN` clause. Syntax `ASOF JOIN ... ON`: ```sql theme={null} 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`: ```sql theme={null} 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: ```text theme={null} 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](/reference/engines/table-engines/special/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: ```sql theme={null} 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: ```sql theme={null} 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](/reference/statements/in#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`: ```response theme={null} ┌─a─┬─b─┬─toTypeName(a)─┬─toTypeName(b)─┐ │ 1 │ 1 │ UInt16 │ UInt8 │ │ 2 │ 2 │ UInt16 │ UInt8 │ └───┴───┴───────────────┴───────────────┘ ``` and the table `t_2`: ```response theme={null} ┌──a─┬────b─┬─toTypeName(a)─┬─toTypeName(b)───┐ │ -1 │ 1 │ Int16 │ Nullable(Int64) │ │ 1 │ -1 │ Int16 │ Nullable(Int64) │ │ 1 │ 1 │ Int16 │ Nullable(Int64) │ └────┴──────┴───────────────┴─────────────────┘ ``` The query ```sql theme={null} SELECT a, b, toTypeName(a), toTypeName(b) FROM t_1 FULL JOIN t_2 USING (a, b); ``` returns the set: ```response theme={null} ┌──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](/reference/settings/session-settings/join#join_use_nulls) define how ClickHouse fills these cells. If the `JOIN` keys are [Nullable](/reference/data-types/nullable) fields, the rows where at least one of the keys has the value [NULL](/reference/syntax#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](/reference/engines/table-engines/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](/reference/statements/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](/reference/statements/create/dictionary) section.

Memory limitations

By default, ClickHouse uses the [hash join](https://en.wikipedia.org/wiki/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](https://en.wikipedia.org/wiki/Sort-merge_join) join algorithm. For `JOIN` algorithms description see the [join\_algorithm](/reference/settings/session-settings/join#join_algorithm) setting. If you need to restrict `JOIN` operation memory consumption use the following settings: * [max\_rows\_in\_join](/reference/settings/session-settings/max-rows#max_rows_in_join) — Limits number of rows in the hash table. * [max\_bytes\_in\_join](/reference/settings/session-settings/max-bytes#max_bytes_in_join) — Limits size of the hash table. When any of these limits is reached, ClickHouse acts as the [join\_overflow\_mode](/reference/settings/session-settings/join#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](/reference/settings/session-settings/max-bytes#max_bytes_before_external_join) — Absolute spill threshold. * [max\_bytes\_ratio\_before\_external\_join](/reference/settings/session-settings/max-bytes#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: ```sql theme={null} 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 ``` ```text theme={null} ┌─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 │ └───────────┴────────┴────────┘ ```

Related content

* Blog: [ClickHouse: A Blazingly Fast DBMS with Full SQL Join Support - Part 1](https://clickhouse.com/blog/clickhouse-fully-supports-joins) * Blog: [ClickHouse: A Blazingly Fast DBMS with Full SQL Join Support - Under the Hood - Part 2](https://clickhouse.com/blog/clickhouse-fully-supports-joins-hash-joins-part2) * Blog: [ClickHouse: A Blazingly Fast DBMS with Full SQL Join Support - Under the Hood - Part 3](https://clickhouse.com/blog/clickhouse-fully-supports-joins-full-sort-partial-merge-part3) * Blog: [ClickHouse: A Blazingly Fast DBMS with Full SQL Join Support - Under the Hood - Part 4](https://clickhouse.com/blog/clickhouse-fully-supports-joins-direct-join-part4)