SQL NOT EXISTS高效插入目标表不存在记录

写 NOT EXISTS 插入逻辑时，最怕的就是子查询没跟外层表绑在一起——直接写个 NOT EXISTS (SELECT 1 FROM target_table)，结果子查询跟外层毫无关系，要么恒真（全部跳过），要么恒假（全部插入），去重效果直接废掉。

正确做法其实很简单：在子查询的 WHERE 里把源表和目标表的关键字段显式关联起来，比如订单ID、业务唯一键。拿这个例子来说：

INSERT INTO orders (order_id, customer_id, amount)
SELECT s.order_id, s.customer_id, s.amount
FROM staging_orders s
WHERE NOT EXISTS (
  SELECT 1 FROM orders t
  WHERE t.order_id = s.order_id
);

这里有几个细节必须盯住：

• 关联字段（比如 order_id）在两个表中类型要一致，否则隐式转换会让索引根本用不上。
• 如果业务唯一键是复合字段（比如 (customer_id, order_date)），那 WHERE 条件里必须一一列出，漏一个就可能重复插入。
• 数据库差异也得留意：MySQL 8.0+ 和 PostgreSQL 对这种写法优化得不错；SQL Server 的话，目标表上得有对应索引，否则 NOT EXISTS 可能退化成嵌套循环全表扫描，性能直接崩掉。

对比 INSERT IGNORE / ON CONFLICT 的适用场景

NOT EXISTS 是标准 SQL，不是所有数据库都有“冲突忽略”语法。它适合你希望精确控制插入逻辑、而且目标表结构不方便改（比如没有主键或唯一索引）的情况。

反过来说，INSERT IGNORE（MySQL）或 ON CONFLICT DO NOTHING（PostgreSQL）写起来更轻量，但前提是目标表已经有唯一约束或主键——否则要么报错，要么静默失败。怎么选？

• 没有唯一索引？那只能老老实实用 NOT EXISTS，或者先建个 CREATE UNIQUE INDEX。
• 需要记录哪些行被跳过了？NOT EXISTS 可以配合 RETURNING（PostgreSQL），或者用临时表捕获源数据；ON CONFLICT 也能用 RETURNING，但 INSERT IGNORE 就不行了。
• Oracle 用户注意：NOT EXISTS 能用，但更推荐 MERGE INTO——语义更清晰，执行计划通常也更稳。

性能瓶颈常出在目标表缺少对应索引

NOT EXISTS 子查询每次都要扫描目标表，如果没索引，那就是每条源数据对目标表全表扫一遍。10 万条源数据 × 目标表 100 万行 = 千亿级比较，根本跑不完。

• 务必在子查询 WHERE 用到的字段上建索引，比如上面例子里的 orders(order_id)。
• 复合条件就建复合索引，字段顺序按子查询 WHERE 里的出现顺序来。比如 WHERE t.customer_id = s.customer_id AND t.order_date = s.order_date，索引就建 (customer_id, order_date)。
• PostgreSQL 里可以用 EXPLAIN ANALYZE 看是否走了 Index Only Scan；SQL Server 就盯着执行计划里有没有 “Index Seek” 节点。

NULL 值会让 NOT EXISTS 判断意外失效

关联字段如果允许为空，比如 order_id 可能是 NULL，那 t.order_id = s.order_id 只要有一端是 NULL，结果就变成 UNKNOWN，导致整个 NOT EXISTS 返回 TRUE，重复插入就发生了。

• 最稳妥的办法：在 WHERE 条件里显式排除 NULL：WHERE t.order_id = s.order_id AND t.order_id IS NOT NULL。
• PostgreSQL 支持 IS NOT DISTINCT FROM，它把 NULL = NULL 视为 TRUE，但 MySQL 和 SQL Server 不支持。
• 更彻底的解法：在 ETL 源头就把空值清洗掉，或者目标表的字段直接设成 NOT NULL 并加默认值。

说到底，真正卡住人的往往不是语法本身，而是子查询没关联上、关联字段没索引、或者字段里有 NULL 却完全没意识到比较逻辑已经失效。把这些地基打牢，NOT EXISTS 用起来才能干净利落。