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

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

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

Тип	Описание
`INNER JOIN`	возвращаются только совпадающие строки.
`LEFT OUTER JOIN`	помимо совпадающих строк возвращаются несовпадающие строки из левой таблицы.
`RIGHT OUTER JOIN`	помимо совпадающих строк возвращаются несовпадающие строки из правой таблицы.
`FULL OUTER JOIN`	помимо совпадающих строк возвращаются несовпадающие строки из обеих таблиц.
`CROSS JOIN`	создает декартово произведение всех строк таблиц; “ключи JOIN” не указываются.
`NATURAL JOIN`	автоматически выполняет JOIN по всем столбцам с одинаковыми именами в обеих таблицах; каждый общий столбец появляется в результате только один раз. Поддерживает варианты `INNER` (по умолчанию), `LEFT`, `RIGHT` и `FULL`. Эквивалентен `JOIN ... USING (col1, col2, ...)`, где список столбцов определяется автоматически.

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

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

Тип	Описание
`LEFT SEMI JOIN`, `RIGHT SEMI JOIN`	Разрешающий список по “ключам JOIN” без построения декартова произведения.
`LEFT ANTI JOIN`, `RIGHT ANTI JOIN`	Запрещающий список по “ключам JOIN” без построения декартова произведения.
`LEFT ANY JOIN`, `RIGHT ANY JOIN`, `INNER ANY JOIN`	Частично (для противоположной стороны `LEFT` и `RIGHT`) или полностью (для `INNER` и `FULL`) отключает декартово произведение для стандартных типов `JOIN`.
`ASOF JOIN`, `LEFT ASOF JOIN`	Объединение последовательностей с неточным совпадением. Использование `ASOF JOIN` описано ниже.
`PASTE JOIN`	Выполняет горизонтальную конкатенацию двух таблиц.

При использовании анализатора отключение настройки semi_join_include_columns_from_both_sides или anti_join_include_columns_from_both_sides ограничивает доступ к столбцам соответствующего JOIN только сохраняемой стороной для выражений, разрешаемых после формирования результата JOIN.

LEFT SEMI JOIN и LEFT ANTI JOIN предоставляют доступ только к столбцам левой стороны.
RIGHT SEMI JOIN и RIGHT ANTI JOIN предоставляют доступ только к столбцам правой стороны.
Это влияет на такие секции, как SELECT, PREWHERE, WHERE, GROUP BY, HAVING, QUALIFY, ORDER BY и LIMIT BY, включая подстановочные шаблоны с квалификатором, такие как t1.*.
Выражение ON того же JOIN по-прежнему может ссылаться на обе стороны.

Когда эти настройки включены (по умолчанию), ClickHouse сохраняет прежнее поведение: обе стороны остаются доступными, а SELECT * раскрывается в список столбцов обеих таблиц.

Когда для join_algorithm задано значение partial_merge, RIGHT JOIN и FULL JOIN поддерживаются только со строгостью ALL (SEMI, ANTI, ANY и ASOF не поддерживаются).

LATERAL JOIN

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

SELECT ...
FROM <left_table>
[INNER|LEFT] JOIN LATERAL (SELECT ... WHERE <expr referencing left_table>) AS <alias> ON true

Предикат ON true обязателен, как и для любого другого INNER или LEFT JOIN; его отсутствие — синтаксическая ошибка. Эта возможность экспериментальная и по умолчанию отключена; включите её с помощью настройки allow_experimental_lateral_join. Пока поддерживаются только перечисленные ниже возможности; всё остальное вызывает ошибку:

Только INNER JOIN LATERAL и LEFT JOIN LATERAL; JOIN типов RIGHT, FULL, PASTE и NATURAL не поддерживаются, а LATERAL нельзя сочетать с CROSS или JOIN через запятую.
Только строгость ALL, используемая по умолчанию; ANY, SEMI, ANTI и ASOF не поддерживаются.
Допускается только предикат JOIN ON true (также принимается ON 1); USING не поддерживается, и предикат нельзя опускать. Разместите фильтры, связывающие обе стороны JOIN, в предложении WHERE латерального подзапроса.
Модификаторы JOIN GLOBAL и LOCAL не поддерживаются.
Латеральный подзапрос должен ссылаться хотя бы на один столбец левой стороны. Для некоррелированного подзапроса используйте обычный JOIN.
Латеральный подзапрос выполняется один раз для каждого уникального сочетания значений столбцов левой стороны, на которые он ссылается, а не для каждой строки левой стороны, поэтому он не должен содержать функции, недетерминированные в пределах запроса, такие как rand или generateUUIDv4, либо табличные функции, генерирующие случайные строки, такие как generateRandom. Функции, значение которых постоянно в пределах запроса, такие как now, допускаются.
В качестве латерального табличного выражения поддерживается только подзапрос. Формы записи источника данных PostgreSQL LATERAL unnest(...) и CROSS JOIN UNNEST(...) не поддерживаются — вместо них используйте секцию ARRAY JOIN.
GROUP BY и ORDER BY латерального подзапроса выполняются один раз над совокупными результатами всех его выполнений, поэтому ограничения max_rows_to_group_by, max_rows_to_sort и max_bytes_to_sort учитывают строки всех выполнений, а не одного. Они поддерживаются только с режимом переполнения throw; any и break вызывают ошибку.
Строки, полученные при всех выполнениях подзапроса, сопоставляются со строками левой стороны с помощью одного JOIN. Как и для любого hash join, max_rows_in_join и max_bytes_in_join ограничивают объём данных на стороне JOIN, которая хранится в памяти, а планировщик выбирает эту сторону: при настройках по умолчанию (correlated_subqueries_use_in_memory_buffer = 1) это левая сторона JOIN LATERAL, поскольку её строки необходимо полностью прочитать до выполнения латерального подзапроса; в противном случае в памяти могут храниться совокупные результаты всех выполнений подзапроса. Ограничения всегда применяются так, как если бы join_overflow_mode имел значение throw: при break JOIN без предупреждения отбрасывал бы строки левой стороны, не связанные с переполнением.

Пример

SELECT u.id, o.total
FROM users AS u
LEFT JOIN LATERAL
(
    SELECT total
    FROM orders
    WHERE orders.user_id = u.id
    ORDER BY total DESC
    LIMIT 1
) AS o ON true
SETTINGS allow_experimental_lateral_join = 1;

Настройки

Тип JOIN по умолчанию можно переопределить с помощью настройки join_default_strictness. Поведение сервера ClickHouse для операций ANY JOIN зависит от настройки 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

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

ссылаться как на левую, так и на правую таблицу
использовать оператор равенства

Другие условия могут использовать иные операторы и ссылаться на левую или правую таблицу запроса либо на обе сразу. Строки объединяются, если выполняется всё составное условие. Если условия не выполняются, строки всё равно могут попасть в результат в зависимости от типа JOIN. Обратите внимание: если поместить те же условия в раздел WHERE и они не выполняются, то строки всегда отфильтровываются из результата. Оператор OR в условии ON работает на основе алгоритма hash join — для каждого аргумента OR, содержащего ключи JOIN, создаётся отдельная хеш-таблица, поэтому расход памяти и время выполнения запроса растут линейно по мере увеличения числа выражений OR в условии ON.

Ключом JOIN условие делает только оператор равенства (=). Другие операторы между столбцами разных таблиц поддерживаются, однако если равенства нет, JOIN вынужден перебирать все пары строк и работает значительно медленнее; см. JOIN с произвольным условием 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:

Query

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.

Response

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

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

Query

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

Response

┌─name─┬─text───┬─scores─┐
│ B    │ Text B │     15 │
└──────┴────────┴────────┘

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

Query

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;

Response

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

Запрос с типом JOIN 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, также поддерживается; подробнее см. в разделах ниже.

Query

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

Response

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

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

ClickHouse поддерживает ALL/ANY/SEMI/ANTI INNER/LEFT/RIGHT/FULL JOIN не только с условиями равенства, но и с условиями неравенства. Если секция ON наряду с неравенством содержит равенство между двумя таблицами, это равенство служит ключом JOIN, а неравенство проверяется для строк, сопоставленных по этому ключу. Такое смешанное условие выполняется только алгоритмами JOIN hash, parallel_hash и grace_hash. Если секция ON не содержит равенства между двумя таблицами, ключа для сопоставления нет. Такой JOIN выполняется алгоритмом ie_join, если условие представляет собой пару неравенств, а в остальных случаях — как блочный JOIN с вложенными циклами. Условие, вычисляемое во время JOIN, не может содержать arrayJoin, поскольку оно должно сохранять количество строк; вместо этого используйте ARRAY JOIN в подзапросе. Пример Таблица 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

JOIN с произвольным условием ON

Секция ON может содержать произвольное булево выражение над столбцами обеих таблиц, например проверку вхождения в диапазон, арифметическое сравнение или вызов скалярной функции. Если в ней нет условия равенства между столбцами двух таблиц, ключ JOIN для сопоставления строк отсутствует. Пара условий неравенства обрабатывается алгоритмом ie_join, если он указан в join_algorithm, как это настроено по умолчанию. Для любого другого условия используется блочный JOIN с вложенными циклами: правая таблица материализуется, а условие проверяется для каждой пары строк. ALL INNER JOIN в этом случае эквивалентен CROSS JOIN с этим условием в качестве фильтра. Блочный JOIN с вложенными циклами поддерживает все типы JOIN и варианты строгости, кроме ASOF JOIN, PASTE JOIN и ANY FULL JOIN. Он не входит в число значений join_algorithm и используется лишь в крайнем случае, когда ни один другой алгоритм не может обработать условие. В выводе EXPLAIN он отображается как шаг BlockNestedLoopJoin. Если allow_block_nested_loop_join отключён, запрос, которому необходим этот алгоритм, отклоняется с ошибкой INVALID_JOIN_ON_EXPRESSION. Пример

Query

CREATE TABLE orders (id UInt32, amount UInt32) ENGINE = Memory;
CREATE TABLE discounts (min_amount UInt32, max_amount UInt32, pct UInt32) ENGINE = Memory;

INSERT INTO orders VALUES (1, 50), (2, 150), (3, 300);
INSERT INTO discounts VALUES (100, 199, 5), (200, 1000, 10);

SELECT o.id, o.amount, d.pct
FROM orders AS o
LEFT JOIN discounts AS d ON o.amount BETWEEN d.min_amount AND d.max_amount
ORDER BY o.id;

Response

┌─id─┬─amount─┬─pct─┐
│  1 │     50 │   0 │
│  2 │    150 │   5 │
│  3 │    300 │  10 │
└────┴────────┴─────┘

Строка, для которой не нашлось соответствия, дополняется в соответствии с join_use_nulls, как и в любом другом JOIN: значением по умолчанию, как в примере выше, или NULL, если этот параметр включён. Строгость ANY и SEMI обычно оставляют одну строку на каждую группу строк с общим ключом JOIN. Здесь таких групп нет, поэтому они оставляют одну строку на каждую строку ведущей таблицы JOIN: LEFT ANY и LEFT SEMI выводят каждую левую строку не более одного раза, RIGHT ANY и RIGHT SEMI — каждую правую строку не более одного раза. ANY INNER ограничивает обе стороны сразу: каждая строка любой из таблиц используется не более одного раза, поэтому в результате строк не больше, чем в меньшей из таблиц. Какие именно пары войдут в такой результат, не определено — ровно так, как и подразумевает ANY, — и он может различаться между двумя запусками одного и того же запроса. Для ANY INNER это касается и количества строк: при жадном сопоставлении строк обеих сторон в том порядке, в котором они просматриваются, без пары может остаться разное число строк, поэтому результат count() для JOIN ANY INNER без ключа JOIN невоспроизводим и зависит от количества потоков и физического порядка строк в правой таблице. Производительность Просматривается каждая пара строк, поэтому объём работы растёт пропорционально произведению количества строк двух таблиц, а не их сумме. Из-за этого блочный JOIN с вложенными циклами на порядки дороже hash join на тех же данных, и разрыв увеличивается с ростом таблиц. Если запрос можно записать хотя бы с одним условием равенства в секции ON, сделайте это. JOIN типов LEFT ANY, ANY INNER, LEFT SEMI и LEFT ANTI прекращают сканирование правой таблицы, как только для левой строки найдена первая подходящая строка, поэтому обычно они обходятся дешевле соответствующего JOIN ALL. Их аналоги с ведущей правой таблицей (RIGHT ANY, RIGHT SEMI, RIGHT ANTI) просматривают все пары, поскольку результат зависит от того, для каких правых строк нашлось соответствие. join_any_take_last_row здесь не действует: без ключа JOIN нет группы подходящих строк, из которой можно было бы взять последнюю. Объём используемой памяти ограничен размером материализованной правой таблицы и не растёт с увеличением результата. На правую таблицу распространяются те же настройки, что и в других алгоритмах JOIN (они перечислены в разделе Ограничения памяти): её размер ограничивают max_rows_in_join, max_bytes_in_join и join_overflow_mode, а max_bytes_before_external_join включает сброс данных на диск.

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

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

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

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

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

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

┌─a─┬─t2.a─┐
│ 0 │    1 │
│ 1 │    0 │
└───┴──────┘

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

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;

┌─a─┬─t2.a─┐
│ 2 │    9 │
│ 3 │    8 │
└───┴──────┘
┌─a─┬─t2.a─┐
│ 0 │    7 │
│ 1 │    6 │
└───┴──────┘
┌─a─┬─t2.a─┐
│ 4 │    5 │
└───┴──────┘

Распределенный JOIN

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

Примеры

Пример:

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 │
└───────────┴────────┴────────┘

Справочник по SQL

Типы данных

Движки

Функции

Форматы

Настройки

Системные таблицы

Озера данных

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

LATERAL JOIN

Настройки

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

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

JOIN с произвольным условием ON

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

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

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

Распределенный JOIN

Неявное преобразование типов

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

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

Синтаксис

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

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

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

Примеры

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

​LATERAL JOIN

​Настройки

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

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

​JOIN с произвольным условием ON

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

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

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

​Распределенный JOIN

​Неявное преобразование типов

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

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

​Синтаксис

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

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

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

​Примеры

​Связанные материалы

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

LATERAL JOIN

Настройки

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

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

JOIN с произвольным условием ON

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

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

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

Распределенный JOIN

Неявное преобразование типов

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

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

Синтаксис

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

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

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

Примеры

Связанные материалы