Set Operations

Search Shortcut cmd + k | ctrl + k

Documentation / SQL / Query Syntax

Set Operations

集合操作允许根据集合操作语义组合查询。集合操作指的是UNION [ALL]、INTERSECT [ALL]和EXCEPT [ALL]子句。普通变体使用集合语义，即消除重复项，而带有ALL的变体使用包语义。

传统的集合操作通过列位置统一查询，并要求要组合的查询具有相同数量的输入列。如果列的类型不同，可能会添加类型转换。结果将使用第一个查询的列名。

DuckDB 还支持 UNION [ALL] BY NAME，它通过名称而不是位置来连接列。UNION BY NAME 不要求输入具有相同数量的列。如果缺少列，将添加 NULL 值。

`UNION`

UNION 子句可用于组合来自多个查询的行。这些查询需要返回相同数量的列。在必要时，会执行隐式转换以组合不同类型的列。如果无法进行此操作，UNION 子句将抛出错误。

Vanilla `UNION` (集合语义)

普通的 UNION 子句遵循集合语义，因此它会执行重复消除，即结果中只包含唯一的行。

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

x
2
1
0

`UNION ALL` (Bag Semantics)

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;

城市	国家	温度	日期
西雅图	NULL	8	2022-10-12
阿姆斯特丹	荷兰	NULL	NULL
柏林	德国	NULL	NULL
阿姆斯特丹	NULL	10	2022-10-14

UNION BY NAME 遵循集合语义（因此它会执行去重操作），而 UNION ALL BY NAME 遵循包语义。

`INTERSECT`

INTERSECT 子句可用于选择在两个查询结果中都出现的所有行。

Vanilla `INTERSECT` (集合语义)

Vanilla 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 子句可用于选择仅在左侧查询中出现的所有行。

Vanilla `EXCEPT` (集合语义)

Vanilla 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