Операции с результатами подзапросов: UNION, INTERSECT, EXCEPT
Объединение (UNION)
Объединение результатов нескольких SELECT (или подзапросов) с удалением дубликатов.
Поведение идентично последовательному исполнению UNION ALL и SELECT DISTINCT *.
См. UNION ALL для информации о деталях поведения.
SELECT key FROM T1
UNION
SELECT key FROM T2;
Также допускается явное указание ключевого слова DISTINCT, которое не влияет на результат выполнения.
SELECT key FROM T1
UNION DISTINCT
SELECT key FROM T2;
Пример
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)
Объединение результатов нескольких SELECT (или подзапросов) без удаления дубликатов.
SELECT key FROM T1
UNION ALL
SELECT key FROM T2;
Пример
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)
Пересечение результатов двух SELECT (или подзапросов) с удалением дубликатов.
Поведение идентично последовательному исполнению INTERSECT ALL и SELECT DISTINCT *.
См. INTERSECT ALL для информации о деталях поведения.
SELECT key FROM T1
INTERSECT
SELECT key FROM T2;
Также допускается явное указание ключевого слова DISTINCT, которое не влияет на результат выполнения.
SELECT key FROM T1
INTERSECT DISTINCT
SELECT key FROM T2;
Пример
SELECT * FROM (VALUES (1), (1), (1), (2)) AS t(x)
INTERSECT
SELECT * FROM (VALUES (1), (1)) AS t(x);
x
1
Пересечение с дубликатами (INTERSECT ALL)
Пересечение результатов двух SELECT (или подзапросов) без удаления дубликатов.
SELECT key FROM T1
INTERSECT ALL
SELECT key FROM T2;
Результатом запроса является таблица дублирующихся строк, лежащих в обеих исходных таблицах.
Количество дубликатов строк равно минимуму из вхождений в каждую из исходных таблиц.
Пример
SELECT * FROM (VALUES (1), (1), (1), (2)) AS t(x)
INTERSECT ALL
SELECT * FROM (VALUES (1), (1)) AS t(x);
x
1
1
Исключение (EXCEPT)
Исключение результатов одного SELECT (или подзапроса) из результатов другого.
SELECT key FROM T1
EXCEPT
SELECT key FROM T2;
Также допускается явное указание ключевого слова DISTINCT, которое не влияет на результат выполнения.
SELECT key FROM T1
EXCEPT DISTINCT
SELECT key FROM T2;
Результатом запроса является таблица уникальных строк, лежащих в первой исходной таблице и не лежащих во второй.
Пример
SELECT * FROM (VALUES (1), (1), (1), (2)) AS t(x)
EXCEPT
SELECT * FROM (VALUES (1)) AS t(x);
x
2
Исключение с дубликатами (EXCEPT ALL)
Исключение результатов одного SELECT (или подзапроса) из результатов другого с учётом дубликатов (количества вхождений).
SELECT key FROM T1
EXCEPT ALL
SELECT key FROM T2;
Результатом запроса является таблица дублирующихся строк, которые встречаются в первой таблице чаще, чем во второй.
Количество дубликатов строк равно разности количества вхождений в первую и количества вхождений во вторую.
Пример
SELECT * FROM (VALUES (1), (1), (1), (2)) AS t(x)
EXCEPT ALL
SELECT * FROM (VALUES (1)) AS t(x);
x
2
1
1
Режимы "по именам" и "по позициям"
Поддерживаются два режима выполнения вышеперечисленных операций – по именам колонок (режим по умолчанию) и по позициям колонок (соответствует стандарту ANSI SQL и включается через соответствующую PRAGMA).
По именам колонок
В режиме "по именам" результирующая схема данных выводится по следующим правилам:
- в результирующую таблицу включаются все колонки, которые встречались хоть в одной из входных таблиц;
- если колонка присутствовала не во всех входных таблицах, то ей автоматически присваивается опциональный тип данных (допускающий значение
NULL); - если колонка в разных входных таблицах имела разные типы, то выводится общий тип (наиболее широкий);
- если колонка в разных входных таблицах имела разнородный тип, например строку и число, то это считается ошибкой.
Порядок выходных колонок в этом режиме выводится как наибольший общий префикс порядка входов, после чего следуют все остальные колонки в алфавитном порядке.
Если наибольший общий префикс пуст (в том числе и из-за отсутствия порядка на одном из входов), то порядок выхода не определен.
Пример
В результате выполнения данного запроса по умолчанию, в режиме "по именам", будет сформирована выборка с тремя колонками x, y, и z:
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:
PRAGMA PositionalUnionAll;
SELECT 1 AS x
UNION ALL
SELECT 2 AS y
UNION ALL
SELECT 3 AS z;
x
1
2
3
Если порядок колонок не определён (например, при использовании AS_TABLE), запрос завершается с ошибкой:
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...