在之前的章节里，我们所有的操作（JOIN, 子查询, CTE）都致力于在一个查询内部，通过各种方式组合和处理数据。

这一章，我们要学习一种全新的思路：将多个独立的 SELECT 查询的结果，像操作数学集合一样进行合并、取交集或取差集。

PostgreSQL 提供了三个标准的集合运算符，它们专门用来处理这种“查询结果”之间的运算：

• UNION: 将两个结果集合并成一个，并自动去重（并集 ∪）。
• INTERSECT: 只返回同时存在于两个结果集中的行（交集 ∩）。
• EXCEPT: 返回存在于第一个结果集，但不存在于第二个结果集中的行（差集 -）。

使用集合运算的基本规则
要让两个 SELECT 语句能够进行集合运算，它们必须满足一个严格的条件：两个查询返回的结果集，在列的数量、顺序和数据类型上必须完全兼容。

• 列的数量必须相同。
• 对应位置的列的数据类型必须兼容（比如 INT 和 NUMERIC 可以，但 INT 和 TEXT 不行）。
• 列名可以不同，最终结果会采用第一个 SELECT 语句的列名。

准备工作：创建两张新表

为了更好地演示集合运算，我们需要两个结构相似但内容不同的表。

场景设定：假设我们公司有两个分部，一个在北京，一个在上海。我们有两张独立的员工表。

CREATE TABLE employees_beijing (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE employees_shanghai (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO employees_beijing (id, name) VALUES
(1, '张三'),
(2, '李四'),
(3, '王五');

INSERT INTO employees_shanghai (id, name) VALUES
(3, '王五'), -- 王五同时在两个分部有记录
(4, '赵六'),
(5, '孙七');

14.1UNIONvsUNION ALL：合并结果集

UNION 是最常用的集合运算符，它将两个查询的结果合并在一起。

UNION：合并并自动去重

问题：我们想获取公司所有员工的名单，每个员工只显示一次。

SELECT id, name FROM employees_beijing
UNION
SELECT id, name FROM employees_shanghai;

执行过程揭秘 :

1. 第一个 SELECT 查出北京的员工：(1, ‘张三’), (2, ‘李四’), (3, ‘王五’)。
2. 第二个 SELECT 查出上海的员工：(3, ‘王五’), (4, ‘赵六’), (5, ‘孙七’)。
3. UNION 操作符将这两个结果集合并，并发现 (3, '王五') 是重复的，于是只保留一个。

执行结果:

 id | name
----+------
  1 | 张三
  2 | 李四
  3 | 王五  -- 只出现了一次
  4 | 赵六
  5 | 孙七
(5 rows)

UNION ALL：合并但保留所有重复项

有时候，我们就是需要保留所有的原始数据，不想去重。比如，我们想统计两个分部的总“人次”而不是总“人数”。这时，UNION ALL 就派上用场了。

SELECT id, name FROM employees_beijing
UNION ALL
SELECT id, name FROM employees_shanghai;

执行结果:

 id | name
----+------
  1 | 张三
  2 | 李四
  3 | 王五  -- 北京的王五
  3 | 王五  -- 上海的王五
  4 | 赵六
  5 | 孙七
(6 rows)

性能提示 ：因为 UNION 需要进行排序和去重操作，所以它的开销比 UNION ALL 要大。如果你能确定两个结果集没有交集，或者你不在乎结果中是否有重复，请优先使用 UNION ALL，性能会更好。

14.2INTERSECT：取两个结果集的交集

INTERSECT 会找出那些同时存在于两个查询结果集中的行。

问题：找出哪些员工同时在北京和上海两个分部都有登记。

SELECT id, name FROM employees_beijing
INTERSECT
SELECT id, name FROM employees_shanghai;

执行结果:

 id | name
----+------
  3 | 王五
(1 row)

只有“王五”同时满足两个条件，所以只有他被返回。

14.3EXCEPT：取两个结果集的差集

EXCEPT 会返回那些存在于第一个查询结果集，但不存在于第二个查询结果集中的行。

注意：A EXCEPT B 和 B EXCEPT A 的结果是不同的，顺序很重要！

问题 1：找出哪些员工只在北京分部，而不在上海分部。

-- 北京的员工 差集 上海的员工
SELECT id, name FROM employees_beijing
EXCEPT
SELECT id, name FROM employees_shanghai;

执行结果:

 id | name
----+------
  1 | 张三
  2 | 李四
(2 rows)

问题 2：找出哪些员工只在上海分部，而不在北京分部。

-- 上海的员工 差集 北京的员工
SELECT id, name FROM employees_shanghai
EXCEPT
SELECT id, name FROM employees_beijing;

执行结果:

 id | name
----+------
  4 | 赵六
  5 | 孙七
(2 rows)

集合运算与ORDER BY

如果你想对最终的集合运算结果进行排序，ORDER BY 子句只能放在最后一个 SELECT 语句的末尾。它会对整个最终结果集进行排序。

SELECT id, name FROM employees_beijing
UNION
SELECT id, name FROM employees_shanghai
ORDER BY name ASC; -- 按姓名对最终的5个结果进行排序

本章小结

你已经掌握了像数学家一样操作数据集的优雅技巧！

• 我们学会了用 UNION 和 UNION ALL 来合并结果集，并理解了它们在去重和性能上的差异。
• 学会了用 INTERSECT 来获取两个结果集的公共部分。
• 学会了用 EXCEPT 来计算两个结果集的差集，并注意到了顺序的重要性。

集合运算为我们提供了一种不同于 JOIN 的、处理多组数据的强大视角。在某些场景下，比如数据仓库中的数据比对、找出差异数据等，它们是不可或缺的工具。

在下一章，我们将进入一个非常高级和强大的领域——窗口函数 (Window Functions)。它能让我们在不改变查询结果行数的情况下，进行复杂的跨行计算，比如计算排名、移动平均等。准备好迎接 SQL 查询能力的又一次巨大飞跃了吗？我们下一章见！