> ## 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 в ClickHouse
> Вводное руководство по работе с JOIN в ClickHouse
export const Image = ({img, alt, size = "lg"}) => {
const normalizedSize = ["sm", "md", "lg"].includes(size) ? size : "lg";
return ;
};
ClickHouse полностью поддерживает стандартные SQL JOIN, обеспечивая эффективный анализ данных.
В этом руководстве вы познакомитесь с некоторыми распространёнными типами JOIN и узнаете, как использовать их с помощью диаграмм Венна и примеров запросов к нормализованному набору данных [IMDB](https://en.wikipedia.org/wiki/IMDb) из [репозитория реляционных наборов данных](https://relational.fit.cvut.cz/dataset/IMDb).
## Тестовые данные и ресурсы
Инструкции по созданию и загрузке таблиц можно найти [здесь](/docs/ru/integrations/connectors/data-ingestion/etl-tools/dbt/guides).
Набор данных также доступен в [Песочнице ClickHouse](https://sql.clickhouse.com?query_id=AACTS8ZBT3G7SSGN8ZJBJY), если вы не хотите создавать и загружать
таблицы локально.
Вы будете использовать следующие четыре таблицы из демонстрационного набора данных:
Эти четыре таблицы содержат данные о фильмах, у которых может быть один или несколько жанров.
Роли в фильмах исполняют актёры.
Стрелки на диаграмме выше обозначают [связи между внешним и первичным ключом](https://en.wikipedia.org/wiki/Foreign_key). Например, столбец `movie_id` в строке таблицы `genres` содержит значение `id` из строки таблицы `movies`.
Между фильмами и актёрами существует [связь многие-ко-многим](https://en.wikipedia.org/wiki/Many-to-many_\(data_model\)).
Эта связь многие-ко-многим нормализуется в две [связи один-ко-многим](https://en.wikipedia.org/wiki/One-to-many_\(data_model\)) с помощью таблицы `roles`.
Каждая строка в таблице `roles` содержит значения из столбцов `id` таблиц `movies` и `actors`.
## Поддерживаемые в ClickHouse типы JOIN
ClickHouse поддерживает следующие типы JOIN:
* [INNER JOIN](#inner-join)
* [OUTER JOIN](#left--right--full-outer-join)
* [CROSS JOIN](#cross-join)
* [SEMI JOIN](#left--right-semi-join)
* [ANTI JOIN](#left--right-anti-join)
* [ANY JOIN](#left--right--inner-any-join)
* [ASOF JOIN](#asof-join)
В следующих разделах мы рассмотрим примеры запросов для каждого из перечисленных выше типов JOIN.
## INNER JOIN
`INNER JOIN` возвращает для каждой пары строк, совпадающих по ключам JOIN, значения столбцов строки из левой таблицы, объединённые со значениями столбцов строки из правой таблицы.
Если для строки находится более одного совпадения, возвращаются все совпадения (то есть для строк с совпадающими ключами JOIN формируется [декартово произведение](https://en.wikipedia.org/wiki/Cartesian_product)).
Этот запрос находит жанры для каждого фильма, объединяя таблицу `movies` с таблицей `genres`:
```sql theme={null}
SELECT
m.name AS name,
g.genre AS genre
FROM movies AS m
INNER JOIN genres AS g ON m.id = g.movie_id
ORDER BY
m.year DESC,
m.name ASC,
g.genre ASC
LIMIT 10;
```
```response theme={null}
┌─name───────────────────────────────────┬─genre─────┐
│ Harry Potter and the Half-Blood Prince │ Action │
│ Harry Potter and the Half-Blood Prince │ Adventure │
│ Harry Potter and the Half-Blood Prince │ Family │
│ Harry Potter and the Half-Blood Prince │ Fantasy │
│ Harry Potter and the Half-Blood Prince │ Thriller │
│ DragonBall Z │ Action │
│ DragonBall Z │ Adventure │
│ DragonBall Z │ Comedy │
│ DragonBall Z │ Fantasy │
│ DragonBall Z │ Sci-Fi │
└────────────────────────────────────────┴───────────┘
```
Ключевое слово `INNER` можно опустить.
Поведение `INNER JOIN` можно расширить или изменить с помощью одного из следующих типов JOIN.
## (LEFT / RIGHT / FULL) OUTER JOIN
`LEFT OUTER JOIN` работает как `INNER JOIN`, но для несовпадающих строк левой таблицы ClickHouse возвращает [значения по умолчанию](/docs/ru/reference/statements/create/table#default_values) для столбцов правой таблицы.
Запрос `RIGHT OUTER JOIN` устроен аналогично и также возвращает значения из несовпадающих строк правой таблицы вместе со значениями по умолчанию для столбцов левой таблицы.
Запрос `FULL OUTER JOIN` объединяет `LEFT` и `RIGHT OUTER JOIN` и возвращает значения из несовпадающих строк левой и правой таблиц вместе со значениями по умолчанию для столбцов правой и левой таблиц соответственно.
ClickHouse можно [настроить](/docs/ru/reference/settings/session-settings#join_use_nulls) так, чтобы он возвращал [NULL](/docs/ru/reference/syntax#null) вместо значений по умолчанию (однако по [соображениям производительности](/docs/ru/reference/data-types/nullable#storage-features) это не рекомендуется).
Этот запрос находит все фильмы без жанра: он выбирает все строки из таблицы `movies`, для которых нет совпадений в таблице `genres`, и поэтому они получают (во время выполнения запроса) значение по умолчанию 0 для столбца `movie_id`:
```sql theme={null}
SELECT m.name
FROM movies AS m
LEFT JOIN genres AS g ON m.id = g.movie_id
WHERE g.movie_id = 0
ORDER BY
m.year DESC,
m.name ASC
LIMIT 10;
```
```response theme={null}
┌─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""" │
└───────────────────────────────────────────┘
```
Ключевое слово `OUTER` можно не указывать.
## CROSS JOIN
`CROSS JOIN` создает полное декартово произведение двух таблиц без учета ключей JOIN.
Каждая строка из левой таблицы объединяется с каждой строкой из правой таблицы.
Таким образом, следующий запрос объединяет каждую строку из таблицы `movies` с каждой строкой из таблицы `genres`:
```sql theme={null}
SELECT
m.name,
m.id,
g.movie_id,
g.genre
FROM movies AS m
CROSS JOIN genres AS g
LIMIT 10;
```
```response theme={null}
┌─name─┬─id─┬─movie_id─┬─genre───────┐
│ #28 │ 0 │ 1 │ Documentary │
│ #28 │ 0 │ 1 │ Short │
│ #28 │ 0 │ 2 │ Comedy │
│ #28 │ 0 │ 2 │ Crime │
│ #28 │ 0 │ 5 │ Western │
│ #28 │ 0 │ 6 │ Comedy │
│ #28 │ 0 │ 6 │ Family │
│ #28 │ 0 │ 8 │ Animation │
│ #28 │ 0 │ 8 │ Comedy │
│ #28 │ 0 │ 8 │ Short │
└──────┴────┴──────────┴─────────────┘
```
Хотя предыдущий пример запроса сам по себе не имел особого смысла, его можно дополнить условием `WHERE`, чтобы сопоставить совпадающие строки и воспроизвести поведение `INNER JOIN` при поиске жанров для каждого фильма:
```sql theme={null}
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;
```
Альтернативный синтаксис `CROSS JOIN` позволяет указать несколько таблиц в предложении `FROM`, разделяя их запятыми.
ClickHouse [переписывает](https://github.com/ClickHouse/ClickHouse/blob/23.2/src/Core/Settings.h#L896) `CROSS JOIN` в `INNER JOIN`, если в разделе `WHERE` запроса есть выражения для JOIN.
Это можно проверить на примере запроса с помощью [EXPLAIN SYNTAX](/docs/ru/reference/statements/explain#explain-syntax) (он возвращает синтаксически оптимизированную версию, в которую запрос переписывается перед [выполнением](https://youtu.be/hP6G2Nlz_cA)):
```sql theme={null}
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;
```
```response theme={null}
┌─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 │
└─────────────────────────────────────────────┘
```
В синтаксически оптимизированной версии запроса `CROSS JOIN` предложение `INNER JOIN` содержит ключевое слово `ALL`, явно добавленное для сохранения семантики декартова произведения `CROSS JOIN` даже при переписывании в `INNER JOIN`, для которого декартово произведение можно [отключить](/docs/ru/reference/settings/session-settings#join_default_strictness).
```sql theme={null}
ALL
```
И поскольку, как упоминалось выше, ключевое слово `OUTER` в `RIGHT OUTER JOIN` можно опустить, а необязательное ключевое слово `ALL` — добавить, можно написать `ALL RIGHT JOIN`, и это тоже будет работать.
## (LEFT / RIGHT) SEMI JOIN
Запрос `LEFT SEMI JOIN` возвращает значения столбцов для каждой строки из левой таблицы, у которой есть хотя бы одно совпадение по ключу JOIN в правой таблице.
Возвращается только первое найденное совпадение (декартово произведение отключено).
Запрос `RIGHT SEMI JOIN` работает аналогично и возвращает значения для всех строк из правой таблицы, у которых есть хотя бы одно совпадение в левой таблице, но возвращается только первое найденное совпадение.
Этот запрос находит всех актёров и актрис, сыгравших в фильме в 2023 году.
Обратите внимание: при обычном (`INNER`) JOIN один и тот же актёр или актриса может появиться несколько раз, если в 2023 году у него или у неё было больше одной роли:
```sql theme={null}
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;
```
```response theme={null}
┌─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 ANTI JOIN` возвращает значения столбцов для всех несовпадающих строк левой таблицы.
Аналогично, `RIGHT ANTI JOIN` возвращает значения столбцов для всех несовпадающих строк правой таблицы.
Альтернативный вариант запроса из предыдущего примера с outer JOIN — использовать anti JOIN, чтобы найти фильмы, у которых в наборе данных не указан жанр:
```sql theme={null}
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;
```
```response theme={null}
┌─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 ANY JOIN` — это комбинация `LEFT OUTER JOIN` и `LEFT SEMI JOIN`, то есть ClickHouse возвращает значения столбцов для каждой строки из левой таблицы: либо в сочетании со значениями столбцов совпавшей строки из правой таблицы, либо со значениями столбцов правой таблицы по умолчанию, если совпадения нет.
Если для строки из левой таблицы в правой таблице найдено более одного совпадения, ClickHouse возвращает только объединённые значения столбцов из первого найденного совпадения (декартово произведение отключено).
Аналогично, `RIGHT ANY JOIN` — это комбинация `RIGHT OUTER JOIN` и `RIGHT SEMI JOIN`.
А `INNER ANY JOIN` — это `INNER JOIN` с отключённым декартовым произведением.
Следующий пример показывает `LEFT ANY JOIN` на абстрактном примере с использованием двух временных таблиц (`left_table` и `right_table`), созданных с помощью [values](https://github.com/ClickHouse/ClickHouse/blob/23.2/src/TableFunctions/TableFunctionValues.h) [табличной функции](/docs/ru/reference/functions/table-functions/index):
```sql theme={null}
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;
```
```response theme={null}
┌─l_c─┬─r_c─┐
│ 1 │ 0 │
│ 2 │ 2 │
│ 3 │ 3 │
└─────┴─────┘
```
Это тот же запрос, но с `RIGHT ANY JOIN`:
```sql theme={null}
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;
```
```response theme={null}
┌─l_c─┬─r_c─┐
│ 2 │ 2 │
│ 2 │ 2 │
│ 3 │ 3 │
│ 3 │ 3 │
│ 0 │ 4 │
└─────┴─────┘
```
Вот запрос с `INNER ANY JOIN`:
```sql theme={null}
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;
```
```response theme={null}
┌─l_c─┬─r_c─┐
│ 2 │ 2 │
│ 3 │ 3 │
└─────┴─────┘
```
## ASOF JOIN
`ASOF JOIN` предоставляет возможность неточного сопоставления.
Если для строки из левой таблицы не находится точного совпадения в правой таблице, в качестве совпадения используется наиболее близкая строка из правой таблицы.
Это особенно полезно для анализа временных рядов и может значительно снизить сложность запроса.
В следующем примере выполняется анализ временных рядов на данных фондового рынка.
Таблица `quotes` содержит котировки тикеров акций для определённых моментов времени в течение дня.
В примере цена обновляется каждые 10 секунд.
Таблица `trades` содержит сделки по тикерам: определённый объём акций был куплен в определённое время:
Чтобы вычислить фактическую стоимость каждой сделки, нужно сопоставить сделки с ближайшим временем котировки.
С `ASOF JOIN` это делается просто и компактно: условие `ON` используется для задания точного совпадения, а условие `AND` — для задания ближайшего совпадения. Для конкретного тикера (точное совпадение) нужно найти строку с «ближайшим» временем из таблицы `quotes`, которое точно совпадает со временем сделки по этому тикеру или предшествует ему (неточное совпадение):
```sql theme={null}
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;
```
```response theme={null}
Row 1:
──────
symbol: ABC
volume: 200
trade_time: 2023-02-22 14:09:05
closest_quote_time: 2023-02-22 14:09:00
quote_price: 32.11
final_price: 6422
Row 2:
──────
symbol: ABC
volume: 300
trade_time: 2023-02-22 14:09:28
closest_quote_time: 2023-02-22 14:09:20
quote_price: 32.15
final_price: 9645
```
Условие `ON` в `ASOF JOIN` является обязательным и задаёт условие точного совпадения наряду с условием неточного совпадения в условии `AND`.
## Кратко
В этом руководстве показано, что ClickHouse поддерживает все стандартные типы SQL JOIN, а также специальные варианты JOIN для аналитических запросов.
Подробнее о JOIN см. в документации по оператору [JOIN](/docs/ru/reference/statements/select/join).