INTERSECT 与 EXCEPT 集合运算验收：重复行、NULL 和排序边界

报表上线后，产品要同时拉出“本月有购买行为”和“参与过试用活动”的客户，另一个统计列表还要筛掉已经完成退订的用户。以前常用的写法是叠好几层JOIN，之后还要花额外精力处理重复行；MySQL 8.4 可以直接用 INTERSECT 取两个集合的交集，用 EXCEPT 取差集，整个验收的重点根本不是会写这两个关键字，而是它自带的去重逻辑、列对齐规则和最后排序的边界行为。

先用客户集合还原问题场景

先建两张测试小表，特意在表里放重复的 customer_id，再把地区字段的部分值设为 NULL。这组测试数据就能覆盖线上平时最容易漏掉的几个特殊场景。

CREATE TEMPORARY TABLE sales_audience (
  customer_id INT,
  region VARCHAR(16)
);

INSERT INTO sales_audience VALUES
  (101, '华东'), (101, '华东'), (102, NULL), (103, '华南');

CREATE TEMPORARY TABLE sales_trial (
  customer_id INT,
  region VARCHAR(16)
);

INSERT INTO sales_trial VALUES
  (101, '华东'), (102, NULL), (102, NULL), (104, '华北');

如果业务层面只关心客户编号，就只把 customer_id 这一列查出来就行；要是把 region 也放到查询结果里，这列就会变成集合行的比对部分。这个很小的改动，直接会让最终的去重结果完全不一样。

INTERSECT：重复行最终保留几次

最精简的写法如下，两边查询都只返回一列，结果里只会留下两边都出现过的客户编号，而且默认自动去重：

SELECT customer_id FROM sales_audience
INTERSECT
SELECT customer_id FROM sales_trial;

最终结果里 101 和 102 各只出现一次。就算 101 在左表里重复了两次，102 在右表里重复了两次，省略修饰词的写法也完全等价于写了 INTERSECT DISTINCT。

如果业务确实需要保留多重集合的语义，改成下面的写法就可以：

SELECT customer_id FROM sales_audience
INTERSECT ALL
SELECT customer_id FROM sales_trial;

可以简单理解成，每个值最终保留的行数是它在左右两个集合里出现次数的较小值：101 最终保留 1 行，102 在左侧集合里只出现 1 次，所以最终也只保留 1 行。不要只看返回的行数就判断语句写得对不对，必须同步核对重复数据的预期结果。

EXCEPT：差集不能直接等价于“按状态过滤”

要筛选出“产生过购买行为但从来没参加过试用”的客户，可以直接这么写：

SELECT customer_id FROM sales_audience
EXCEPT
SELECT customer_id FROM sales_trial;

这套测试数据跑出来的结果是 103。默认的 EXCEPT DISTINCT 会先把左侧的重复值按完整集合行做去重；换成 EXCEPT ALL 的话会按两边出现的次数做减法，只有左侧比右侧多出来的部分才会被留下来。

这里有个很容易踩的边界：差集只会比对两个查询块实际返回的行，它不会自动识别你写的“退订”“有效期”“最新状态”这些背后的业务含义。要是客户状态都存在同一张客户表里，你得先明确好状态的时间快照，再把稳定的数据集投影出来，别指望靠一条差集语句掩盖数据口径不一致的问题。

列数、类型与 NULL：提前定好接口契约

集合运算两侧的查询必须返回相同数量的列。下面这种写法直接就会报错：

SELECT customer_id, region FROM sales_audience
INTERSECT
SELECT customer_id FROM sales_trial;

列的对应关系是按查询返回的位置计算的，不是按列名匹配。更稳妥的做法是让两边的 SELECT 列表列顺序完全一致，代码评审的时候把这条当成一个小型的接口契约来核对。

列类型会按集合运算的规则自动协调，但别把隐式转换当成正常的设计方案。编号类字段两侧统一成整数类型，文本类字段统一字符集和长度；有必要的时候显式用 CAST 做转换，后续数据库升级或者切换连接字符集的时候，能更快发现潜在问题。

NULL 本身也是集合行的合法值。相同位置上的 NULL，做集合去重和比对的表现不能拿平时常用的“NULL = NULL 代表未知”的逻辑去反推，直接在你线上用的目标 MySQL 版本里跑一组最小验收数据，把预期返回的行数写到测试用例里最稳妥。

混用 UNION、INTERSECT、EXCEPT 必须显式加括号

MySQL 8.4 里，INTERSECT 的优先级高于 UNION 和 EXCEPT。举个例子：

SELECT customer_id FROM sales_audience
EXCEPT
SELECT customer_id FROM sales_trial
INTERSECT
SELECT customer_id FROM vip_customer;

很多开发会下意识按从左到右的顺序理解执行逻辑，数据库实际却会先算后面两块的交集。实际项目里建议直接显式加括号把计算顺序写死，旁边还可以随手加注释说明“先取试用用户和VIP用户的交集，再从购买集合里排除这部分人”，让SQL的写法和业务逻辑的表述完全对得上。

最终的排序操作要放在整个集合表达式的最外层：

(SELECT customer_id FROM sales_audience
 EXCEPT
 SELECT customer_id FROM sales_trial)
ORDER BY customer_id
LIMIT 50;

要是某个子查询块需要先排序或者先做行数限制，也要用括号把这个查询块单独包起来，不然你测到的可能只是语法改写后生成的另一条完全不同的查询。

上线前用四组结果做回归验收

生产环境跑查询还要评估数据量。集合运算解决的是SQL语义表达更清晰的问题，不代表性能一定比写JOIN更快；先拿 EXPLAIN 检查查询的扫描范围和中间结果大小，再用线上真实规模的客户数据做对照验证。把返回结果数、重复行数、NULL 行的匹配情况和排序的稳定性都纳入回归检查，比只确认“SQL能正常跑完”要靠谱得多。

常见问题

INTERSECT 默认是 DISTINCT 还是 ALL？

默认是 DISTINCT。只有明确写出 INTERSECT ALL 才会保留全部重复行。

EXCEPT 能替代所有 NOT EXISTS 场景吗？

不能。EXCEPT 更适合两个独立结果集合取差集的场景；如果右侧条件要关联左侧的多列、需要带出额外关联状态或者要保留左表的明细字段，用 NOT EXISTS 或者 JOIN 写出来的逻辑往往可读性更好。

集合运算两边可以用不一样的列名吗？

可以，匹配规则是按位置而不是按列名，但列数必须完全相同，列的排列顺序也要符合业务比对的要求。

为什么我在子查询块里写了 ORDER BY，结果返回的顺序还是不对？

集合运算生成的整体结果要在外层做排序才会生效。查询块内部写的 ORDER BY/LIMIT 只有在用括号把这个查询块单独包住的时候，才会代表你要对局部结果做排序加限制的逻辑。

把 INTERSECT 和 EXCEPT 当成有明确输入输出契约的集合接口，先把投影列固定下来，再依次验收 DISTINCT、ALL、NULL、运算符优先级和排序的边界，后续这部分SQL就不会因为依赖了某个隐式规则，平白无故跑出不符合预期的结果。