1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
|
# JOIN
Позволяет объединить несколько источников данных (подзапросов или таблиц) по равенству значений указанных столбцов или выражений (ключей `JOIN`).
## Синтаксис
```yql
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` <span style="color: gray;">(по умолчанию)</span> — строки объединяемых выборок, для которых не найдено соответствие ни с одной строкой с другой стороны, не попадут в результат.
* `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`.
#### Примеры
```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"|
|
