Перейти к основному содержимому
Перейти к основному содержимому

JOIN клаузула

JOIN создает новую таблицу, комбинируя столбцы из одной или нескольких таблиц, используя значения, общие для каждой из них. Это распространенная операция в базах данных с поддержкой SQL, которая соответствует объединению в реляционной алгебре. Специальный случай объединения одной таблицы часто называют "самообъединением".

Синтаксис

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

Связанный контент​

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

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

  • INNER JOIN, возвращаются только совпадающие строки.
  • LEFT OUTER JOIN, возвращаются несовпадающие строки из левой таблицы в дополнение к совпадающим строкам.
  • RIGHT OUTER JOIN, возвращаются несовпадающие строки из правой таблицы в дополнение к совпадающим строкам.
  • FULL OUTER JOIN, возвращаются несовпадающие строки из обеих таблиц в дополнение к совпадающим строкам.
  • CROSS JOIN, создает декартово произведение целых таблиц, "ключи объединения" не указываются.

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

Дополнительные типы объединения, доступные в ClickHouse:

  • LEFT SEMI JOIN и RIGHT SEMI JOIN, белый список по "ключам объединения", без создания декартова произведения.
  • LEFT ANTI JOIN и RIGHT ANTI 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 поддерживаются только с ALL строгостью (SEMI, ANTI, ANY и ASOF не поддерживаются).

Настройки​

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

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

См. также

Используйте настройку cross_to_inner_join_rewrite, чтобы определить поведение, когда ClickHouse не может переписать CROSS JOIN как INNER JOIN. Значение по умолчанию - 1, что позволяет продолжить выполнение объединения, но оно будет медленнее. Установите cross_to_inner_join_rewrite в 0, если вы хотите, чтобы возникла ошибка, и установите его в 2, чтобы не выполнять кросс-объединения, а вместо этого принудительно переписать все запятые/кросс объединения. Если переписывание не удастся при значении 2, вы получите сообщение об ошибке с текстом "Пожалуйста, попробуйте упростить секцию WHERE".

Условия секции ON​

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

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

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

примечание

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

Пример

Рассмотрим table_1 и table_2:

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

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

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

Результат:

Запрос с типом объединения INNER и условием с OR:

Результат:

Запрос с типом объединения 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, обратите внимание на раздел ниже для получения дополнительных сведений.

Результат:

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

На данный момент ClickHouse поддерживает ALL/ANY/SEMI/ANTI INNER/LEFT/RIGHT/FULL JOIN с неравными условиями в дополнение к условиям равенства. Неравные условия поддерживаются только для алгоритмов объединения hash и grace_hash. Неравные условия не поддерживаются с join_use_nulls.

Пример

Таблица t1:

Таблица t2

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

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

Пример

Таблица A:

Таблица B:

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

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

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

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

Алгоритм требует специального столбца в таблицах. Этот столбец:

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

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

Вы можете использовать любое количество условий равенства и ровно одно условие ближайшего совпадения. Например, 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:

ASOF JOIN использует equi_columnX для соединения по равенству и asof_column для соединения по ближайшему совпадению с условием 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 можно использовать для объединения по равенству, а столбец ev_time — для объединения по ближайшему совпадению. В нашем примере event_1_1 может быть объединен с event_2_1, а event_1_2 может быть объединен с event_2_3, но event_2_2 не может быть объединен.

примечание

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

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

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

Пример:

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

Распределенное JOIN​

Существует два способа выполнить объединение с использованием распределенных таблиц:

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

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

Неявное приведение типов​

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

Пример

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

и таблицу t_2:

Запрос

возвращает множество:

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

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

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

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

Синтаксис​

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

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

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

Для нескольких клауз в JOIN в одном запросе SELECT:

  • Взять все столбцы с помощью * доступно только в случае объединения таблиц, а не подзапросов.
  • Клаузула PREWHERE недоступна.
  • Клаузула USING недоступна.

Для клауз ON, WHERE и GROUP BY:

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

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

Когда выполняется JOIN, нет оптимизации порядка выполнения относительно других этапов запроса. Объединение (поиск в правой таблице) выполняется до фильтрации в WHERE и до агрегации.

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

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

Если вам нужен JOIN для объединения с измерениями (это относительно небольшие таблицы, которые содержат свойства измерений, такие как названия рекламных кампаний), JOIN может быть не самым удобным из-за того, что правую таблицу повторно запрашивают для каждого запроса. Для таких случаев есть функция "словари", которую следует использовать вместо JOIN. Для получения дополнительной информации смотрите раздел Словари.

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

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

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

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

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

Примеры​

Пример: