SQL查询单表重复记录并排除另一表匹配值的优化方案咨询

基础信息梳理 #

测试表数据 #

• Table1：

col1  col2
-------------
a1    b1
a2    b1
a3    b2
a4    b3
a5    b3
a5    b4
a5    b2

• Table2：

col2  col3
----------
b1    c1
b4    c2

需求与现有实现 #

原有逻辑为查询Table1中col2字段存在重复的所有记录，对应SQL：

SELECT x.col1,x.col2
  FROM table1 x
  JOIN (SELECT t.col2
          FROM table1 t
      GROUP BY t.col2
        HAVING COUNT(t.col2) > 1) y ON y.col2 = x.col2

需要新增逻辑：排除上述结果中col2值存在于Table2的条目，预期输出：

col1  col2
----------
a3    b2
a4    b3
a5    b3
a5    b2 

当前使用NOT IN的实现可以跑通测试数据，但存在性能和鲁棒性问题。

问题1：性能更优的写法 #

有两类可落地的优化方案，性能均优于当前写法：

• 过滤逻辑下推+NOT EXISTS替代NOT IN
  把对Table2的过滤逻辑提前到聚合子查询内部，先筛掉不需要的col2值，减少外层JOIN的数据量；同时用NOT EXISTS替代NOT IN，匹配到对应记录就终止扫描，在Table2.col2建索引时效率提升明显：

SELECT x.col1, x.col2
FROM table1 x
JOIN (
    SELECT t.col2
    FROM table1 t
    GROUP BY t.col2
    HAVING COUNT(t.col2) > 1
        AND NOT EXISTS (SELECT 1 FROM table2 t2 WHERE t2.col2 = t.col2)
) y ON y.col2 = x.col2

• 窗口函数替代自连接（支持窗口函数的数据库适用）
  用窗口函数一次扫描表就计算出每个col2的重复次数，省掉聚合后自连接的步骤，大表场景下性能提升非常显著：

WITH col2_stat AS (
    SELECT 
        col1,
        col2,
        COUNT(*) OVER(PARTITION BY col2) AS repeat_cnt
    FROM table1
)
SELECT col1, col2
FROM col2_stat
WHERE repeat_cnt > 1
    AND NOT EXISTS (SELECT 1 FROM table2 t2 WHERE t2.col2 = col2_stat.col2)

问题2：NOT IN写法的边界风险 #

当前写法在特定场景下会返回完全不符合预期的结果，核心风险有两个：

• NULL值导致结果全空
  如果Table2的col2字段存在NULL值，整个NOT IN的判断逻辑会失效。SQL中NULL代表未知值，当子查询返回的结果集包含NULL时，所有值与NULL做不等值判断的结果都是UNKNOWN，最终WHERE条件没有符合的记录，返回空结果，和预期完全偏差。
• 隐式转换导致结果错误+性能下降
  如果table1.col2和table2.col2的字段类型、字符集、排序规则不一致，会触发隐式类型转换，一方面会导致字段上的索引失效，查询变慢；另一方面可能出现匹配逻辑错误，比如大小写规则不匹配导致该排除的记录没排除、不该排除的记录被过滤。

如果能从表结构层面保证Table2.col2有非空约束、且两个关联字段类型/字符集完全一致，NOT IN可以正常返回结果，但鲁棒性远低于NOT EXISTS写法。

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