Working with JOINs in ClickHouse

An alternative syntax for `CROSS JOIN` specifies multiple tables in the `FROM` clause separated by commas.

ClickHouse is [rewriting](https://github.com/ClickHouse/ClickHouse/blob/23.2/src/Core/Settings.h#L896) a `CROSS JOIN` to an `INNER JOIN` if there are joining expressions in the `WHERE` section of the query.

You can check that for the example query via [EXPLAIN SYNTAX](/docs/sql-reference/statements/explain#explain-syntax) (that returns the syntactically optimized version into which a query gets rewritten before being [executed](https://youtu.be/hP6G2Nlz_cA)):

```
EXPLAIN SYNTAX
SELECT
    m.name AS name,
    g.genre AS genre
FROM movies AS m
CROSS JOIN genres AS g
WHERE m.id = g.movie_id
ORDER BY
    m.year DESC,
    m.name ASC,
    g.genre ASC
LIMIT 10;
```

```
┌─explain─────────────────────────────────────┐
│ SELECT                                      │
│     name AS name,                           │
│     genre AS genre                          │
│ FROM movies AS m                            │
│ ALL INNER JOIN genres AS g ON id = movie_id │
│ WHERE id = movie_id                         │
│ ORDER BY                                    │
│     year DESC,                              │
│     name ASC,                               │
│     genre ASC                               │
│ LIMIT 10                                    │
└─────────────────────────────────────────────┘
```

The `INNER JOIN` clause in the syntactically optimized `CROSS JOIN` query version contains the `ALL` keyword, that got explicitly added in order to keep the cartesian product semantics of the `CROSS JOIN` even when being rewritten into an `INNER JOIN`, for which the cartesian product can be [disabled](/docs/operations/settings/settings#join_default_strictness).

```
ALL
```

And because, as mentioned above, the `OUTER` keyword can be omitted for a `RIGHT OUTER JOIN`, and the optional `ALL` keyword can be added, you can write `ALL RIGHT JOIN` and it will work all right.

## (LEFT / RIGHT) SEMI JOIN[​](#left--right-semi-join "Direct link to (LEFT / RIGHT) SEMI JOIN")

A `LEFT SEMI JOIN` query returns column values for each row from the left table that has at least one join key match in the right table.
Only the first found match is returned (the cartesian product is disabled).

A `RIGHT SEMI JOIN` query is similar and returns values for all rows from the right table with at least one match in the left table, but only the first found match is returned.

This query finds all actors/actresses that performed in a movie in 2023.
Note that with a normal (`INNER`) join, the same actor/actress would show up more than one time if they had more than one role in 2023:

```
SELECT
    a.first_name,
    a.last_name
FROM actors AS a
LEFT SEMI JOIN roles AS r ON a.id = r.actor_id
WHERE toYear(created_at) = '2023'
ORDER BY id ASC
LIMIT 10;
```

```
┌─first_name─┬─last_name──────────────┐
│ Michael    │ 'babeepower' Viera     │
│ Eloy       │ 'Chincheta'            │
│ Dieguito   │ 'El Cigala'            │
│ Antonio    │ 'El de Chipiona'       │
│ José       │ 'El Francés'           │
│ Félix      │ 'El Gato'              │
│ Marcial    │ 'El Jalisco'           │
│ José       │ 'El Morito'            │
│ Francisco  │ 'El Niño de la Manola' │
│ Víctor     │ 'El Payaso'            │
└────────────┴────────────────────────┘
```

## (LEFT / RIGHT) ANTI JOIN[​](#left--right-anti-join "Direct link to (LEFT / RIGHT) ANTI JOIN")

A `LEFT ANTI JOIN` returns column values for all non-matching rows from the left table.

Similarly, the `RIGHT ANTI JOIN` returns column values for all non-matching right table rows.

An alternative formulation of the previous outer join example query is using an anti join for finding movies that have no genre in the dataset:

```
SELECT m.name
FROM movies AS m
LEFT ANTI JOIN genres AS g ON m.id = g.movie_id
ORDER BY
    year DESC,
    name ASC
LIMIT 10;
```

```
┌─name──────────────────────────────────────┐
│ """Pacific War, The"""                    │
│ """Turin 2006: XX Olympic Winter Games""" │
│ Arthur, the Movie                         │
│ Bridge to Terabithia                      │
│ Mars in Aries                             │
│ Master of Space and Time                  │
│ Ninth Life of Louis Drax, The             │
│ Paradox                                   │
│ Ratatouille                               │
│ """American Dad"""                        │
└───────────────────────────────────────────┘
```

## (LEFT / RIGHT / INNER) ANY JOIN[​](#left--right--inner-any-join "Direct link to (LEFT / RIGHT / INNER) ANY JOIN")

A `LEFT ANY JOIN` is the combination of the `LEFT OUTER JOIN` + the `LEFT SEMI JOIN`, meaning that ClickHouse returns column values for each row from the left table, either combined with the column values of a matching row from the right table or combined with default column values for the right table, in case no match exists.
If a row from the left table has more than one match in the right table, ClickHouse only returns the combined column values from the first found match (the cartesian product is disabled).

Similarly, the `RIGHT ANY JOIN` is the combination of the `RIGHT OUTER JOIN` + the `RIGHT SEMI JOIN`.

And the `INNER ANY JOIN` is the `INNER JOIN` with a disabled cartesian product.

The following example demonstrates the `LEFT ANY JOIN` with an abstract example using two temporary tables (`left_table` and `right_table`) constructed with the [values](https://github.com/ClickHouse/ClickHouse/blob/23.2/src/TableFunctions/TableFunctionValues.h) [table function](/docs/sql-reference/table-functions):

```
WITH
    left_table AS (SELECT * FROM VALUES('c UInt32', 1, 2, 3)),
    right_table AS (SELECT * FROM VALUES('c UInt32', 2, 2, 3, 3, 4))
SELECT
    l.c AS l_c,
    r.c AS r_c
FROM left_table AS l
LEFT ANY JOIN right_table AS r ON l.c = r.c;
```

```
┌─l_c─┬─r_c─┐
│   1 │   0 │
│   2 │   2 │
│   3 │   3 │
└─────┴─────┘
```

This is the same query using a `RIGHT ANY JOIN`:

```
WITH
    left_table AS (SELECT * FROM VALUES('c UInt32', 1, 2, 3)),
    right_table AS (SELECT * FROM VALUES('c UInt32', 2, 2, 3, 3, 4))
SELECT
    l.c AS l_c,
    r.c AS r_c
FROM left_table AS l
RIGHT ANY JOIN right_table AS r ON l.c = r.c;
```

```
┌─l_c─┬─r_c─┐
│   2 │   2 │
│   2 │   2 │
│   3 │   3 │
│   3 │   3 │
│   0 │   4 │
└─────┴─────┘
```

This is the query with an `INNER ANY JOIN`:

```
WITH
    left_table AS (SELECT * FROM VALUES('c UInt32', 1, 2, 3)),
    right_table AS (SELECT * FROM VALUES('c UInt32', 2, 2, 3, 3, 4))
SELECT
    l.c AS l_c,
    r.c AS r_c
FROM left_table AS l
INNER ANY JOIN right_table AS r ON l.c = r.c;
```

```
┌─l_c─┬─r_c─┐
│   2 │   2 │
│   3 │   3 │
└─────┴─────┘
```

## ASOF JOIN[​](#asof-join "Direct link to ASOF JOIN")

The `ASOF JOIN`, provides non-exact matching capabilities.
If a row from the left table doesn’t have an exact match in the right table, then the closest matching row from the right table is used as a match instead.

This is particularly useful for time-series analytics and can drastically reduce query complexity.

The following example performs time-series analytics of stock market data.
A `quotes` table contains stock symbol quotes based on specific times of the day.
The price is updated every 10 seconds in the example data.
A `trades` table lists symbol trades - a specific volume of a symbol got bought at a specific time:

In order to calculate the concrete cost of each trade, we need to match the trades with their closest quote time.

This is easy and compact with the `ASOF JOIN`, where you use the `ON` clause for specifying an exact match condition and the `AND` clause for specifying the closest match condition - for a specific symbol (exact match) you're looking for the row with the ‘closest’ time from the `quotes` table at exactly or before the time (non-exact match) of a trade of that symbol:

```
SELECT
    t.symbol,
    t.volume,
    t.time AS trade_time,
    q.time AS closest_quote_time,
    q.price AS quote_price,
    t.volume * q.price AS final_price
FROM trades t
ASOF LEFT JOIN quotes q ON t.symbol = q.symbol AND t.time >= q.time
FORMAT Vertical;
```