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

Оператор 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 с ключами для соединения создается отдельная хеш-таблица, поэтому потребление памяти и время выполнения запроса растут линейно с увеличением количества выражений 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, ознакомьтесь с разделом ниже для получения более подробной информации.

Результат:

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

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

Пример

Таблица t1:

Таблица t2

NULL значения в ключах соединения​

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

Пример

Таблица A:

Таблица B:

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

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

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

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

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

  • Должен содержать упорядоченную последовательность.
  • Может быть одного из следующих типов: Int, UInt, Float, Date, DateTime, Decimal.
  • Для алгоритма hash это не может быть единственным столбцом в секции 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. Для получения дополнительной информации см. раздел Словари.

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

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

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

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

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

Примеры​

Пример:

Связанное содержимое​