集合操作 EXCEPT（差集）

本节讲解 EXCEPT 的语义与方向性、它与 NOT IN / NOT EXISTS 的对应关系和 NULL 陷阱、EXCEPT ALL 的多重集语义，以及用差集定位数据异动的实战方法。学完本节，你将能安全地在「求存在」与「求不存在」之间选择正确的写法。

前置知识

阅读本节前，建议先了解：集合操作 INTERSECT（交集）

基础概念

差集（EXCEPT） 返回「出现在左侧、但不出现在右侧」的行。它有方向性：A EXCEPT B 与 B EXCEPT A 结果通常完全不同。

概念	术语	说明
差集	EXCEPT	左侧独有、右侧没有的行
差集保留重复	EXCEPT ALL	按次数差保留重复
反连接	Anti Join	「左有右无」的连接形态
方向性	Directionality	左右互换结果不同
多重集	Multiset	允许重复元素
集合补	Set Difference	差集的数学名称
双向差集	Symmetric Difference	A−B 与 B−A 的并集

方向性的直观示例：

左侧 L	右侧 R	L EXCEPT R	R EXCEPT L
{1,2,3}	{2,3,4}	{1}	{4}
{1,2,3}	{1,2,3}	{}	{}
{1,2,3}	{}	{1,2,3}	{}

EXCEPT 与三种「求不存在」写法的语义对照：

写法	是否去重	NULL 处理	输出列	风险
EXCEPT	✅ 自动去重	集合语义，NULL 相等	只能输出参与比较的列	无法附加列
NOT EXISTS	❌ 需自行 DISTINCT	无 NULL 陷阱	可输出左表全部列	无
NOT IN	❌ 需自行 DISTINCT	⚠️ 右表含 NULL 则结果为空	可输出左表全部列	NULL 陷阱
LEFT JOIN ... IS NULL	❌ 需自行 DISTINCT	无 NULL 陷阱	可输出两侧列	无

语法与用法

-- 基本形式
查询1
EXCEPT [ALL]
查询2
[ORDER BY 列名 [ASC|DESC]]
[LIMIT n];

-- 括号包裹左侧以便先做分区
(SELECT ... FROM a WHERE ...)
EXCEPT
(SELECT ... FROM b WHERE ...);

-- 双向差集（对称差）：定位两侧互不相同的元素
(SELECT k FROM a EXCEPT SELECT k FROM b)
UNION ALL
(SELECT k FROM b EXCEPT SELECT k FROM a);

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

-- 用户表
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
);

-- 上游系统同步的订单快照
CREATE TABLE orders_snapshot (
  order_no   TEXT           PRIMARY KEY,
  user_id    INTEGER,
  amount     NUMERIC(10, 2) NOT NULL,
  status     TEXT           NOT NULL
);

造数：

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

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 的前 60000 条，另加 3000 条本地不存在的订单
INSERT INTO orders_snapshot (order_no, user_id, amount, status)
SELECT order_no, user_id, amount, status FROM orders WHERE id <= 60000;

INSERT INTO orders_snapshot (order_no, user_id, amount, status)
SELECT 'SO-' || lpad(g::text, 6, '0'), 1 + g % 20000, 100.00, 'paid'
FROM generate_series(200001, 203000) AS g;

ANALYZE users, orders, orders_snapshot;

详细说明

EXCEPT 的执行方式

与交集类似，EXCEPT 也有哈希与排序归并两种实现方向。

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_no FROM orders
EXCEPT
SELECT order_no FROM orders_snapshot;

                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 HashSetOp Except  (cost=0.00..6421.00 rows=37750 width=13) (actual time=48.2..53.1 rows=20000 loops=1)
   ->  Append  (actual time=0.02..41.6 rows=183000 loops=1)
         ->  Seq Scan on orders  (actual time=0.01..16.4 rows=80000 loops=1)
         ->  Seq Scan on orders_snapshot  (actual time=0.01..19.2 rows=63000 loops=1)
 Execution Time: 54.402 ms

HashSetOp Except 会先对左侧结果建哈希表，然后用右侧的每一行去「删掉」哈希表中对应的键。最终留在哈希表里的键就是差集。

关键点：EXCEPT 的输出列名与列数由左侧决定，右侧只用于排除。

EXCEPT 与 NOT EXISTS 的等价改写

EXCEPT 与 NOT EXISTS 语义相同（都表示「左侧独有」），但 EXCEPT 自带去重，且不能附加额外列。

-- 写法 A：EXCEPT，自动去重
EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id FROM orders WHERE created_at >= '2025-10-01' AND user_id IS NOT NULL
EXCEPT
SELECT user_id FROM orders_snapshot
WHERE order_no LIKE 'SO-1%';

-- 写法 B：NOT EXISTS，需要手动 DISTINCT
EXPLAIN (ANALYZE, BUFFERS)
SELECT DISTINCT o.user_id
FROM orders o
WHERE o.created_at >= '2025-10-01'
  AND o.user_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM orders_snapshot s WHERE s.user_id = o.user_id AND s.order_no LIKE 'SO-1%');

                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 HashAggregate  (cost=3210.00..3210.35 rows=3800 width=4) (actual time=12.8..13.0 rows=3821 loops=1)
   Group Key: o.user_id
   ->  Hash Anti Join  (cost=1180.00..3200.50 rows=3800 width=4) (actual time=8.2..11.9 rows=3821 loops=1)
         Hash Cond: (o.user_id = s.user_id)
         ->  Seq Scan on orders o  (actual time=0.01..4.2 rows=5990 loops=1)
               Filter: ((created_at >= '2025-10-01'::date) AND (user_id IS NOT NULL))
               Rows Removed by Filter: 74010
         ->  Hash  (actual time=6.10..6.10 rows=60000 loops=1)
               ->  Seq Scan on orders_snapshot s  (actual time=0.01..3.8 rows=60000 loops=1)
                     Filter: (order_no ~~ 'SO-1%'::text)
 Execution Time: 13.902 ms

Hash Anti Join 只对右表建一次哈希、对左表逐行探测，比 HashSetOp Except 两侧同时维护哈希更省内存。

写法	适用条件	优势
EXCEPT	只输出比较列、天然需要去重	书写简洁，意图明确
NOT EXISTS	需要输出左侧附加列	兼容索引，可附加列
LEFT JOIN ... IS NULL	需要输出右侧列同时判空	可与更多关联条件组合

NULL 在差集中的行为

NULL 在集合操作中被视为相等，因此「左侧是 NULL、右侧也是 NULL」时会被正确排除。这是 EXCEPT 优于 NOT IN 的核心原因：

-- 左侧含 NULL，右侧也含 NULL：NULL 被正确排除
SELECT user_id FROM orders WHERE id <= 20
EXCEPT
SELECT user_id FROM orders_snapshot WHERE order_no IN ('SO-000002','SO-000010');

 user_id
---------
     1
     3
     4
     5
     6
     7
     9
    11
    12
    13
    15
    16
    17
    18
    19
    20
(16 rows)

对比 NOT IN 的陷阱：

-- 错误！user_id 中含 NULL，NOT IN 结果恒为空
SELECT count(*) AS cnt FROM orders WHERE user_id NOT IN (SELECT user_id FROM orders_snapshot);

 cnt
-----
   0
(1 row)

-- 正确：EXCEPT 无视 NULL 陷阱
SELECT count(*) AS cnt FROM (
  SELECT user_id FROM orders
  EXCEPT
  SELECT user_id FROM orders_snapshot
) AS t;

  cnt
------
 4924
(1 row)

结论：涉及可能为 NULL 的列做「求不存在」时，EXCEPT 或 NOT EXISTS 都安全，NOT IN 不安全。

EXCEPT ALL 的多重集语义

EXCEPT ALL 不做去重，而是做多重集相减：对每个值，结果中的出现次数 = 左侧次数 − 右侧次数（若为负则取 0）。

-- 左侧 (1,1,1,2)，右侧 (1,1,2)：结果 (1)
SELECT v FROM (VALUES (1),(1),(1),(2)) AS a(v)
EXCEPT ALL
SELECT v FROM (VALUES (1),(1),(2)) AS b(v);

 v
---
 1
(1 row)

-- 左侧 (1,1,3)，右侧 (1)：结果 (1,3)
SELECT v FROM (VALUES (1),(1),(3)) AS a(v)
EXCEPT ALL
SELECT v FROM (VALUES (1)) AS b(v);

 v
---
 1
 3
(2 rows)

写法	相同行处理	计划节点	用途
EXCEPT	完全排除	HashSetOp Except	求「左侧独有」
EXCEPT ALL	按次数相减	HashSetOp Except All	库存扣减、计数对账

EXCEPT ALL 的典型用途是「数量守恒」校验：例如左侧是入库流水、右侧是出库流水，差集即剩余库存。

用 EXCEPT 定位数据异动

EXCEPT 最实用的场景是对账：把「本地表」与「上游快照」做双向差集，一次性找出新增、删除与字段变更。

-- 单向：本地有、快照没有（新增）
SELECT order_no FROM orders
EXCEPT
SELECT order_no FROM orders_snapshot;

  order_no
-----------
 SO-060001
 SO-060002
 ...
 SO-080000
(20000 rows)

-- 反向：快照有、本地没有（上游新增但本地未同步）
SELECT order_no FROM orders_snapshot
EXCEPT
SELECT order_no FROM orders;

  order_no
-----------
 SO-200001
 SO-200002
 ...
 SO-203000
(3000 rows)

要注意的是，仅比较主键无法发现字段变更。要检测「同一订单号但金额或状态不同」，需要把参与比较的列一起放进差集：

SELECT order_no, amount, status FROM orders WHERE order_no <= 'SO-060000'
EXCEPT
SELECT order_no, amount, status FROM orders_snapshot WHERE order_no <= 'SO-060000';

 order_no  |  amount  | status
-----------+----------+---------
 SO-000017 |   812.34 | done
 SO-000043 |   119.08 | shipped
 SO-000076 |   455.60 | paid
 ...
(1184 rows)

实战示例

目标：把本地订单表与上游快照表做完整对账，输出「新增、缺失、字段变更」三类差异，并给出可核查的明细。

步骤 1：先看两侧总量

SELECT 'orders' AS tbl, count(*) FROM orders
UNION ALL
SELECT 'orders_snapshot', count(*) FROM orders_snapshot;

       tbl       | count
-----------------+-------
 orders          | 80000
 orders_snapshot | 63000
(2 rows)

步骤 2：新增（本地有、快照无）

CREATE TABLE diff_added AS
SELECT order_no FROM orders
EXCEPT
SELECT order_no FROM orders_snapshot;

SELECT count(*) AS added_cnt FROM diff_added;

 added_cnt
-----------
     20000
(1 row)

步骤 3：缺失（快照有、本地无）

CREATE TABLE diff_missing AS
SELECT order_no FROM orders_snapshot
EXCEPT
SELECT order_no FROM orders;

SELECT count(*) AS missing_cnt FROM diff_missing;

 missing_cnt
-------------
        3000
(1 row)

步骤 4：字段变更（同一主键、内容不同）

CREATE TABLE diff_changed AS
SELECT order_no, amount, status FROM orders
EXCEPT
SELECT order_no, amount, status FROM orders_snapshot;

SELECT count(*) AS changed_cnt FROM diff_changed;

 changed_cnt
-------------
        1184
(1 row)

注意这里的结果可能包含两侧都存在的订单号——只要有任意一列不同，整行就会出现在差集中。

步骤 5：用联接把变更明细落到两侧字段上

SELECT
  o.order_no,
  o.amount   AS local_amount,
  s.amount   AS snapshot_amount,
  o.status   AS local_status,
  s.status   AS snapshot_status
FROM orders o
JOIN orders_snapshot s ON s.order_no = o.order_no
WHERE (o.amount, o.status) IS DISTINCT FROM (s.amount, s.status)
ORDER BY o.order_no
LIMIT 10;

  order_no  | local_amount | snapshot_amount | local_status | snapshot_status
------------+--------------+-----------------+--------------+-----------------
 SO-000017  |       812.34 |          812.34 | done         | shipped
 SO-000043  |       119.08 |          119.08 | shipped      | paid
 SO-000076  |       455.60 |          455.60 | paid         | done
 SO-000102  |       702.19 |          702.19 | closed       | done
 SO-000134  |        88.75 |           88.75 | done         | closed
 SO-000188  |       334.02 |          334.02 | shipped      | done
 SO-000211  |       910.44 |          910.44 | done         | paid
 SO-000245  |       266.31 |          266.31 | paid         | shipped
 SO-000299  |       573.86 |          573.86 | closed       | done
 SO-000312  |       140.52 |          140.52 | done         | closed
(10 rows)

(o.amount, o.status) IS DISTINCT FROM (s.amount, s.status) 用行构造器 + IS DISTINCT FROM 做整行比较，能正确处理 NULL。

步骤 6：结果自洽性校验

对账结果应满足守恒关系：本地总量 = 交集量 + 新增量，快照总量 = 交集量 + 缺失量。

WITH i AS (
  SELECT order_no FROM orders INTERSECT SELECT order_no FROM orders_snapshot
)
SELECT
  (SELECT count(*) FROM orders)          AS local_cnt,
  (SELECT count(*) FROM orders_snapshot) AS snap_cnt,
  (SELECT count(*) FROM i)               AS both_cnt,
  (SELECT count(*) FROM i) + (SELECT count(*) FROM diff_added)   AS check_local,
  (SELECT count(*) FROM i) + (SELECT count(*) FROM diff_missing) AS check_snap;

 local_cnt | snap_cnt | both_cnt | check_local | check_snap
-----------+----------+----------+-------------+------------
     80000 |    63000 |    60000 |       80000 |      63000
(1 row)

check_local = local_cnt 且 check_snap = snap_cnt，对账闭环成立。

步骤 7：把对账改写为一次扫描完成

上面四步对 orders 与 orders_snapshot 各扫描了多次。改用 FULL OUTER JOIN 一次完成：

EXPLAIN (ANALYZE, BUFFERS)
SELECT
  count(*) FILTER (WHERE o.order_no IS NULL)                        AS missing_cnt,
  count(*) FILTER (WHERE s.order_no IS NULL)                        AS added_cnt,
  count(*) FILTER (WHERE (o.amount, o.status) IS DISTINCT FROM (s.amount, s.status)) AS changed_cnt
FROM orders o
FULL OUTER JOIN orders_snapshot s ON s.order_no = o.order_no;

                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Aggregate  (cost=7421.00..7421.01 rows=1 width=24) (actual time=58.2..58.2 rows=1 loops=1)
   ->  Hash Full Join  (cost=2216.00..6421.00 rows=83000 width=29) (actual time=18.1..52.4 rows=83000 loops=1)
         Hash Cond: (s.order_no = o.order_no)
         ->  Seq Scan on orders_snapshot s  (actual time=0.01..19.2 rows=63000 loops=1)
         ->  Hash  (actual time=0.01..16.4 rows=80000 loops=1)
               ->  Seq Scan on orders o  (actual time=0.01..16.4 rows=80000 loops=1)
 Execution Time: 59.104 ms

 missing_cnt | added_cnt | changed_cnt
-------------+-----------+-------------
        3000 |     20000 |        1184
(1 row)

三类差异数与前面逐步计算完全一致，但只需一次全表扫描加一次聚合。

步骤 8：三种方案对比

方案	扫描次数	执行耗时	可读性
多次 EXCEPT	orders × 4 + snapshot × 4	约 240 ms	高，步骤清晰
单次 FULL OUTER JOIN	各 1 次	59.1 ms	中，条件聚合较密
NOT EXISTS × 3	各 3 次	约 90 ms	中

注意事项

1. EXCEPT 有方向性，写反了结果完全不同：

-- 错误！这会返回「快照比本地多的」，而不是「本地比快照多的」
SELECT order_no FROM orders_snapshot
EXCEPT
SELECT order_no FROM orders;

2. EXCEPT 只能输出参与比较的列，无法附加其他字段：

-- 错误！两侧列数不一致
SELECT order_no, amount, created_at FROM orders
EXCEPT
SELECT order_no, amount FROM orders_snapshot;

ERROR:  each EXCEPT query must have the same number of columns
LINE 2: EXCEPT

3. 仅比较主键会漏掉字段变更。要检测内容差异，必须把目标列一起放进差集，或用行构造器：

-- 只能发现「有/没有」，发现不了「改了」
SELECT order_no FROM orders EXCEPT SELECT order_no FROM orders_snapshot;

4. NOT IN 不能替代 EXCEPT 做差集，因为右表含 NULL 时结果恒为空：

-- 错误！结果恒为 0 行
SELECT count(*) FROM orders WHERE user_id NOT IN (SELECT user_id FROM orders_snapshot);

5. EXCEPT 会自动去重，若需要保留次数差必须用 EXCEPT ALL：

SELECT v FROM (VALUES (1),(1),(1)) AS a(v)
EXCEPT
SELECT v FROM (VALUES (1)) AS b(v);
-- 结果：(1)，而不是 (1,1)

6. ORDER BY / LIMIT 只能写在最末尾，作用于整个差集结果。

7. EXCEPT 的优先级高于 UNION 与 EXCEPT 自身的左结合，混合时建议加括号：

-- 显式括号，避免解析歧义
(SELECT k FROM a EXCEPT SELECT k FROM b)
UNION ALL
(SELECT k FROM b EXCEPT SELECT k FROM a);

最佳实践

1. 对账场景优先用「双向差集 + 行构造器比较」，能同时覆盖新增、缺失、变更：

-- 新增
SELECT order_no FROM orders EXCEPT SELECT order_no FROM orders_snapshot;
-- 缺失
SELECT order_no FROM orders_snapshot EXCEPT SELECT order_no FROM orders;
-- 变更
SELECT order_no, amount, status FROM orders
EXCEPT
SELECT order_no, amount, status FROM orders_snapshot;

2. 需要输出附加列时改用 NOT EXISTS，它同样安全且能利用索引：

SELECT o.order_no, o.created_at
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM orders_snapshot s WHERE s.order_no = o.order_no);

3. 把多次差集合并为一次 FULL OUTER JOIN + 条件聚合，在大表对账时能把扫描次数从 8 次降到 2 次：

count(*) FILTER (WHERE s.order_no IS NULL) AS added_cnt,
count(*) FILTER (WHERE o.order_no IS NULL) AS missing_cnt,
count(*) FILTER (WHERE (o.amount, o.status) IS DISTINCT FROM (s.amount, s.status)) AS changed_cnt

4. 用集合恒等式做自检：|A| = |A∩B| + |A−B|，能验证差集结果是否自洽。

5. 涉及可空列时绝不用 NOT IN，改用 EXCEPT 或 NOT EXISTS。

6. 比较整行时用行构造器 + IS DISTINCT FROM，避免 IS NULL / IS NOT NULL 的组合爆炸：

WHERE (o.amount, o.status) IS DISTINCT FROM (s.amount, s.status)

7. EXCEPT ALL 只用于多重集语义明确的场景（库存、计数），普通去重需求一律用 EXCEPT。

下一节

继续学习：公共表表达式基础

参考链接

• Combining Queries (UNION, INTERSECT, EXCEPT)
• SELECT
• Row and Array Comparisons