ydb/yql/essentials/docs/ru/syntax/join.md

JOIN

Позволяет объединить несколько источников данных (подзапросов или таблиц) по равенству значений указанных столбцов или выражений (ключей JOIN).

Синтаксис

SELECT ...    FROM table_1
-- первый шаг объединения:
  <Join_Type> JOIN table_2 <Join_Condition>
  -- исходная выборка -- записи в таблице table_1
  -- присоединяемая выборка -- записи в таблице table_2
-- следующий шаг объединения:
  <Join_Type> JOIN table_n <Join_Condition>
  -- исходная выборка -- результат объединения на предыдущем шаге
  -- присоединяемая выборка -- записи в таблице 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 — обе стороны минус пересечение.

{% note info %}

NULL является особым значением, которое ничему не равно. Таким образом, NULL с двух сторон не считаются равными друг другу. Это избавляет от неоднозначности в некоторых типах JOIN, а также от гигантского декартового произведения, которое часто возникает в противном случае.

{% endnote %}

Условия объединения (Join_Condition)

Для CROSS JOIN условие объединения не указывается. В результат попадет декартово произведение исходной и присоединяемой выборок, то есть сочетание всех со всеми. Количество строк в результирующей выборке будет произведением количества строк исходной и присоединяемой выборок.

Для любых других типов объединения необходимо указать условие одним из двух способов:

1. USING (column_name). Используется при наличии в исходной и присоединяемой выборках одноименной колонки, равенство значений в которой является условием объединения.
2. ON (equality_conditions). Позволяет задать условие равенства значений колонок или выражений над колонками исходной и присоединяемой выборок, или несколько таких условий, объединенных по and.

Примеры

SELECT    a.value as a_value, b.value as b_value
FROM      a_table AS a
FULL JOIN b_table AS b USING (key);

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;

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.

Например, создание индекса для использования в условии соединения:

ALTER TABLE b_table ADD INDEX b_index_ref GLOBAL ON(ref);

Использование созданного индекса:

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, где ANY пишется перед типом JOIN и работает только для правой стороны.

Запрос

$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"