Оператор JOIN

Документация по оператору JOIN

Оператор JOIN создает новую таблицу, объединяя столбцы одной или нескольких таблиц на основе общих значений. Это распространенная операция в базах данных с поддержкой SQL, соответствующая операции JOIN в реляционной алгебре. Частный случай, когда таблица соединяется сама с собой, часто называют “self-join”.

Синтаксис

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

Выражения из условия ON и столбцы из предложения USING называются “ключами JOIN”. Если не указано иное, JOIN создаёт декартово произведение строк с совпадающими “ключами JOIN”, из-за чего в результате может оказаться гораздо больше строк, чем в исходных таблицах.

​

Поддерживаемые типы JOIN

Поддерживаются все стандартные типы SQL JOIN:

Тип	Описание
INNER JOIN	возвращаются только совпадающие строки.
LEFT OUTER JOIN	помимо совпадающих строк возвращаются несовпадающие строки из левой таблицы.
RIGHT OUTER JOIN	помимо совпадающих строк возвращаются несовпадающие строки из правой таблицы.
FULL OUTER JOIN	помимо совпадающих строк возвращаются несовпадающие строки из обеих таблиц.
CROSS JOIN	создает декартово произведение всех строк таблиц; “ключи JOIN” не указываются.
NATURAL JOIN	автоматически выполняет JOIN по всем столбцам с одинаковыми именами в обеих таблицах; каждый общий столбец появляется в результате только один раз. Поддерживает варианты INNER (по умолчанию), LEFT, RIGHT и FULL. Эквивалентен JOIN ... USING (col1, col2, ...), где список столбцов определяется автоматически.

• JOIN без указания типа подразумевает INNER.
• Ключевое слово OUTER можно без опасений опустить.
• Альтернативный синтаксис для CROSS JOIN — указать несколько таблиц в FROM clause, разделив их запятыми.
• Если у NATURAL JOIN нет совпадающих столбцов, он работает как CROSS JOIN.

В ClickHouse также доступны дополнительные типы JOIN:

Тип	Описание
LEFT SEMI JOIN, RIGHT SEMI JOIN	Разрешающий список по “ключам JOIN” без построения декартова произведения.
LEFT ANTI JOIN, RIGHT ANTI JOIN	Запрещающий список по “ключам JOIN” без построения декартова произведения.
LEFT ANY JOIN, RIGHT ANY JOIN, INNER ANY JOIN	Частично (для противоположной стороны LEFT и RIGHT) или полностью (для INNER и FULL) отключает декартово произведение для стандартных типов JOIN.
ASOF JOIN, LEFT ASOF JOIN	Объединение последовательностей с неточным совпадением. Использование ASOF JOIN описано ниже.
PASTE JOIN	Выполняет горизонтальную конкатенацию двух таблиц.

Если для join_algorithm задано значение partial_merge, то RIGHT JOIN и FULL JOIN поддерживаются только со strictness ALL (SEMI, ANTI, ANY и ASOF не поддерживаются).

​

Настройки

Тип JOIN по умолчанию можно переопределить с помощью настройки join_default_strictness.

Поведение сервера ClickHouse для операций ANY JOIN зависит от настройки any_join_distinct_right_table_keys.

См. также

• join_algorithm
• join_any_take_last_row
• join_use_nulls
• partial_merge_join_rows_in_right_blocks
• join_on_disk_max_files_to_merge
• any_join_distinct_right_table_keys

Используйте настройку cross_to_inner_join_rewrite, чтобы задать поведение, если ClickHouse не удаётся преобразовать CROSS JOIN в INNER JOIN. Значение по умолчанию — 1, что позволяет продолжить выполнение JOIN, но он будет медленнее. Установите cross_to_inner_join_rewrite в 0, если хотите, чтобы было сгенерировано исключение, и в 2, чтобы не выполнять cross joins, а вместо этого принудительно преобразовывать все joins через запятую и cross joins. Если преобразование не удастся при значении 2, вы получите сообщение об ошибке: “Please, try to simplify WHERE section”.

​

Условия в разделе ON

Раздел ON может содержать несколько условий, объединённых с помощью операторов AND и OR. Условия, задающие ключи JOIN, должны:

• ссылаться как на левую, так и на правую таблицу
• использовать оператор равенства

Другие условия могут использовать иные логические операторы, но при этом они должны ссылаться либо на левую, либо на правую таблицу запроса.

Строки объединяются, если выполняется всё составное условие. Если условия не выполняются, строки всё равно могут попасть в результат в зависимости от типа JOIN. Обратите внимание: если поместить те же условия в раздел WHERE и они не выполняются, то строки всегда отфильтровываются из результата.

Оператор OR в условии ON работает на основе алгоритма hash join — для каждого аргумента OR, содержащего ключи JOIN, создаётся отдельная хеш-таблица, поэтому расход памяти и время выполнения запроса растут линейно по мере увеличения числа выражений OR в условии ON.

Если условие ссылается на столбцы из разных таблиц, то на данный момент поддерживается только оператор равенства (=).

Пример

Рассмотрим table_1 и 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 │
└────┴──────┘     └────┴────────────────┴────────┘

Запрос с одним условием по ключу в JOIN и дополнительным условием для table_2:

Query

SELECT name, text FROM table_1 LEFT OUTER JOIN table_2
    ON table_1.Id = table_2.Id AND startsWith(table_2.text, 'Text');

Обратите внимание, что результат содержит строку с именем C и пустым текстовым столбцом. Она включена в результат, поскольку используется JOIN типа OUTER.

Response

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

Запрос с JOIN типа INNER и несколькими условиями:

Query

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

Response

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

Запрос с JOIN типа INNER и условием с OR:

Query

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

┌─a─┬──b─┬─val─┐
│ 0 │  0 │   0 │
│ 1 │ -1 │   1 │
│ 2 │ -2 │   2 │
│ 3 │ -3 │   3 │
│ 4 │ -4 │   4 │
└───┴────┴─────┘

Запрос с типом JOIN INNER и условиями с OR и AND:

По умолчанию поддерживаются условия неравенства, если в них используются столбцы из одной и той же таблицы. Например, t1.a = t2.key AND t1.b > 0 AND t2.b > t2.c, поскольку в t1.b > 0 используются только столбцы из t1, а в t2.b > t2.c — только столбцы из t2. Однако вы можете попробовать экспериментальную поддержку условий вида t1.a = t2.key AND t1.b > t2.key; подробнее см. в разделе ниже.

Query

SELECT a, b, val FROM t1 INNER JOIN t2 ON t1.a = t2.key OR t1.b = t2.key AND t2.val > 3;

Response

┌─a─┬──b─┬─val─┐
│ 0 │  0 │   0 │
│ 2 │ -2 │   2 │
│ 4 │ -4 │   4 │
└───┴────┴─────┘

​

JOIN с условиями неравенства для столбцов из разных таблиц

В настоящее время ClickHouse поддерживает ALL/ANY/SEMI/ANTI INNER/LEFT/RIGHT/FULL JOIN не только с условиями равенства, но и с условиями неравенства. Условия неравенства поддерживаются только для алгоритмов JOIN hash и grace_hash. Условия неравенства не поддерживаются при использовании join_use_nulls.

Пример

Таблица t1:

┌─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 │
└──────┴──────┴───┴───┴───┘

Таблица t2

┌─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 │
└──────┴──────┴───┴───┴───┘

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

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

​

Значения NULL в ключах JOIN

NULL не равно никакому значению, включая само себя. Это означает, что если ключ JOIN в одной таблице имеет значение NULL, он не будет сопоставлен со значением NULL в другой таблице.

Пример

Таблица A:

┌───id─┬─name────┐
│    1 │ Alice   │
│    2 │ Bob     │
│ ᴺᵁᴸᴸ │ Charlie │
└──────┴─────────┘

Таблица B:

┌───id─┬─score─┐
│    1 │    90 │
│    3 │    85 │
│ ᴺᵁᴸᴸ │    88 │
└──────┴───────┘

SELECT A.name, B.score FROM A LEFT JOIN B ON A.id = B.id

┌─name────┬─score─┐
│ Alice   │    90 │
│ Bob     │     0 │
│ Charlie │     0 │
└─────────┴───────┘

Обратите внимание, что строка с Charlie из таблицы A и строка с оценкой 88 из таблицы B отсутствуют в результате из-за значения NULL в ключе JOIN.

Если вы хотите сопоставлять значения NULL, используйте функцию isNotDistinctFrom для сравнения ключей JOIN.

SELECT A.name, B.score FROM A LEFT JOIN B ON isNotDistinctFrom(A.id, B.id)

┌─name────┬─score─┐
│ Alice   │    90 │
│ Bob     │     0 │
│ Charlie │    88 │
└─────────┴───────┘

​

Использование ASOF JOIN

ASOF JOIN полезен, когда нужно соединить записи без точного совпадения.

Для этого алгоритма JOIN в таблицах требуется специальный столбец. Этот столбец:

• должен содержать упорядоченную последовательность;
• может иметь один из следующих типов: Int, UInt, Float, Date, DateTime, Decimal;
• для алгоритма hash JOIN он не может быть единственным столбцом в условии JOIN.

Синтаксис ASOF JOIN ... ON:

SELECT expressions_list
FROM table_1
ASOF LEFT JOIN table_2
ON equi_cond AND closest_match_cond

Вы можете использовать любое количество условий равенства и ровно одно условие поиска ближайшего соответствия. Например, SELECT count() FROM table_1 ASOF LEFT JOIN table_2 ON table_1.a == table_2.b AND table_2.t <= table_1.t.

Для поиска ближайшего соответствия поддерживаются следующие условия: >, >=, <, <=.

Синтаксис ASOF JOIN ... USING:

SELECT expressions_list
FROM table_1
ASOF JOIN table_2
USING (equi_column1, ... equi_columnN, asof_column)

ASOF JOIN использует equi_columnX для JOIN по равенству и asof_column для JOIN по ближайшему совпадению с условием table_1.asof_column >= table_2.asof_column. Столбец asof_column всегда указывается последним в секции USING.

Например, рассмотрим следующие таблицы:

         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 может брать временную метку пользовательского события из table_1 и находить событие в table_2, временная метка которого наиболее близка к временной метке события из table_1 в соответствии с условием ближайшего совпадения. Если доступны равные значения временной метки, именно они считаются ближайшими. Здесь столбец user_id можно использовать для JOIN по равенству, а столбец ev_time — для JOIN по ближайшему совпадению. В нашем примере event_1_1 можно соединить с event_2_1, а event_1_2 — с event_2_3, но event_2_2 соединить нельзя.

ASOF JOIN поддерживается только алгоритмами JOIN hash и full_sorting_merge. Он не поддерживается в движке таблицы Join.

​

Использование PASTE JOIN

Результатом PASTE JOIN является таблица, содержащая все столбцы левого подзапроса, а затем все столбцы правого подзапроса. Строки сопоставляются по их позициям в исходных таблицах (порядок строк должен быть определён). Если подзапросы возвращают разное количество строк, лишние строки будут отброшены.

Пример:

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 │
└───┴──────┘

Примечание: в этом случае результат может быть недетерминированным при параллельном чтении. Например:

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 │
└───┴──────┘

​

Распределенный JOIN

Существует два способа выполнить JOIN с distributed таблицами:

• При использовании обычного JOIN запрос отправляется на удаленные серверы. Подзапросы выполняются на каждом из них, чтобы сформировать правую таблицу, после чего выполняется JOIN с этой таблицей. Иными словами, правая таблица формируется отдельно на каждом сервере.
• При использовании GLOBAL ... JOIN сервер-инициатор сначала выполняет подзапрос, чтобы вычислить одну из сторон JOIN, и собирает результат во временную таблицу. Затем эта временная таблица передается на каждый удаленный сервер, и на них выполняются запросы с использованием переданных временных данных. Для LEFT и INNER JOIN правая таблица вычисляется подзапросом. Для RIGHT JOIN вместо этого вычисляется левая таблица, поскольку сохраняется правая таблица, и ее следует читать из сегментов.

Будьте осторожны при использовании GLOBAL. Дополнительные сведения см. в разделе Распределенные подзапросы.

​

Неявное преобразование типов

Запросы INNER JOIN, LEFT JOIN, RIGHT JOIN и FULL JOIN поддерживают неявное преобразование типов для “ключей JOIN”. Однако запрос не может быть выполнен, если ключи JOIN из левой и правой таблиц нельзя привести к одному типу (например, не существует типа данных, который мог бы хранить все значения и из UInt64, и из Int64, или из String и Int32).

Пример

Рассмотрим таблицу t_1:

┌─a─┬─b─┬─toTypeName(a)─┬─toTypeName(b)─┐
│ 1 │ 1 │ UInt16        │ UInt8         │
│ 2 │ 2 │ UInt16        │ UInt8         │
└───┴───┴───────────────┴───────────────┘

и таблица t_2:

┌──a─┬────b─┬─toTypeName(a)─┬─toTypeName(b)───┐
│ -1 │    1 │ Int16         │ Nullable(Int64) │
│  1 │   -1 │ Int16         │ Nullable(Int64) │
│  1 │    1 │ Int16         │ Nullable(Int64) │
└────┴──────┴───────────────┴─────────────────┘

Запрос

SELECT a, b, toTypeName(a), toTypeName(b) FROM t_1 FULL JOIN t_2 USING (a, b);

возвращает набор:

┌──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) │
└────┴──────┴───────────────┴─────────────────┘

​

Рекомендации по использованию

​

Обработка пустых ячеек или ячеек со значением NULL

При JOIN таблиц могут появляться пустые ячейки. Настройка join_use_nulls определяет, как ClickHouse заполняет эти ячейки.

Если ключи JOIN — это поля Nullable, то строки, в которых хотя бы один из ключей имеет значение NULL, не объединяются.

​

Синтаксис

Столбцы, указанные в USING, должны иметь одинаковые имена в обоих подзапросах, а остальные столбцы должны называться по-разному. Чтобы изменить имена столбцов в подзапросах, можно использовать псевдонимы.

Предложение USING задаёт один или несколько столбцов для JOIN, тем самым устанавливая равенство этих столбцов. Список столбцов указывается без скобок. Более сложные условия JOIN не поддерживаются.

​

Ограничения синтаксиса

При использовании нескольких секций JOIN в одном запросе SELECT:

• Получение всех столбцов через * доступно, только если объединяются таблицы, а не подзапросы.
• Секция PREWHERE недоступна.
• Предложение USING недоступно.

Для секций ON, WHERE и GROUP BY:

• Произвольные выражения нельзя использовать в секциях ON, WHERE и GROUP BY, но можно определить выражение в секции SELECT, а затем использовать его в этих секциях через алиас.

​

Производительность

При выполнении JOIN порядок выполнения по отношению к другим этапам запроса не оптимизируется. JOIN (поиск в правой таблице) выполняется до фильтрации в WHERE и до агрегации.

Каждый раз, когда выполняется запрос с одним и тем же JOIN, подзапрос запускается заново, потому что результат не кэшируется. Чтобы этого избежать, используйте специальный движок таблицы Join — подготовленный массив для JOIN, который всегда находится в оперативной памяти.

В некоторых случаях эффективнее использовать IN вместо JOIN.

Если JOIN нужен для соединения с таблицами измерений (это относительно небольшие таблицы, содержащие свойства измерений, например названия рекламных кампаний), он может быть не очень удобен, поскольку к правой таблице приходится обращаться заново при каждом запросе. В таких случаях вместо JOIN следует использовать возможность «словари». Подробнее см. в разделе Словари.

​

Ограничения памяти

По умолчанию ClickHouse использует алгоритм hash join. ClickHouse берет right_table и создает для нее хеш-таблицу в оперативной памяти. Если включен параметр join_algorithm = 'auto', то после достижения определенного порога потребления памяти ClickHouse переключается на алгоритм merge JOIN. Описание алгоритмов JOIN см. в настройке join_algorithm.

Если вам нужно ограничить потребление памяти операцией JOIN, используйте следующие настройки:

• max_rows_in_join — Ограничивает количество строк в хеш-таблице.
• max_bytes_in_join — Ограничивает размер хеш-таблицы.

При достижении любого из этих ограничений ClickHouse действует в соответствии с настройкой join_overflow_mode.

​

Примеры

Пример:

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

┌─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 │
└───────────┴────────┴────────┘

​

Связанные материалы

• Блог: ClickHouse: молниеносно быстрая СУБД с полной поддержкой SQL JOIN — Часть 1
• Блог: ClickHouse: молниеносно быстрая СУБД с полной поддержкой SQL JOIN — Внутреннее устройство — Часть 2
• Блог: ClickHouse: молниеносно быстрая СУБД с полной поддержкой SQL JOIN — Внутреннее устройство — Часть 3
• Блог: ClickHouse: молниеносно быстрая СУБД с полной поддержкой SQL JOIN — Внутреннее устройство — Часть 4