Операции над множествами

Операции над множествами позволяют комбинировать запросы в соответствии с семантикой операций над множествами. Операции над множествами относятся к условиям UNION [ALL], INTERSECT [ALL] и EXCEPT [ALL]. Стандартные варианты используют семантику множеств, то есть они устраняют дубликаты, в то время как варианты с ALL используют семантику мультимножеств.

Традиционные операции над множествами объединяют запросы по позиции столбцов и требуют, чтобы объединенные запросы имели одинаковое количество входных столбцов. Если столбцы имеют разные типы, могут быть добавлены преобразования типов. Результат будет использовать имена столбцов из первого запроса.

DuckDB также поддерживает UNION [ALL] BY NAME, который объединяет столбцы по имени вместо позиции. UNION BY NAME не требует, чтобы входные данные имели одинаковое количество столбцов. NULL значения будут добавлены в случае отсутствия столбцов.

`UNION`

Условие UNION может быть использовано для объединения строк из нескольких запросов. Запросы должны возвращать одинаковое количество столбцов. Неявное преобразование типов к одному из возвращаемых типов выполняется для объединения столбцов разных типов при необходимости. Если это невозможно, условие UNION выдает ошибку.

Стандартный `UNION` (Семантика множеств)

Стандартный UNION оператор следует семантике множеств, поэтому он выполняет удаление дубликатов, то есть в результате будут только уникальные строки.

SELECT * FROM range(2) t1(x)
UNION
SELECT * FROM range(3) t2(x);

x
2
1
0

`UNION ALL` (Семантика мультимножеств)

UNION ALL возвращает все строки обоих запросов, следуя семантике мультимножеств, то есть без удаления дубликатов.

SELECT * FROM range(2) t1(x)
UNION ALL
SELECT * FROM range(3) t2(x);

x
0
1
0
1
2

`UNION [ALL] BY NAME`

Условие UNION [ALL] BY NAME может быть использовано для объединения строк из разных таблиц по имени, а не по позиции. UNION BY NAME не требует, чтобы оба запроса имели одинаковое количество столбцов. Любые столбцы, которые найдены только в одном из запросов, заполняются NULL значениями для другого запроса.

Рассмотрим следующие таблицы, например:

CREATE TABLE capitals (city VARCHAR, country VARCHAR);
INSERT INTO capitals VALUES
    ('Amsterdam', 'NL'),
    ('Berlin', 'Germany');
CREATE TABLE weather (city VARCHAR, degrees INTEGER, date DATE);
INSERT INTO weather VALUES
    ('Amsterdam', 10, '2022-10-14'),
    ('Seattle', 8, '2022-10-12');

SELECT * FROM capitals
UNION BY NAME
SELECT * FROM weather;

city	country	degrees	date
Seattle	NULL	8	2022-10-12
Amsterdam	NL	NULL	NULL
Berlin	Germany	NULL	NULL
Amsterdam	NULL	10	2022-10-14

UNION BY NAME следует семантике множеств (следовательно, выполняет удаление дубликатов), а UNION ALL BY NAME следует семантике мультимножеств.

`INTERSECT`

Условие INTERSECT может быть использовано для выбора всех строк, которые встречаются в обоих запросах.

Стандартный `INTERSECT` (Семантика множеств)

Стандартный INTERSECT выполняет удаление дубликатов, поэтому возвращаются только уникальные строки.

SELECT * FROM range(2) t1(x)
INTERSECT
SELECT * FROM range(6) t2(x);

x
0
1

`INTERSECT ALL` (Семантика мультимножеств)

INTERSECT ALL следует семантике мультимножеств, поэтому дубликаты возвращаются.

SELECT unnest([5, 5, 6, 6, 6, 6, 7, 8]) AS x
INTERSECT ALL
SELECT unnest([5, 6, 6, 7, 7, 9]);

x
5
6
6
7

`EXCEPT`

Условие EXCEPT может быть использовано для выбора всех строк, которые только встречаются в левом запросе.

Стандартный `EXCEPT` (Семантика множеств)

Стандартный EXCEPT следует семантике множеств, поэтому выполняется удаление дубликатов, и возвращаются только уникальные строки.

SELECT * FROM range(5) t1(x)
EXCEPT
SELECT * FROM range(2) t2(x);

x
2
3
4

`EXCEPT ALL` (Семантика мультимножеств)

EXCEPT ALL использует семантику мультимножеств:

SELECT unnest([5, 5, 6, 6, 6, 6, 7, 8]) AS x
EXCEPT ALL
SELECT unnest([5, 6, 6, 7, 7, 9]);

x
5
8
6
6