集合操作（UNION / INTERSECT / EXCEPT）

功能概述

集合操作用于将多个 SELECT 查询的结果合并为一个结果集。Lakehouse 支持以下集合操作：

操作	说明
`UNION` UNION	两个查询结果的并集，自动去重
`UNION ALL` UNION ALL	两个查询结果的并集，保留重复行
`INTERSECT` INTERSECT	两个查询结果的交集，自动去重
`INTERSECT ALL` INTERSECT ALL	两个查询结果的交集，保留重复行
`EXCEPT` EXCEPT	在第一个结果中但不在第二个结果中的行，自动去重
`EXCEPT ALL` EXCEPT ALL	在第一个结果中但不在第二个结果中的行，保留重复行
`MINUS` MINUS	`EXCEPT` EXCEPT 的同义词

语法

select_statement UNION [ALL | DISTINCT] select_statement select_statement INTERSECT [ALL | DISTINCT] select_statement select_statement EXCEPT [ALL | DISTINCT] select_statement select_statement MINUS [ALL | DISTINCT] select_statement

关键规则：

不指定
```
ALL
```
ALL
或
```
DISTINCT
```
DISTINCT
时，默认为
```
DISTINCT
```
DISTINCT
（去重）。
多个集合操作可链式使用，从左到右依次计算。
```
INTERSECT
```
INTERSECT
优先级高于
```
UNION
```
UNION
和
```
EXCEPT
```
EXCEPT
，即
```
A UNION B INTERSECT C
```
A UNION B INTERSECT C
等价于
```
A UNION (B INTERSECT C)
```
A UNION (B INTERSECT C)
。
```
ORDER BY
```
ORDER BY
和
```
LIMIT
```
LIMIT
子句作用于整个结果集，需写在最后一个
```
SELECT
```
SELECT
之后。

使用要求

所有参与集合操作的
```
SELECT
```
SELECT
语句必须返回相同数量的列。
对应位置的列必须具有兼容的数据类型。
结果集的列名取自第一个
```
SELECT
```
SELECT
语句。

使用示例

UNION — 去重合并

SELECT 1 AS id, 'a' AS name UNION SELECT 1, 'a' UNION SELECT 2, 'b';

执行结果（重复行

(1, 'a')

(1, 'a')

只保留一行）：

+----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+

UNION ALL — 保留重复行

SELECT 1 AS id, 'a' AS name UNION ALL SELECT 1, 'a' UNION ALL SELECT 2, 'b';

执行结果：

+----+------+ | id | name | +----+------+ | 1 | a | | 1 | a | | 2 | b | +----+------+

INTERSECT — 取交集

SELECT * FROM VALUES (1), (2), (3) AS t1(id) INTERSECT SELECT * FROM VALUES (2), (3), (4) AS t2(id);

执行结果：

+----+ | id | +----+ | 2 | | 3 | +----+

EXCEPT / MINUS — 取差集

-- 在第一个结果中但不在第二个结果中的行 SELECT * FROM VALUES (1), (2), (3) AS t1(id) EXCEPT SELECT * FROM VALUES (2), (3), (4) AS t2(id);

执行结果：

+----+ | id | +----+ | 1 | +----+

MINUS

MINUS

与

EXCEPT

EXCEPT

完全等价：

SELECT * FROM VALUES (1), (2), (3) AS t1(id) MINUS SELECT * FROM VALUES (2), (3), (4) AS t2(id);

执行结果相同（返回

）。

链式集合操作与 ORDER BY

SELECT * FROM VALUES (1), (2) AS t1(id) UNION ALL SELECT * FROM VALUES (2), (3) AS t2(id) UNION ALL SELECT * FROM VALUES (3), (4) AS t3(id) ORDER BY id;

执行结果（

UNION ALL

UNION ALL

保留所有行，包括重复）：

+----+ | id | +----+ | 1 | | 2 | | 2 | | 3 | | 3 | | 4 | +----+

结合业务表 — 在职与离职员工合并

-- 在职员工（is_active = TRUE） SELECT id, name, dept, '在职' AS status FROM doc_test.employees WHERE is_active = TRUE UNION ALL -- 离职员工（is_active = FALSE） SELECT id, name, dept, '离职' AS status FROM doc_test.employees WHERE is_active = FALSE ORDER BY id;

INTERSECT 找两个部门的共同员工

-- 查询同时在 Engineering 和 Marketing 出现的员工名（理论上不存在，演示语法） SELECT name FROM doc_test.employees WHERE dept = 'Engineering' INTERSECT SELECT name FROM doc_test.employees WHERE dept = 'Marketing';

执行结果（两个部门无同名员工，返回空集）：

+------+ | name | +------+ (0 rows)

注意事项

```
UNION
```
UNION
（不带
```
ALL
```
ALL
）会对结果去重，性能开销比
```
UNION ALL
```
UNION ALL
大。若确定不需要去重，建议使用
```
UNION ALL
```
UNION ALL
。
子查询中使用
```
ORDER BY
```
ORDER BY
或
```
LIMIT
```
LIMIT
时，需用括号包裹该子查询。
```
MINUS
```
MINUS
是
```
EXCEPT
```
EXCEPT
的别名，行为完全一致，可互换使用。
```
INTERSECT ALL
```
INTERSECT ALL
和
```
EXCEPT ALL
```
EXCEPT ALL
保留重复行时，重复次数取两个结果集中较小的那个。

联系我们