JOIN clause

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/select/join.md)

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**

```
SELECT <expr_list>
FROM <left_table>
[GLOBAL] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI|ANY|ALL|ASOF] JOIN <right_table>
(ON <expr_list>)|(USING <column_list>) ...
```

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[​](#supported-types-of-join "Direct link to 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](/docs/sql-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. | | | | | | | | | | | |

Note

When [join\_algorithm](/docs/operations/settings/settings#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).

## Settings[​](#settings "Direct link to Settings")

The default join type can be overridden using [`join_default_strictness`](/docs/operations/settings/settings#join_default_strictness) setting.

The behavior of the ClickHouse server for `ANY JOIN` operations depends on the [`any_join_distinct_right_table_keys`](/docs/operations/settings/settings#any_join_distinct_right_table_keys) setting.

**See also**

- [`join_algorithm`](/docs/operations/settings/settings#join_algorithm)
- [`join_any_take_last_row`](/docs/operations/settings/settings#join_any_take_last_row)
- [`join_use_nulls`](/docs/operations/settings/settings#join_use_nulls)
- [`partial_merge_join_rows_in_right_blocks`](/docs/operations/settings/settings#partial_merge_join_rows_in_right_blocks)
- [`join_on_disk_max_files_to_merge`](/docs/operations/settings/settings#join_on_disk_max_files_to_merge)
- [`any_join_distinct_right_table_keys`](/docs/operations/settings/settings#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[​](#on-section-conditions "Direct link to 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 logical operators but they must reference either the left or the right table of a query.

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.

Note

If a condition references columns from different tables, then only the equality operator (`=`) is supported so far.

**Example**

Consider `table_1` and `table_2`:

```
┌─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`:

```
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.

```
┌─name─┬─text───┐
│ A    │ Text A │
│ B    │ Text B │
│ C    │        │
└──────┴────────┘
```

Query with `INNER` type of a join and multiple conditions:

```
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');
```

Result:

```
┌─name─┬─text───┬─scores─┐
│ B    │ Text B │     15 │
└──────┴────────┴────────┘
```

Query with `INNER` type of a join and condition with `OR`:

```
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);