PHP小码农

Appearance

集合操作 UNION（并集） ​

本节讲解集合操作中最常用的 UNION：集合操作与连接的本质区别、UNION 与 UNION ALL 的去重代价、列数与类型的对齐规则、排序与 LIMIT 的作用位置，以及用 UNION ALL 手工改写以避开 Sort 的实战技巧。学完本节，你将能判断一个 UNION 该不该加上 ALL。

前置知识

阅读本节前，建议先了解：子查询优化

基础概念 ​

集合操作（Set Operation） 是把两个查询结果当作「行的集合」来运算，而连接（Join）是把两个表的行横向拼接。前者增加行数、不增加列；后者增加列数。

概念术语说明
并集UNION合并两个结果集并去重
并集保留重复UNION ALL合并两个结果集不去重
交集INTERSECT同时出现在两侧的行
差集EXCEPT出现在左侧但不在右侧的行
去重Deduplication消除完全相同的行
列对齐Column Alignment按位置而非列名匹配
集合算子SetOp计划中执行去重的节点
追加Append计划中单纯拼接的节点

连接与集合操作的方向差异：

维度连接（JOIN）集合操作（UNION 等）
作用方向横向，列变多纵向，行变多
列数要求无限制两侧列数必须相同
列名来源保留只取第一个查询的列名
类型要求各自独立对应列类型必须兼容
去重不去重（除非 DISTINCT）UNION / INTERSECT / EXCEPT 默认去重

UNION 与 UNION ALL 的语义差异：

写法是否去重是否排序计划节点开销
UNION ALL❌❌Append最低
UNION✅✅（排序去重时）HashAggregate 或 Unique + Sort高

铁律：能用 UNION ALL 时永远不要写 UNION。 去重需要把全部结果读进哈希表或排序，代价随结果集增大而线性上升。

语法与用法 ​

text
-- 基本形式
查询1
UNION [ALL]
查询2
[UNION [ALL]
查询3 ...]

-- 结果排序：ORDER BY 只能写在最后，且只能引用第一段查询的列名或输出列序号
查询1
UNION ALL
查询2
ORDER BY 列名 [ASC|DESC]
LIMIT n;

-- 分段限制行数：必须用小括号包住每段
(SELECT ... ORDER BY ... LIMIT 10)
UNION ALL
(SELECT ... ORDER BY ... LIMIT 10);

准备本节贯穿使用的示例表：

sql
-- 用户表
CREATE TABLE users (
  id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT    NOT NULL,
  city TEXT    NOT NULL,
  vip  BOOLEAN NOT NULL DEFAULT false
);

-- 订单表
CREATE TABLE orders (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no   TEXT           NOT NULL UNIQUE,
  user_id    INTEGER,
  amount     NUMERIC(10, 2) NOT NULL CHECK (amount >= 0),
  status     TEXT           NOT NULL,
  created_at DATE           NOT NULL
);

-- 存档订单表：结构与 orders 相同，用于演示跨表合并
CREATE TABLE orders_archive (
  LIKE orders INCLUDING ALL
);

造数：

sql
INSERT INTO users (name, city, vip)
SELECT '用户' || g, (ARRAY['北京','上海','广州','深圳'])[1 + g % 4], g % 5 = 0
FROM generate_series(1, 20000) AS g;

-- orders：2025 年订单
INSERT INTO orders (order_no, user_id, amount, status, created_at)
SELECT
  'SO-' || lpad(g::text, 6, '0'),
  CASE WHEN g % 997 = 0 THEN NULL ELSE 1 + g % 20000 END,
  (random() * 1000)::numeric(10, 2),
  (ARRAY['paid','shipped','done','closed'])[1 + g % 4],
  DATE '2025-01-01' + (g % 365)
FROM generate_series(1, 80000) AS g;

-- orders_archive：2024 年历史订单，订单号有重叠也有新增
INSERT INTO orders_archive (order_no, user_id, amount, status, created_at)
SELECT
  'SO-' || lpad(g::text, 6, '0'),
  CASE WHEN g % 811 = 0 THEN NULL ELSE 1 + g % 20000 END,
  (random() * 800)::numeric(10, 2),
  (ARRAY['done','closed'])[1 + g % 2],
  DATE '2024-01-01' + (g % 366)
FROM generate_series(70000, 100000) AS g;

ANALYZE users, orders, orders_archive;

详细说明 ​

UNION 与 UNION ALL 的去重代价 ​

先看 UNION ALL——它只是把两个结果头尾接起来：

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_no, amount FROM orders WHERE status = 'closed'
UNION ALL
SELECT order_no, amount FROM orders_archive WHERE status = 'closed';
text
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Append  (cost=0.00..5421.00 rows=35000 width=19) (actual time=0.02..18.42 rows=34988 loops=1)
   ->  Seq Scan on orders  (actual time=0.01..9.31 rows=19995 loops=1)
         Filter: (status = 'closed'::text)
         Rows Removed by Filter: 60005
   ->  Seq Scan on orders_archive  (actual time=0.01..4.42 rows=14993 loops=1)
         Filter: (status = 'closed'::text)
         Rows Removed by Filter: 15007
 Execution Time: 19.104 ms

节点就叫 Append：不做去重、不做排序，两个分支的 actual rows 直接相加。

换成 UNION：

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_no, amount FROM orders WHERE status = 'closed'
UNION
SELECT order_no, amount FROM orders_archive WHERE status = 'closed';
text
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 HashAggregate  (cost=5821.00..6171.00 rows=35000 width=19) (actual time=28.1..31.4 rows=34981 loops=1)
   Group Key: orders.order_no, orders.amount
   Batches: 1  Memory Usage: 2705kB
   ->  Append  (actual time=0.02..22.10 rows=34988 loops=1)
         ->  Seq Scan on orders  (actual time=0.01..9.31 rows=19995 loops=1)
               Filter: (status = 'closed'::text)
         ->  Seq Scan on orders_archive  (actual time=0.01..4.42 rows=14993 loops=1)
               Filter: (status = 'closed'::text)
 Execution Time: 32.711 ms

多出了 HashAggregate（Memory Usage: 2705kB），耗时从 19ms 升到 33ms。这里只去掉了 7 行重复，代价却增加了 70%。若结果集更大、work_mem 不够，还会溢出到磁盘变成 Batches: 4。

关键权衡：如果你的业务逻辑保证两段结果天然不重叠（例如按 created_at 年份切分），UNION 是完全多余的。

列对齐规则 ​

集合操作的列是按位置匹配的，与列名无关。列名取第一段查询的别名。

sql
-- 列名取第一段：输出列叫 id / name / city
SELECT u.id, u.name, u.city FROM users u WHERE u.vip
UNION ALL
SELECT o.id, o.order_no, o.status FROM orders o WHERE o.amount > 999;
text
  id   |    name    |  city
-------+------------+--------
     5 | 用户5      | 北京
    10 | 用户10     | 上海
    15 | 用户15     | 广州
  ...
 99001 | SO-099001  | done
 99234 | SO-099234  | closed
(4312 rows)

注意第二段返回的是订单数据，但列名仍然是 name / city——这是个常见的可读性陷阱。建议显式给第一段加别名：

sql
-- 推荐：用别名让输出列名自解释
SELECT u.id AS entity_id, u.name AS label, 'user'::text AS source FROM users u WHERE u.vip
UNION ALL
SELECT o.id, o.order_no, 'order'::text FROM orders o WHERE o.amount > 999;

类型不兼容时 PostgreSQL 会尝试隐式转换，失败则报错：

sql
-- 错误！integer 与 text 无法自动对齐
SELECT 1 UNION ALL SELECT 'abc';
text
ERROR:  UNION types integer and text cannot be matched
LINE 1: SELECT 1 UNION ALL SELECT 'abc';
                                ^

需要显式转换：

sql
SELECT 1::text AS v UNION ALL SELECT 'abc'::text;
text
  v
-----
 1
 abc
(2 rows)

排序与 LIMIT 的作用位置 ​

ORDER BY / LIMIT 只作用于整个集合操作的结果，而不是某一段——除非用括号把某一段单独包起来。

sql
-- ORDER BY 作用于最终合并结果，LIMIT 取全局最大的 5 条
SELECT order_no, amount FROM orders WHERE status = 'closed'
UNION ALL
SELECT order_no, amount FROM orders_archive WHERE status = 'closed'
ORDER BY amount DESC
LIMIT 5;
text
 order_no | amount
----------+---------
 SO-034512 |  999.99
 SO-091203 |  999.87
 SO-012887 |  999.76
 SO-066120 |  999.70
 SO-045098 |  999.64
(5 rows)
text
                                          QUERY PLAN
-------------------------------------------------------------------------------------------------
 Limit  (cost=6380.10..6380.11 rows=5 width=19) (actual time=41.2..41.2 rows=5 loops=1)
   ->  Sort  (cost=6380.10..6467.60 rows=35000 width=19) (actual time=41.2..41.2 rows=5 loops=1)
         Sort Key: orders.amount DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Append  (actual time=0.02..23.10 rows=34988 loops=1)
               ->  Seq Scan on orders  (actual time=0.01..9.31 rows=19995 loops=1)
               ->  Seq Scan on orders_archive  (actual time=0.01..4.42 rows=14993 loops=1)
 Execution Time: 41.812 ms

Sort Method: top-N heapsort 说明优化器知道只需要前 5 名，没有全量排序——这是 PostgreSQL 对 LIMIT 的优化。

如果想让每一段各自限制，必须加括号：

sql
-- 每段各取最新的 3 条，再做并集
(SELECT order_no, created_at FROM orders ORDER BY created_at DESC, id DESC LIMIT 3)
UNION ALL
(SELECT order_no, created_at FROM orders_archive ORDER BY created_at DESC, id DESC LIMIT 3)
ORDER BY created_at DESC;
text
 order_no | created_at
----------+------------
 SO-080000 | 2025-12-31
 SO-079999 | 2025-12-30
 SO-079998 | 2025-12-29
 SO-100000 | 2024-12-31
 SO-099999 | 2024-12-30
 SO-099998 | 2024-12-29
(6 rows)

规则记忆：不带括号的 ORDER BY / LIMIT 属于整个集合操作；带括号的属于该段自身。

用 UNION ALL 手工改写去重 ​

当两段结果大部分不重叠、只有少量重叠时，用 UNION ALL + 条件排除通常比 UNION 快：

sql
-- 原写法：全量去重，代价高
SELECT order_no, amount FROM orders WHERE status = 'closed'
UNION
SELECT order_no, amount FROM orders_archive WHERE status = 'closed';
sql
-- 改写：先并集，再用 NOT EXISTS 排除右表已有的行
SELECT order_no, amount FROM orders WHERE status = 'closed'
UNION ALL
SELECT a.order_no, a.amount FROM orders_archive a
WHERE a.status = 'closed'
  AND NOT EXISTS (SELECT 1 FROM orders o WHERE o.order_no = a.order_no);

第二段的 NOT EXISTS 会走 order_no 上的唯一索引，只在右表行数级别做探测，避免了对 3.5 万行整体哈希。

反过来说：如果两段结果重叠比例很高，UNION 的一次性 HashAggregate 更划算。判断依据是「重复行占比」与「是否有可用唯一索引」。

常见应用场景 ​

场景推荐写法原因
归档表 + 现役表合并查询UNION ALL + NOT EXISTS数据分布有偏，避免全量去重
多张同构分表汇总UNION ALL分片键天然不重叠
把多组统计结果竖排UNION ALL每段标签不同，本来就无重复
需要真正去重的合并UNION语义要求
合并后取 Top NUNION ALL + 末尾 ORDER BY ... LIMIT让 top-N heapsort 生效

实战示例 ​

目标：构建一张「近期订单活动流」，把现役订单与归档订单统一按时间倒序展示，并要求同一订单号只出现一次（以现役表为准）。

步骤 1：确认两表重叠情况

sql
-- 重叠订单号数量
SELECT count(*) AS overlap_cnt
FROM orders o
JOIN orders_archive a ON a.order_no = o.order_no;
text
 overlap_cnt
-------------
       10000
(1 row)

有 1 万条重叠，说明不能简单 UNION ALL。

步骤 2：写法 A —— 直接 UNION

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_no, user_id, amount, created_at FROM orders
UNION
SELECT order_no, user_id, amount, created_at FROM orders_archive
ORDER BY created_at DESC
LIMIT 20;
text
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Limit  (cost=9821.40..9821.45 rows=20 width=34) (actual time=118.4..118.4 rows=20 loops=1)
   ->  Sort  (cost=9821.40..10071.40 rows=100000 width=34) (actual time=118.3..118.3 rows=20 loops=1)
         Sort Key: orders.created_at DESC
         Sort Method: top-N heapsort  Memory: 28kB
         ->  HashAggregate  (cost=5421.00..6421.00 rows=100000 width=34) (actual time=96.2..106.4 rows=100000 loops=1)
               Group Key: orders.order_no, orders.user_id, orders.amount, orders.created_at
               Batches: 1  Memory Usage: 8253kB
               ->  Append  (actual time=0.02..48.1 rows=110000 loops=1)
                     ->  Seq Scan on orders  (actual time=0.01..16.4 rows=80000 loops=1)
                     ->  Seq Scan on orders_archive  (actual time=0.01..21.2 rows=30000 loops=1)
 Execution Time: 119.702 ms

Memory Usage: 8253kB，且要哈希全部 11 万行。

步骤 3：写法 B —— UNION ALL + NOT EXISTS

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_no, user_id, amount, created_at FROM orders
UNION ALL
SELECT a.order_no, a.user_id, a.amount, a.created_at FROM orders_archive a
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.order_no = a.order_no)
ORDER BY created_at DESC
LIMIT 20;
text
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Limit  (cost=8321.40..8321.45 rows=20 width=34) (actual time=42.1..42.1 rows=20 loops=1)
   ->  Sort  (cost=8321.40..8421.40 rows=100000 width=34) (actual time=42.0..42.0 rows=20 loops=1)
         Sort Key: orders.created_at DESC
         Sort Method: top-N heapsort  Memory: 28kB
         ->  Append  (cost=0.42..7421.00 rows=100000 width=34) (actual time=0.03..36.2 rows=100000 loops=1)
               ->  Seq Scan on orders  (actual time=0.01..16.4 rows=80000 loops=1)
               ->  Nested Loop Anti Join  (actual time=0.04..18.1 rows=20000 loops=1)
                     ->  Seq Scan on orders_archive a  (actual time=0.01..6.2 rows=30000 loops=1)
                     ->  Index Only Scan using orders_order_no_key on orders
                           (actual time=0.001..0.001 rows=0 loops=30000)
                           Index Cond: (order_no = a.order_no)
                           Heap Fetches: 0
 Execution Time: 43.107 ms

去掉了 HashAggregate 的 8MB 内存占用，耗时从 120ms 降到 43ms。

步骤 4：验证两写法结果一致

sql
WITH a AS (
  SELECT order_no FROM orders
  UNION
  SELECT order_no FROM orders_archive
), b AS (
  SELECT order_no FROM orders
  UNION ALL
  SELECT a.order_no FROM orders_archive a
  WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.order_no = a.order_no)
)
SELECT (SELECT count(*) FROM a) AS a_cnt,
       (SELECT count(*) FROM b) AS b_cnt,
       (SELECT count(*) FROM (SELECT order_no FROM a EXCEPT SELECT order_no FROM b) d) AS only_in_a,
       (SELECT count(*) FROM (SELECT order_no FROM b EXCEPT SELECT order_no FROM a) d) AS only_in_b;
text
 a_cnt  | b_cnt  | only_in_a | only_in_b
--------+--------+-----------+-----------
 100000 | 100000 |         0 |         0
(1 row)

结果完全一致。

步骤 5：让活动流进一步加速

如果查询模式固定，可以建一个「合并视图」并配合时间索引：

sql
CREATE OR REPLACE VIEW recent_activity AS
SELECT order_no, user_id, amount, created_at, 'current'::text AS source FROM orders
UNION ALL
SELECT a.order_no, a.user_id, a.amount, a.created_at, 'archive'::text FROM orders_archive a
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.order_no = a.order_no);

CREATE INDEX idx_orders_created ON orders (created_at DESC);
CREATE INDEX idx_archive_created ON orders_archive (created_at DESC);
sql
EXPLAIN (ANALYZE)
SELECT * FROM recent_activity ORDER BY created_at DESC LIMIT 20;
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Limit  (actual time=0.06..0.31 rows=20 loops=1)
   ->  Merge Append  (actual time=0.05..0.29 rows=20 loops=1)
         Sort Key: orders.created_at DESC
         ->  Index Scan Backward using idx_orders_created on orders  (actual time=0.02..0.09 rows=20 loops=1)
         ->  Nested Loop Anti Join  (actual time=0.03..0.18 rows=0 loops=1)
               ->  Index Scan Backward using idx_archive_created on orders_archive a  (actual time=0.01..0.05 rows=20 loops=1)
               ->  Index Only Scan using orders_order_no_key on orders  (actual time=0.001..0.001 rows=0 loops=20)
                     Index Cond: (order_no = a.order_no)
 Execution Time: 0.402 ms

优化器把 ORDER BY ... LIMIT 下推给了 Append，形成 Merge Append：两个分支各自只取前 20 行，再归并。耗时降到 0.4ms——这是集合操作配合索引排序的最优形态。

注意事项 ​

  1. UNION 会给整个结果排序或哈希，代价不可忽略。 明确知道两段不重叠时，一定用 UNION ALL。
sql
-- 不推荐：按年份切分的两段天然不重叠
SELECT * FROM orders WHERE created_at >= '2025-01-01'
UNION
SELECT * FROM orders_archive WHERE created_at < '2025-01-01';
sql
-- 正确
SELECT * FROM orders WHERE created_at >= '2025-01-01'
UNION ALL
SELECT * FROM orders_archive WHERE created_at < '2025-01-01';
  1. 不要在中间段写 ORDER BY，它会被整体忽略甚至报错：
sql
-- 错误！ORDER BY 出现在非末尾位置
SELECT order_no FROM orders ORDER BY created_at
UNION ALL
SELECT order_no FROM orders_archive;
text
ERROR:  syntax error at or near "UNION"
LINE 2: UNION ALL

需要时必须加括号。

  1. ORDER BY 只能引用第一段的列名或输出列序号：
sql
-- 错误！created_at 不在第一段输出列中
SELECT order_no FROM orders
UNION ALL
SELECT order_no FROM orders_archive
ORDER BY created_at;
text
ERROR:  column "created_at" does not exist
LINE 4: ORDER BY created_at;
  1. 列数不等会直接报错，且提示位置不明显：
sql
SELECT 1, 2 UNION ALL SELECT 3;
text
ERROR:  each UNION query must have the same number of columns
LINE 1: SELECT 1, 2 UNION ALL SELECT 3;
  1. UNION 去重比较的是整行所有列，NULL 在集合操作中被视为彼此相等（与 = 运算符不同）：
sql
SELECT NULL::integer AS v UNION SELECT NULL::integer;
text
 v
---
  
(1 row)

两个 NULL 被合并成一行——这是集合操作的语义，不要与 WHERE a = b 混淆。

最佳实践 ​

  1. 默认写 UNION ALL，只有在语义确实需要去重时才写 UNION。

  2. 用 NOT EXISTS 手工去重替代 UNION，前提是重叠部分有唯一索引可探测。

  3. 给第一段加语义化别名，因为输出列名只取第一段：

sql
SELECT id AS entity_id, name AS label, 'user'::text AS source FROM users
UNION ALL
SELECT id, order_no, 'order'::text FROM orders;
  1. 把 ORDER BY ... LIMIT 放在最外层，并给参与的列建降序索引，争取 Merge Append：
sql
CREATE INDEX idx_orders_created ON orders (created_at DESC);
  1. 用 UNION ALL 竖排多组统计结果代替多次查询：
sql
SELECT '总用户' AS metric, count(*)::text AS value FROM users
UNION ALL
SELECT '总订单', count(*)::text FROM orders
UNION ALL
SELECT '归档订单', count(*)::text FROM orders_archive;
  1. 分段限制行数时必须加括号，否则 LIMIT 会作用于全局：
sql
(SELECT order_no FROM orders ORDER BY created_at DESC LIMIT 3)
UNION ALL
(SELECT order_no FROM orders_archive ORDER BY created_at DESC LIMIT 3);
  1. 用 EXCEPT 双向求差集验证改写等价性，不要只看行数是否相等。

下一节

继续学习：集合操作 INTERSECT

参考链接 ​