跳到主要内容

集合操作

集合操作允许按照集合运算语义组合多个查询。集合操作包括 UNION [ALL]、INTERSECT [ALL] 和 EXCEPT [ALL] 子句。默认变体使用集合语义（即去重），带 ALL 的变体使用多重集语义。

传统集合操作按列位置对齐查询，要求被组合的查询具有相同数量的输入列。如果列类型不一致，系统会在必要时添加类型转换。结果将使用第一个查询的列名。

Goose 还支持 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;
citycountrydegreesdate
SeattleNULL82022-10-12
AmsterdamNLNULLNULL
BerlinGermanyNULLNULL
AmsterdamNULL102022-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

语法​