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
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
|
# Операции с результатами подзапросов: UNION, INTERSECT, EXCEPT
## Объединение (UNION) {#union}
Объединение результатов нескольких `SELECT` (или подзапросов) с удалением дубликатов.
Поведение идентично последовательному исполнению `UNION ALL` и `SELECT DISTINCT *`.
См. [UNION ALL](#union-all) для информации о деталях поведения.
```yql
SELECT key FROM T1
UNION
SELECT key FROM T2;
```
Также допускается явное указание ключевого слова `DISTINCT`, которое не влияет на результат выполнения.
```yql
SELECT key FROM T1
UNION DISTINCT
SELECT key FROM T2;
```
#### Пример
```yql
SELECT * FROM (VALUES (1, 2)) AS t(x, y)
UNION
SELECT * FROM (VALUES (1, 2)) AS t(x, y);
```
```
x y
1 2
```
## Объединение c дубликатами (UNION ALL) {#union-all}
Объединение результатов нескольких `SELECT` (или подзапросов) без удаления дубликатов.
```yql
SELECT key FROM T1
UNION ALL
SELECT key FROM T2;
```
#### Пример
```yql
SELECT * FROM (VALUES (1, 2)) AS t(x, y)
UNION ALL
SELECT * FROM (VALUES (1, 2)) AS t(x, y);
```
```
x y
1 2
1 2
```
## Пересечение (INTERSECT) {#intersect}
Пересечение результатов двух `SELECT` (или подзапросов) с удалением дубликатов.
Поведение идентично последовательному исполнению `INTERSECT ALL` и `SELECT DISTINCT *`.
См. [INTERSECT ALL](#intersect-all) для информации о деталях поведения.
```yql
SELECT key FROM T1
INTERSECT
SELECT key FROM T2;
```
Также допускается явное указание ключевого слова `DISTINCT`, которое не влияет на результат выполнения.
```yql
SELECT key FROM T1
INTERSECT DISTINCT
SELECT key FROM T2;
```
#### Пример
```yql
SELECT * FROM (VALUES (1), (1), (1), (2)) AS t(x)
INTERSECT
SELECT * FROM (VALUES (1), (1)) AS t(x);
```
```
x
1
```
## Пересечение с дубликатами (INTERSECT ALL) {#intersect-all}
Пересечение результатов двух `SELECT` (или подзапросов) без удаления дубликатов.
```yql
SELECT key FROM T1
INTERSECT ALL
SELECT key FROM T2;
```
Результатом запроса является таблица дублирующихся строк, лежащих в обеих исходных таблицах.
Количество дубликатов строк равно минимуму из вхождений в каждую из исходных таблиц.
#### Пример
```yql
SELECT * FROM (VALUES (1), (1), (1), (2)) AS t(x)
INTERSECT ALL
SELECT * FROM (VALUES (1), (1)) AS t(x);
```
```
x
1
1
```
## Исключение (EXCEPT) {#except}
Исключение результатов одного `SELECT` (или подзапроса) из результатов другого.
```yql
SELECT key FROM T1
EXCEPT
SELECT key FROM T2;
```
Также допускается явное указание ключевого слова `DISTINCT`, которое не влияет на результат выполнения.
```yql
SELECT key FROM T1
EXCEPT DISTINCT
SELECT key FROM T2;
```
Результатом запроса является таблица уникальных строк, лежащих в первой исходной таблице и не лежащих во второй.
#### Пример
```yql
SELECT * FROM (VALUES (1), (1), (1), (2)) AS t(x)
EXCEPT
SELECT * FROM (VALUES (1)) AS t(x);
```
```
x
2
```
## Исключение с дубликатами (EXCEPT ALL) {#except-all}
Исключение результатов одного `SELECT` (или подзапроса) из результатов другого с учётом дубликатов (количества вхождений).
```yql
SELECT key FROM T1
EXCEPT ALL
SELECT key FROM T2;
```
Результатом запроса является таблица дублирующихся строк, которые встречаются в первой таблице чаще, чем во второй.
Количество дубликатов строк равно разности количества вхождений в первую и количества вхождений во вторую.
#### Пример
```yql
SELECT * FROM (VALUES (1), (1), (1), (2)) AS t(x)
EXCEPT ALL
SELECT * FROM (VALUES (1)) AS t(x);
```
```
x
2
1
1
```
## Режимы "по именам" и "по позициям" {#positional-mode}
Поддерживаются два режима выполнения вышеперечисленных операций – по именам колонок (режим по умолчанию) и по позициям колонок (соответствует стандарту ANSI SQL и включается через соответствующую [PRAGMA](../pragma/global.md#positionalunionall)).
### По именам колонок
В режиме "по именам" результирующая схема данных выводится по следующим правилам:
* в результирующую таблицу включаются все колонки, которые встречались хоть в одной из входных таблиц;
* если колонка присутствовала не во всех входных таблицах, то ей автоматически присваивается [опциональный тип данных](../../types/optional.md) (допускающий значение `NULL`);
* если колонка в разных входных таблицах имела разные типы, то выводится общий тип (наиболее широкий);
* если колонка в разных входных таблицах имела разнородный тип, например строку и число, то это считается ошибкой.
Порядок выходных колонок в этом режиме выводится как наибольший общий префикс порядка входов, после чего следуют все остальные колонки в алфавитном порядке.
Если наибольший общий префикс пуст (в том числе и из-за отсутствия порядка на одном из входов), то порядок выхода не определен.
#### Пример
В результате выполнения данного запроса по умолчанию, в режиме "по именам", будет сформирована выборка с тремя колонками `x`, `y`, и `z`:
```yql
SELECT 1 AS x
UNION ALL
SELECT 2 AS y
UNION ALL
SELECT 3 AS z;
```
```
x y z
1
2
3
```
### По позициям колонок
В режиме "по позициям" результирующая схема данных выводится по следующим правилам:
* число колонок во всех входах должно быть одинаковым;
* порядок колонок во всех входах должен быть определен;
* имена результирующих колонок совпадают с именами колонок первой таблицы;
* тип результирующих колонок выводится как общий (наиболее широкий) тип из типов входных колонок стоящих на одинаковых позициях.
Порядок выходных колонок в этом режиме совпадает с порядком колонок первого входа.
#### Пример
При включенной `PRAGMA PositionalUnionAll;` в следующем запросе результатом будет одна колонка `x`:
```yql
PRAGMA PositionalUnionAll;
SELECT 1 AS x
UNION ALL
SELECT 2 AS y
UNION ALL
SELECT 3 AS z;
```
```
x
1
2
3
```
Если порядок колонок не определён (например, при использовании `AS_TABLE`), запрос завершается с ошибкой:
```yql
PRAGMA PositionalUnionAll;
SELECT 1 AS x, 2 as y
UNION ALL
SELECT * FROM AS_TABLE([<|x:3, y:4|>]);
```
```
Input #1 does not have ordered columns...
```
|
