Except算子是否计算成本高昂？有无替代函数实现表差集需求

嘿，我来帮你拆解这个问题——先搞清楚EXCEPT为什么会慢，再给你几个实用的替代方案。

为什么EXCEPT可能耗时高？ #

EXCEPT（默认等价于EXCEPT DISTINCT）的工作逻辑是：先分别对两个输入表的结果集做去重，再找出只存在于第一个表的记录。这个过程涉及排序、去重和差集比对，如果你的表没有合适的索引，或者数据（哪怕行数不多但字段多、单条数据体积大）导致数据库不得不触发磁盘排序，耗时就会飙升。尤其是去重步骤，会额外消耗CPU和内存资源，这也是它比其他方法更“重”的核心原因。

替代方案推荐 #

根据你的需求（获取仅在大表存在的记录），这里有几个更高效的替代方法：

1. NOT EXISTS子查询（最推荐） #

这是大多数场景下的最优解，尤其是当你不需要自动去重（或者大表本身没有重复记录）的时候。它的优势是可以利用小表的索引快速做匹配，数据库通常会用嵌套循环或者哈希连接来优化查询：

SELECT * 
FROM big_table bt
WHERE NOT EXISTS (
    SELECT 1 
    FROM small_table st
    -- 因为两张表schema一致，这里列出所有用来匹配的字段（比如主键或全字段）
    WHERE bt.id = st.id
      AND bt.col1 = st.col1
      AND bt.col2 = st.col2
      -- 其他字段依次类推
);

如果小表的匹配字段上有索引，这个查询的速度会比EXCEPT快很多。

2. LEFT JOIN + IS NULL #

另一种经典写法，原理是把大表和小表左连接，然后筛选出小表侧没有匹配到的记录：

SELECT bt.* 
FROM big_table bt
LEFT JOIN small_table st
ON bt.id = st.id
   AND bt.col1 = st.col1
   AND bt.col2 = st.col2
-- 匹配所有需要比对的字段
WHERE st.id IS NULL;

注意：如果大表有重复记录，这种方法会保留重复，而EXCEPT默认会去重，要根据实际需求选择。

3. EXCEPT ALL（如果数据库支持） #

如果你的数据库（比如PostgreSQL、SQL Server）支持EXCEPT ALL，它和EXCEPT的区别是不会自动去重，只返回大表中不在小表的所有记录（包括重复项）。省去去重开销后，性能会比默认的EXCEPT好不少，适合不需要去重的场景：

SELECT * FROM big_table
EXCEPT ALL
SELECT * FROM small_table;

额外优化建议 #

不管用哪种方法，给匹配字段建索引都是关键！比如在小表的匹配字段（比如主键、或者你用来比对的所有字段）上建立联合索引，数据库就能快速定位匹配记录，避免全表扫描，大幅降低查询耗时。

内容的提问来源于stack exchange，提问作者Akira