--- metadata: - name: generator content: Diplodoc Platform v5.50.6 alternate: - https://ytsaurus.tech/docs/en/yql/syntax/join.md - https://ytsaurus.tech/docs/ru/yql/syntax/join.md --- > **Documentation Index:** Fetch the complete configuration index at https://ytsaurus.tech/docs/ru/llms.txt # JOIN Позволяет объединить несколько источников данных (подзапросов или таблиц) по равенству значений указанных столбцов или выражений (ключей `JOIN`). ## Синтаксис ```yql SELECT ... FROM table_1 -- первый шаг объединения: JOIN table_2 -- исходная выборка -- записи в таблице table_1 -- присоединяемая выборка -- записи в таблице table_2 -- следующий шаг объединения: JOIN table_n -- исходная выборка -- результат объединения на предыдущем шаге -- присоединяемая выборка -- записи в таблице table_n -- могут быть следующие шаги объединения ... WHERE ... ``` На каждом шаге объединения по заданным правилам определяются соответствия между строками исходной и присоединяемой выборок данных, и формируется новая выборка, в которую попадают все сочетания подошедших под условия объединения строк. {% note warning "Внимание" %} Так как колонки в YQL идентифицируются по именам и в выборке не может быть двух колонок с одинаковыми именами, `SELECT * FROM ... JOIN ...` не может быть исполнен при наличии колонок с одинаковыми именами в объединяемых таблицах. {% endnote %} ## Типы объединения (Join_Type) * `INNER` (по умолчанию) — строки объединяемых выборок, для которых не найдено соответствие ни с одной строкой с другой стороны, не попадут в результат. * `LEFT` — при отсутствии значения в присоединяемой выборке включает строку в результат со значениями колонок из исходной выборки, оставляя пустыми (`NULL`) колонки присоединяемой выборки. * `RIGHT` — при отсутствии значения в исходной выборке включает строку в результат со значениям колонок из присоединяемой выборки, оставляя пустыми (`NULL`) колонки исходной выборки. * `FULL` = `LEFT` + `RIGHT` * `LEFT/RIGHT SEMI` — одна сторона выступает как белый список (whitelist) ключей, её значения недоступны. В результат включаются столбцы только из одной таблицы, декартового произведения не возникает. * `LEFT/RIGHT ONLY` — вычитание множеств по ключам (blacklist). Практически эквивалентно добавлению условия `IS NULL` на ключ противоположной стороны в обычном `LEFT/RIGHT`, но, как и в `SEMI`, нет доступа к значениям. * `CROSS` — декартово произведение двух таблиц целиком без указания ключевых колонок, секция с `ON/USING` явно не пишется. * `EXCLUSION` — обе стороны минус пересечение. ![JOIN](_assets/join-YQL-06.png) {% note info %} `NULL` является особым значением, которое ничему не равно. Таким образом, значения `NULL` с двух сторон не считаются равными друг другу. Это избавляет от неоднозначности в некоторых типах `JOIN`, а также от гигантского декартового произведения, которое часто возникает в противном случае. {% endnote %} ## Условия объединения (Join_Condition) Для `CROSS JOIN` условие объединения не указывается. В результат попадёт декартово произведение исходной и присоединяемой выборок, то есть сочетание всех со всеми. Количество строк в результирующей выборке будет произведением количества строк исходной и присоединяемой выборок. Для любых других типов объединения необходимо указать условие одним из двух способов: 1. `USING (column_name)`. Используется при наличии в исходной и присоединяемой выборках одноименной колонки, равенство значений в которой является условием объединения. 2. `ON (equality_conditions)`. Позволяет задать условие равенства значений колонок или выражений над колонками исходной и присоединяемой выборок, или несколько таких условий, объединенных по `and`. #### Примеры ```yql SELECT a.value as a_value, b.value as b_value FROM a_table AS a FULL JOIN b_table AS b USING (key); ``` ```yql SELECT a.value as a_value, b.value as b_value FROM a_table AS a FULL JOIN b_table AS b ON a.key = b.key; ``` ```yql SELECT a.value as a_value, b.value as b_value, c.column2 FROM a_table AS a CROSS JOIN b_table AS b LEFT JOIN c_table AS c ON c.ref = a.key and c.column1 = b.value; ``` Для исключения необходимости в полном сканировании правой присоединяемой таблицы может использоваться вторичный индекс над колонками, входящими в условие соединения. Обращение ко вторичному индексу должно быть указано в явном виде, в формате `JOIN table_name VIEW index_name AS table_alias`. Например, создание индекса для использования в условии соединения: ```yql ALTER TABLE b_table ADD INDEX b_index_ref GLOBAL ON(ref); ``` Использование созданного индекса: ```yql SELECT a.value as a_value, b.value as b_value FROM a_table AS a INNER JOIN b_table VIEW b_index_ref AS b ON a.ref = b.ref; ``` Если в выражении помимо `JOIN` выполняется фильтрация данных, то рекомендуется обернуть те условия, про которые известно, что они вернут `true` для большинства строк, в вызов функции `LIKELY(...)`. Если предположение о преобладании положительных значений в условии окажется верно, такая подсказка может положительно сказаться на времени выполнения запроса. Также `LIKELY` может быть полезен в том случае, когда вычисление предиката ресурсоёмко и при этом сам JOIN значительно сокращает число строк. Перед любым источником данных для `JOIN` можно указать ключевое слово `ANY`, которое служит для подавления дубликатов по ключам `JOIN` с соответствующей стороны. В этом случае из множества строк с одинаковым значением ключей `JOIN` остается только одна (не уточняется какая именно – отсюда и название `ANY`). Данный синтаксис отличается от принятого в [ClickHouse](https://clickhouse.tech/docs/ru/sql-reference/statements/select/join/), где `ANY` пишется перед типом `JOIN` и работает только для правой стороны. Запрос ```yql $t1 = AsList( AsStruct("1" AS key, "v111" AS value), AsStruct("2" AS key, "v121" AS value), AsStruct("2" AS key, "v122" AS value), AsStruct("3" AS key, "v131" AS value), AsStruct("3" AS key, "v132" AS value)); $t2 = AsList( AsStruct("2" AS key, "v221" AS value), AsStruct("2" AS key, "v222" AS value), AsStruct("3" AS key, "v231" AS value), AsStruct("3" AS key, "v232" AS value), AsStruct("4" AS key, "v241" AS value)); SELECT a.key, a.value, b.value FROM ANY AS_TABLE($t1) AS a JOIN ANY AS_TABLE($t2) AS b ON a.key == b.key; ``` выдаст: |a.key|a.value|b.value| | --- | --- | --- | |"3"|"v131"|"v231"| |"2"|"v121"|"v221"| а без `ANY` выдал бы: |a.key|a.value|b.value| | --- | --- | --- | |"3"|"v131"|"v231"| |"3"|"v131"|"v232"| |"3"|"v132"|"v231"| |"3"|"v132"|"v232"| |"2"|"v121"|"v221"| |"2"|"v121"|"v222"| |"2"|"v122"|"v221"| |"2"|"v122"|"v222"| ## Стратегии выполнения JOIN в YTsaurus ### Введение В стандартном SQL поддерживается следующий синтаксис `JOIN`: ``` yql SELECT ... FROM T1 JOIN T2 ON F(T1, T2); ``` где `F(T1, T2)` — произвольный предикат, зависящий от колонок обоих таблиц `T1, T2`. В YQL поддерживается только частный случай — когда предикат `F` сепарабельный, т. е. имеет следующий вид: ``` yql SELECT ... FROM T1 JOIN T2 ON F1(T1) = G1(T2) AND F2(T1) = G2(T2) AND ...; ``` Такая структура предиката позволяет эффективно реализовать `JOIN` в рамках концепции map-reduce. В YQL, так же как и в стандартном SQL, можно сделать несколько `JOIN` в одном SELECT: ``` yql SELECT ... FROM T1 JOIN T2 ON F1(T1) = G1(T2) AND F2(T1) = G2(T2) AND ... JOIN T3 ON H1(T1,T2) = J1(T3) AND H2(T1,T2) = J2(T3) AND ...; ``` В настоящий момент такие `JOIN` выполняются последовательно и именно в том порядка, в котором написано в запросе. Единственным исключением является стратегия StarJoin. ### Вычисление ключей JOIN Выполнение `JOIN` начинается с вычислениия ключей и сохранения их значений в отдельную колонку. Такое преобразование происходит ещё на уровне SQL парсера и является общим для всех бэкендов (YTsaurus, DQ и т. п.) Фактически, запрос ``` yql SELECT ... FROM T1 JOIN T2 ON F1(T1) = G1(T2) AND F2(T1) = G2(T2) AND ...; ``` преобразуется в: ``` yql SELECT ... FROM ( SELECT t.*, F1(...) as _yql_join_key1, F2(...) as _yql_join_key2, ... FROM T1 as t ) as t1 JOIN ( SELECT t.*, G1(...) as _yql_join_key1, G2(...) as _yql_join_key2, ... FROM T2 as t ) as t2 ON t1._yql_join_key1 = t2._yql_join_key1 AND t1._yql_join_key2 = t2._yql_join_key2 AND ...; ``` ### Приведение ключей к простому общему типу Данная стадия уже является специфичной для YTsaurus. В ней ключевые колонки с обоих сторон приводятся к одинаковому простому типу. Запрос ``` yql SELECT ... FROM T1 as t1 JOIN T2 as t2 ON t1.key1 = t2.key1 AND t1.key2 = t2.key2 AND ...; ``` преобразуется в: ``` yql SELECT ... FROM ( SELECT t.*, CastToCommonKeyType(key1) as _yql_join_key1, CastToCommonKeyType(key2) as _yql_join_key2, FROM T1 as t ) as t1 JOIN ( SELECT t.*, CastToCommonKeyType(key1) as _yql_join_key1, CastToCommonKeyType(key2) as _yql_join_key2, FROM T2 as t ) as t2 ON t1._yql_join_key1 = t2._yql_join_key1 AND t1._yql_join_key2 = t2._yql_join_key2 AND ...; ``` Приведение к общему типу необходимо для корректной работы map-reduce по ключам родственных, но разных типов. Например, для ключей типа `Int32` и `Uint32`, общим типом будет `Optional`. Если конвертацию в общий тип не сделать и оставить исходные колонки в качестве ключевых для map-reduce операций, то YTsaurus будет рассматривать ключи -1 и 4294967295 как равные. Такая конвертация нужна не всегда — например, ключи типов Int32 и Optional работают корректно. Дополнительно, ключи сложного типа (всё, что сложнее Optional от [простого типа](https://ytsaurus.tech/docs/ru/yql/types/primitive.md))) после каста в общий тип ещё и конвертируются в строку: ```yql if ( YQL::HasNulls(casted_key), -- если где-то в ключе встречается null null, -- то конвертируем значение в null строкового типа (null в SQL не равен никакому значению, в том числе самому себе) StablePickle(casted_key), -- иначе сериализуем значение в строковое представление ) ``` Такая конвертация нужна, поскольку ключи сложных типов не поддерживаются в качестве ключей reduce операций в YTsaurus. Таким образом, после всех конвертаций мы получаем c обоих сторон `JOIN` ключи попарно одинакового простого типа (с точностью до Optional). ### Базовая стратегия JOIN (aka CommonJoin) Базовая стратегия `JOIN` выбирается в тех случаях, когда не удаётся применить по каким-либо причинам остальные стратегии `JOIN`. Эта стратегия поддерживает все типы `JOIN`, включая `CROSS JOIN` и реализуется через одну MapReduce операцию. При этом в map стадии происходит: 1) Приведение ключей к простому общему типу. 2) При наличии модификатора `ANY` одинаковые ключи "прореживаются" отдельным оконным фильтром – на окне некоторого размера (сотни мегабайт) детектятся строки с одинаковыми ключами и дубликаты отфильтровываются. 3) Обработка нуллов в ключах. При этом для `INNER JOIN` нуллы с обоих сторон отфильтровываются, а для `LEFT/RIGHT/FULL JOIN` строки с нулевыми ключами идут в отдельную выходную таблицу сразу из map стадии. Из map стадии строки с одинаковым ключом попадают в одну YTsaurus reduce джобу, в которой и происходит `JOIN`. Если необходимо, результирующая таблица из reduce стадии объединяется с выходными таблцами из map стадии с помощью отдельной YTsaurus Merge операции. Чтобы выполнить `CROSS JOIN` (в котором ключей нет), на map стадии всем строчкам обоих входных таблиц назначается одинаковый ключ 0. ### Стратегия LookupJoin Данная стратегия срабатывает, когда одна из таблиц сортирована по ключам `JOIN`, а вторая имеет очень малый размер (меньше ~900 строк). {% note info %} Здесь и далее таблица называется _сортированной по ключам JOIN_, если список ключей `JOIN` является префиксом ключей сортировки для некоторого порядка ключей `JOIN`. Например, таблица с ключами `JOIN` `b, a` и сортировкой по `a, b, c` является сортированной по ключам `JOIN`. {% endnote %} LookupJoin поддерживается для следующих типов JOIN: * `INNER` (малая таблица может быть с произвольной стороны). * `LEFT SEMI` (малая таблица справа). * `RIGHT SEMI` (малая таблица слева). Стратегия LookupJoin реализуется через одну Map операцию по большой таблице, при этом малая таблица загружается в память. Типы ключей при этом не обязательно должны совпадать – ключи малой таблицы приводятся ([CAST](https://ytsaurus.tech/docs/ru/yql/syntax/expressions.md#cast)) к типу ключей в большой таблице. В отличие от стратегии MapJoin (смотрите ниже), в LookupJoin значения ключей `JOIN` из малой таблицы попадают в настройку `ranges` в YPath большой таблицы. Таким образом из большой таблицы вычитываются только строки с ключами `JOIN`, которые есть в малой таблице. LookupJoin является наиболее эффективной стратегией `JOIN`, но налагает самые жёсткие условия на типы `JOIN` (они должны быть "фильтрующими" по большой таблице) и на размер малой таблицы (ключи должны "влезать" в максимально допустимое число `ranges` в YPath). Кроме того, в LookupJoin не поддерживается `ANY` на стороне большой таблицы. Настройки (PRAGMA) для стратегии: | Название | Описание | | --- | --- | | [yt.LookupJoinLimit](pragma/yt.md#ytlookupjoinlimit) | Максимальный размер малой таблицы в байтах (не более 10М) | | [yt.LookupJoinMaxRows](pragma/yt.md#ytlookupjoinmaxrows) | Максимальный размер малой таблицы в строках (не более 1000)| Установка любого из этих значений в 0 приводит к отключению LookupJoin стратегии. ### Стратегия SortedJoin (aka MergeJoin) Данная стратегия срабатывает, когда обе таблицы сортированы по ключам `JOIN`. При этом ключи `JOIN` должны совпадать по типам с точностью до Optional на верхнем уровне. Если сортирована только одна таблица, а размер другой не превышает `yt.JoinMergeUnsortedFactor * <размер сортированной таблицы>`, стратегия SortedJoin также выбирается, при этом несортированная таблица сортируется отдельной YTsaurus операцией. Значение настройки [`yt.JoinMergeUnsortedFactor`](https://ytsaurus.tech/docs/ru/yql/syntax/pragma/yt.md#ytjoinmergeunsortedfactor) по умолчанию составляет `0.2`. Стратегия SortedJoin поддерживает все виды `JOIN` кроме `CROSS JOIN` и реализуется через одну операцию Reduce. При этом, по возможности используется режим reduce с внешними таблицами. Кроме того, при уникальности ключей `JOIN` дополнительно включается настройка `enable_key_guarantee = false`. Стратегию SortedJoin можно выбрать принудительно через [SQL хинт](https://ytsaurus.tech/docs/ru/yql/syntax/lexer.md#sql-hints): ```yql SELECT * FROM T1 AS a JOIN /*+ merge() */ T2 AS b ON a.key = b.key; ``` В этом случае (если необходимо): 1) Ключи `JOIN` будут приведены к общему типу. 2) Обе таблицы будут отсортированы по ключам `JOIN`. Настройки (PRAGMA) для стратегии: | Название | Описание | | --- | --- | | [`yt.JoinMergeUnsortedFactor`](pragma/yt.md#ytjoinmergeunsortedfactor) | Смотрите выше | | [`yt.JoinMergeTablesLimit`](pragma/yt.md#ytjoinmergetableslimit) | Максимальное количество таблиц на входе `JOIN` (при использовании [RANGE,CONCAT](select/concat.md) и т. п.) | | [`yt.JoinMergeUseSmallAsPrimary`](pragma/yt.md#ytjoinmergeusesmallasprimary) | Влияет на выбор primary таблицы при выполнении Reduce операции | | [`yt.JoinMergeForce`](pragma/yt.md#ytjoinmergeforce) | Форсирует выбор SortedJoin стратегии для всех `JOIN` в запросе | Установка `yt.JoinMergeTablesLimit` в 0 отключает стратегию SortedJoin. ### Стратегия MapJoin Данная стратегия срабатывает, если одна из входных таблиц достаточно маленькая (размером не более чем [`yt.MapJoinLimit`](https://ytsaurus.tech/docs/ru/yql/syntax/pragma/yt.md#ytmapjoinlimit)). При этом меньшая таблица загружается в память (в виде словаря по ключам `JOIN`), а затем производится Map по большой таблице. Данная стратегия поддерживает все виды `JOIN` (в том числе `CROSS`), но не выбирается, если имеется `ANY` на большей стороне. Уникальной особенностью MapJoin стратегии является возможность раннего выбора этой стратегии — т. е. когда малая входная таблица уже посчиталась и попадает под ограничения на размер, а большая таблица ещё не готова. В этом случае мы можем сразу выбрать MapJoin, причём есть шанс, что Map операция по большой таблице "склеится" с Map операцией (например, фильтром), которая эту большую таблицу готовит. Существует также шардированный вариант MapJoin: малая таблица разбивается на [`yt.MapJoinShardCount`](https://ytsaurus.tech/docs/ru/yql/syntax/pragma/yt.md#ytmapjoinshardcount) частей (каждая часть при этом не должна превышать `yt.MapJoinLimit`), каждая часть параллельно и независимо `JOIN`-ится с большой таблицей через Map операцию, и затем все полученные части объединяются через YTsaurus Merge. Шардированный MapJoin возможен только для некоторых типов `JOIN`: `INNER`,`CROSS`, `LEFT SEMI` при условии, что малая таблица справа уникальна. Настройки (PRAGMA) для стратегии: | Название | Описание | | --- | --- | | [`yt.MapJoinLimit`](pragma/yt.md#ytmapjoinlimit) | Максимальный размер представления в памяти меньшей стороны `JOIN`, при котором выбирается стратегия MapJoin | | [`yt.MapJoinShardCount`](pragma/yt.md#ytmapjoinshardcount) | Максимальное число шардов | | [`yt.MapJoinShardMinRows`](pragma/yt.md#ytmapjoinshardminrows) | Минимальное число строк в одном шарде | Установка `yt.MapJoinLimit` в 0 отключает стратегию MapJoin. ### Стратегия StarJoin Особенностью данной стратегии является то, что она позволяет выполнять сразу несколько последовательных `JOIN` через одну операцию Reduce. Стратегия возможна, когда к одной ("главной") таблице последовательно через `INNER JOIN` или `LEFT JOIN` присоединяются таблицы-словари, причём: 1) Из главной таблицы во всех запросах `JOIN` используются одинаковые ключи. 2) Все таблиицы сортированы по ключам `JOIN`. 3) Таблицы-словари ещё уникальны по ключам `JOIN`. Стратегию StarJoin можно выбрать принудительно через [SQL хинт](https://ytsaurus.tech/docs/ru/yql/syntax/lexer.md#sql-hints): ```yql SELECT ... FROM T1 AS a JOIN /*+ star() */ T2 AS b ON a.key = b.key JOIN T3 AS c ON a.key = c.key ``` При указании хинта: - в качестве главной таблицы форсируется _левая сторона_ JOIN, для которого указан хинт - вставляется дополнительная сортировка по ключам для всех входов, для которых это необходимо - в случаях, когда стратегия StarJoin неприменима - выводится предупреждение с причиной Настройки (PRAGMA) для стратегии: | Название | Описание | | --- | --- | | [`yt.JoinEnableStarJoin`](pragma/yt.md#ytjoinenablestarjoin) | Включает/отключает выбор стратегии StarJoin (включена по умолчанию)| ### Порядок выбора стратегий При выполнении `JOIN` запроса стратегии пробуются в определённом порядке, и выбирается первая подходящая. Порядок при этом следующий: 1) StarJoin 2) LookupJoin 3) OrderedJoin 4) MapJoin 5) CommonJoin (всегда возможна)