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

Оператор JOIN

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

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

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

Пример

Таблица A:

Таблица B:

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

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

Использование 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 использует алгоритм hash join. ClickHouse берет правую таблицу и создает для нее хеш-таблицу в оперативной памяти. Если join_algorithm = 'auto' включен, то после достижения определенного порога потребления памяти ClickHouse переходит на алгоритм объединения merge. Для описания алгоритмов JOIN см. настройку join_algorithm.

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

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

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

Примеры​

Пример: