SQL EXCEPT 和 NOT EXISTS 有什么区别？差集对账为什么会漏重复？
本文目录
面试怎么回答
我会先区分集合差与多重集差。EXCEPT 默认按选出的整行比较并去重，EXCEPT ALL 才保留重复次数的差额；NOT EXISTS 则按相关条件判断是否匹配，未匹配的左侧重复行仍会保留。还要处理 NULL 的比较差异。用于对账时，我会统一列和类型，做双向差集，再比较业务键或整行的出现次数，不能只看差集为空。
先把这段讲顺；面试官追问时，再展开下面的原理和细节。
详细讲解
两张表双向 EXCEPT 都查不到差异，验收通过；可业务汇总还是多了一笔。这个结果不矛盾：如果一边有两份相同记录，另一边只有一份，默认集合差恰好可能看不见它。
集合差异、重复次数和匹配条件是三件事；对账要同时明确比较列、空值语义和多重集数量。
先问清楚：你要比较什么？
EXCEPT 比较的是 SELECT 出来的整行。只选订单号，就只是在检查订单号集合；把订单号和金额一起选出来，金额变化才会参与比较。两边列数、对应位置和类型也必须兼容，不能把字段名相同误认为位置一定相同。
NOT EXISTS 则由相关条件决定匹配关系。按订单号判断“右边有没有这笔订单”，与按订单号、金额、状态判断“有没有完全相同的一行”，解决的是不同问题。先确定对账粒度，再选语法，顺序不能反过来。
默认 EXCEPT，会把重复次数折叠掉
以 PostgreSQL 17 为例，左侧为 A、A、B，右侧为 A、B。左减右为空，右减左也为空，因为两边不同元素的集合都是 A、B；但两边原始条数并不相等。
SELECT order_id, amount FROM source_orders
EXCEPT
SELECT order_id, amount FROM target_orders;如果要保留每种相同行的数量差，可以使用 EXCEPT ALL。同一行左侧出现 m 次、右侧出现 n 次，左差右保留 max(m-n, 0) 次。上面的例子会留下一个 A，交换左右后则为空。
不过，ALL 不代表按主键比较最新版，也不会替你找出金额差多少。它仍然按投影后的整行匹配，只是保留出现次数差额。对业务键不唯一的数据，要先决定重复是否合法，不能用集合运算替模型做决策。
NOT EXISTS 也不是 EXCEPT ALL 的替身
下面的查询检查目标侧是否存在同订单号记录。假设源侧有两行订单号 C，而目标侧完全没有 C，它会返回两行，不会自动去重：
SELECT s.order_id, s.amount
FROM source_orders s
WHERE NOT EXISTS (
SELECT 1
FROM target_orders t
WHERE t.order_id = s.order_id
);反过来，只要右侧存在一条 A，左侧所有 A 都能匹配，不会因为左侧多一份就留下一个。因此它保留未匹配左行，与逐份抵消的 EXCEPT ALL 仍然不同。
性能也不能只看语法名字。优化器可能选择哈希、排序或反连接，列宽、数据量、重复比例和统计信息都会影响成本。先让语义正确，再看执行计划和实际资源消耗。
NULL 是另一个容易漏掉的差异
PostgreSQL 的集合去重和集合比较会把对应位置的 NULL 作为可相等的一类值处理；普通等号却不会把 NULL = NULL 判为真。因此，把 EXCEPT 改成使用等号的 NOT EXISTS，结果可能变化。
如果业务明确需要空值安全比较，可以使用 IS NOT DISTINCT FROM。但如果订单号本就不该为空，更合理的是先把它识别为质量异常，而不是让两边的空订单号互相匹配，掩盖源数据问题。
字符串空格、大小写、时间精度与金额类型也应先统一比较规则。必要的标准化应保留原值和规则说明，不能为了“对得上”随意截断或四舍五入。
一个典型场景：集合一致，重复却没被发现
比如目标表误重放了一条订单，双向 EXCEPT 结果都为空，团队最初怀疑金额计算公式有问题。把两侧按整行分组统计次数，才发现某条记录从一次变成两次。
根因不是 EXCEPT 算错，而是验收把集合一致当成了多重集一致。原测试只有缺行和字段变化，没有重复行，恰好没有触发这个盲区。修复应增加出现次数检查，并分别报告缺失、多出、字段变化和重复。
对账结论要能说明验证范围
如果引擎不支持 EXCEPT ALL，可以将两边按需要比较的列汇总次数，再用空值安全的键匹配比较计数。也可以对非空唯一业务键先检查唯一性，再开展字段级比较。
最终报告应写清比较日期、快照、字段与规则。只比较了订单号，就不要声称两张表完全一致；只检查了一侧差集，也不能证明另一侧没有多余数据。