33. 集合运算

集合运算（set operations）是把「多条 SELECT 的结果」当成集合来操作：合并、取公共部分、取独有的部分。它和 JOIN 不同——JOIN 是把列拼宽（横向加列），集合运算是把行叠高（纵向加行）或做比较。

参与运算的前提

参与运算的每条 SELECT 必须满足两个前提：

1. 列数要一样，且顺序对应；
2. 对应列的数据类型要兼容（不一定完全相同，但要能隐式转换）。

下面用两张电影表来演示。一张存「高分电影」，一张存「热门电影」：

CREATE TABLE top_rated (
    title VARCHAR(100),
    release_year INT,
    box_office INT
);
CREATE TABLE most_popular (
    title VARCHAR(100),
    release_year INT,
    box_office INT
);

INSERT INTO top_rated VALUES
    ('肖申克的救赎', 1994, 28),
    ('教父', 1972, 25),
    ('黑暗骑士', 2008, 100);
INSERT INTO most_popular VALUES
    ('黑暗骑士', 2008, 100),
    ('教父', 1972, 25),
    ('灰猎犬号', 2020, 40);

UNION 与 UNION ALL（合并）

UNION 把两个结果集合并，并自动去掉重复行。UNION ALL 也一样合并，但保留重复行。

-- 去重合并：6 行里《教父》《黑暗骑士》各重复一次，结果 4 行
SELECT * FROM top_rated
UNION
SELECT * FROM most_popular;

-- 保留重复：结果是 6 行
SELECT * FROM top_rated
UNION ALL
SELECT * FROM most_popular;

去重是怎么判定的？两行「所有对应列都相等」才算重复。UNION 会先排序再去重，所以比 UNION ALL 多花一点代价。

INTERSECT（交集）

INTERSECT 返回「同时出现在两个结果集里」的行。要找「既是高分又是热门的电影」：

SELECT * FROM top_rated
INTERSECT
SELECT * FROM most_popular;

结果只有《教父》和《黑暗骑士》两部。它等价于「在 A 里且也在 B 里」。和 UNION 一样，默认会去重。

EXCEPT（差集）

EXCEPT 返回「在左边结果集里、但不在右边结果集里」的行。顺序是左边减右边，不能调换语义。

-- 高分但不够热门的电影
SELECT * FROM top_rated
EXCEPT
SELECT * FROM most_popular;

结果是《肖申克的救赎》。它只在 top_rated 里出现。若反过来写（把两张表位置对调），得到的就是《灰猎犬号》。

排序怎么写

集合运算的 ORDER BY 只能写在最后一条 SELECT 后面，对整个结果排序，不能写在中间。

SELECT * FROM top_rated
UNION ALL
SELECT * FROM most_popular
ORDER BY release_year;

如果给第一条 SELECT 加 ORDER BY，PostgreSQL 会报错。这一点和 JOIN 写法里的习惯不太一样，要留神。需要给某个分支单独排序再合并？那就得把分支先包成子查询或 CTE，但通常没必要。

三种运算对比

运算	含义	去重	类比
UNION	两集合合并	去重	A ∪ B
UNION ALL	两集合合并	不去重	简单拼接
INTERSECT	取公共部分	去重	A ∩ B
EXCEPT	左减右	去重	A − B

和集合运算、和 JOIN 的区别

• JOIN：横向拼列，适合「把两张表的相关字段放一行」。
• 集合运算：纵向叠行，适合「把多批同结构的数据合并比较」。

比如「想看 A 表和 B 表各自的前 10 名再合到一起」，用 UNION ALL 最自然；而「想看订单配上用户姓名」，那还得用 JOIN。两者解决的是不同维度的问题，别混用。

常见错误

1. 两条 SELECT 列数不一致，直接报错。
2. 对应列数据类型不兼容，无法隐式转换。
3. 把 ORDER BY 写在中间那条 SELECT 上，报语法错。
4. 误以为 EXCEPT 是对称的（它是有方向的，左右顺序影响结果）。

运算优先级

PostgreSQL 里这三种运算的优先级不一样：INTERSECT 高于 UNION 和 EXCEPT。也就是说，下面这条语句会先做 INTERSECT，再把结果和前面的 UNION 合并：

SELECT * FROM a
UNION
SELECT * FROM b
INTERSECT
SELECT * FROM c;

如果你想要「先 A 并 B，再和 C 取交集」，必须用括号把前面的合并包起来：

(SELECT * FROM a UNION SELECT * FROM b)
INTERSECT
SELECT * FROM c;

合并后再聚合

UNION ALL 先把多批数据叠起来，外面再套一层聚合，是做「跨表汇总」的常用套路。比如两张表结构一样，想一起算总票房：

SELECT title, SUM(box_office) AS 总票房
FROM (
    SELECT * FROM top_rated
    UNION ALL
    SELECT * FROM most_popular
) t
GROUP BY title;

注意这里用 UNION ALL 而不是 UNION，否则《教父》《黑暗骑士》的票房会被去重合并、只算一次，结果就不对了。

用 EXCEPT 做对账

EXCEPT 很适合「两边数据对不上」的排查。比如找出「在 orders 里有、但在 users 里找不到对应用户」的孤儿订单：

SELECT user_id FROM orders
WHERE user_id IS NOT NULL
EXCEPT
SELECT id FROM users;

如果结果为空，说明所有订单都挂着合法用户；有结果，就说明存在引用断了的脏数据。和外键约束配合，是数据质量的双保险。

NULL 在集合运算里的行为

判断「两行相同」时，集合运算把两个 NULL 视为相等。所以 UNION 会认为 (张三, NULL) 与 (张三, NULL) 是重复行并去重；INTERSECT 也会把它俩算成交集。这点和对 NULL 的普通比较（NULL 不等于 NULL）不同，是集合运算特有的规则，写对账类查询时要留神。

结果列名怎么定

合并后的列名，取自第一条 SELECT 的列名（或别名）。后续 SELECT 的列名即使不同也会被忽略，所以想给结果起好名字，在第一个分支里用 AS 即可。

去重遇到 NULL 的例外处理

如果业务上希望「带 NULL 的行不参与去重、各自保留」，直接 UNION 做不到。这时可改用 UNION ALL 后再配合 DISTINCT ON（PostgreSQL 特有）精确控制保留哪一行，或用 WHERE 先把 NULL 行过滤出来单独处理。集合运算的去重是「整行全等才算重复」，理解这一点，遇到奇怪的去重结果就不会慌。

小结

集合运算把多条 SELECT 当集合处理：UNION/UNION ALL 合并（去不去重之分）、INTERSECT 取交集、EXCEPT 取差集。记住列数类型要对齐、ORDER BY 放最后、EXCEPT 有方向，基本就不会踩坑。它和 JOIN 是两套思路，按「拼列」还是「叠行」来选就行。