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

JOIN Clause

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

Синтаксис

Выражения из секции 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. Условия, specifying join keys, должны ссылаться как на левую, так и на правую таблицы и должны использовать оператор равенства. Другие условия могут использовать другие логические операторы, но они должны ссылаться либо на левую, либо на правую таблицу запроса.

Строки объединяются, если выполняется все сложное условие. Если условия не выполнены, строки все равно могут быть включены в результат в зависимости от типа 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.
  • Для алгоритма соединения 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 может быть не очень удобным из-за того, что правая таблица повторно доступается для каждого запроса. Для таких случаев есть функция "словари", которую вы должны использовать вместо JOIN. Для получения дополнительной информации см. раздел Словари.

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

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

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

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

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

Примеры​

Пример: