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

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

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

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

Тип 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 может содержать несколько условий, объединённых с помощью операторов 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:

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.

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

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

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

┌

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

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;

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

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

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

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

В настоящее время ClickHouse поддерживает ALL/ANY/SEMI/ANTI INNER/LEFT/RIGHT/FULL JOIN не только с условиями равенства, но и с условиями неравенства. Условия неравенства поддерживаются только для алгоритмов JOIN hash, parallel_hash и grace_hash. Условие неравенства, вычисляемое во время JOIN, не может содержать arrayJoin, поскольку оно должно сохранять количество строк; условие, применяемое только к одной стороне, и ключ равенства с arrayJoin извлекаются до JOIN и не затрагиваются; недизъюнктивное условие ALL INNER JOIN также не затрагивается, поскольку в этом случае условие применяется после JOIN. Если разворачивание зависит только от одной стороны, перенесите его в ARRAY JOIN в подзапросе перед JOIN; условие, аргумент arrayJoin которого читает столбцы с обеих сторон, необходимо реструктурировать.

Пример

Таблица 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, он не будет сопоставлен со значением 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 │
└─────────┴───────┘

Значения NaN в ключах JOIN типа float не подчиняются приведённому выше правилу для NULL. Скалярное сравнение двух значений NaN (NaN = NaN) равно 0, однако ключи JOIN сравниваются не по правилам скалярной семантики — ключи NaN могут сопоставляться. Будут ли они фактически сопоставлены, зависит от деталей реализации, алгоритма JOIN, типа ключа и настроек сеанса. Не полагайтесь на конкретное поведение. Если необходимо, чтобы строки с NaN не сопоставлялись, преобразуйте их в значения NULL: ON if(isNaN(A.id), NULL, A.id) = B.id. В ASOF JOIN столбец для поиска ближайшего совпадения сравнивается по порядку, который NaN не поддерживает — отфильтруйте такие строки с обеих сторон.

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 соединить нельзя.

Результатом 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

┌

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

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;

┌

Существует два способа выполнить 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) │
└────┴──────┴───────────────┴─────────────────┘

При 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