32 UNION、INTERSECT 与 EXCEPT

集合运算逐行组合两个结果集。三者都要求两侧查询返回相同数量的列，且类型兼容。UNION 返回任一侧出现的行（去除重复项）。INTERSECT 返回两侧都出现的行。EXCEPT 返回第一侧有而第二侧没有的行。每种运算都有 ALL 变体。UNION ALL 连接两个输入。INTERSECT ALL 保留每行在两侧出现次数中的较小值；EXCEPT ALL 从左侧次数中减去右侧次数，最小为零。后两种仍然需要匹配并统计行数。

本章要点

• 比较 UNION 和 UNION ALL，了解各自适用的场景
• INTERSECT 与 INTERSECT ALL
• EXCEPT 与 EXCEPT ALL
• 组合集合运算，以及运算优先级如何生效
• 性能：什么时候 UNION ALL 会明显更快

示例

-- UNION removes duplicates across the combined result
SELECT 1 AS n UNION SELECT 2 UNION SELECT 1;

-- UNION ALL keeps duplicates (cheaper, no sort/hash)
SELECT 1 AS n UNION ALL SELECT 1 UNION ALL SELECT 2;

-- INTERSECT: rows present in both
SELECT id FROM fruits WHERE id <= 3
INTERSECT
SELECT id FROM fruits WHERE id >= 2;

-- EXCEPT: rows in the first query but not the second
SELECT id FROM fruits WHERE id <= 4
EXCEPT
SELECT id FROM fruits WHERE id >= 3;

-- Order of evaluation: INTERSECT binds tighter than UNION / EXCEPT.
-- Use parentheses to be explicit:
(SELECT id FROM fruits WHERE id < 5)
UNION
(SELECT id FROM fruits WHERE id > 100);

-- ORDER BY applies to the combined result; column refs by position or alias
SELECT id AS n FROM fruits WHERE id < 3
UNION
SELECT id     FROM fruits WHERE id > 100
ORDER BY n;

UNION 会在完整行上进行排序或哈希，以去除重复项——这项工作确实存在，并会出现在 EXPLAIN 中。如果你知道输入不会产生重复项（例如谓词互不相交），优先使用 UNION ALL。对于大型查询，两者差异可能很大。

INTERSECT 和 EXCEPT 在应用代码中不太常见，但用于分析很方便：“点击了 A 和 B 的用户”“在目录 A 中而不在目录 B 中的产品”。

集合运算是对行集合的运算。各输入的列类型必须对齐——int 和 text 不能直接用集合运算合并。列名取自第一个查询。

运行示例

运行方式（需要 fruits）：

$ psql -f source/union-intersect-except.sql postgres
 n
---
 1
 2
(2 rows)

 n
---
 1
 1
 2
(3 rows)
 ...

常见陷阱

• 对于不会产生重复项的查询，使用 UNION 而不是 UNION ALL 会把 CPU 浪费在去重上。除非可能出现你不想保留的重复项，否则选择 UNION ALL。
• 列数或类型不对齐：SELECT id, name FROM t UNION SELECT id FROM t2 会报错。用 NULL::text 补齐列，或对齐列。
• 集合运算后的 ORDER BY 针对合并后的结果，不能使用带表限定的列名。使用位置（ORDER BY 1）或第一个查询中的别名。
• EXCEPT 对 NULL 很敏感：EXCEPT 把两个 NULL 当作相等（就像 DISTINCT），所以左侧有 NULL、右侧也有 NULL 的行会被删除。这通常正是你想要的结果，但值得记住。

下载

SQL 文件：union-intersect-except.sql · 全部示例文件 · 准备工作