如何优化带OR条件的LEFT JOIN无UNION查询，提升数据插入校验效率

问题说明 #

需要从tableB向tableA插入既无匹配email也无匹配id的行：只要tableA中某行的email或id与tableB的行任一匹配，就视为已存在，不插入；只有两者都不匹配时才执行插入。

现有两种尝试方案的问题：

• 带OR条件的LEFT JOIN结果正确，但大数据量下触发全表扫描，耗时极长
• UNION方式会返回错误结果（误将tableB中仅email不匹配但id匹配的行也筛选出来）

测试场景代码：

If(OBJECT_ID('tempdb..#tableA') Is Not Null) Begin
    Drop Table #tableA End

If(OBJECT_ID('tempdb..#tableB') Is Not Null) Begin
    Drop Table #tableB End

create table #tableA ( email nvarchar(50), id int )

create table #tableB ( email nvarchar(50), id int )


insert into #tableA (email, id) values ('123@abc.com', 1), ('456@abc.com', 2), ('789@abc.com', 3), ('012@abc.com', 4)

insert into #tableB (email, id) values ('234@abc.com', 1), ('456@abc.com', 2), ('567@abc.com', 3), ('012@abc.com', 4), ('345@abc.com', 5)

 -- 正确返回1条记录，但大数据量下性能差
 select B.email, B.id  
 from #tableB B  
 left join #tableA A on A.email = B.email or B.id = A.id  
 where A.id is null

 -- 错误返回3条记录（id为1、3的行在tableA已存在，不应被筛选）
select B.email, B.id  
from #tableB B  
left join #tableA A on A.email = B.email  
where A.id is null  
union 
select B.email, B.id  
from #tableB B  
left join #tableA A on B.id = A.id  
where A.id is null


If(OBJECT_ID('tempdb..#tableA') Is Not Null) Begin
    Drop Table #tableA End

If(OBJECT_ID('tempdb..#tableB') Is Not Null) Begin
    Drop Table #tableB End

优化方案 #

方案1：用NOT EXISTS替代LEFT JOIN + OR #

NOT EXISTS的写法能让数据库更好地利用索引，避免全表扫描，同时保证逻辑正确：

SELECT B.email, B.id
FROM #tableB B
WHERE NOT EXISTS (
    SELECT 1
    FROM #tableA A
    WHERE A.email = B.email OR A.id = B.id
)

方案2：拆分NOT EXISTS条件（最大化索引利用率） #

如果tableA的email和id分别有单独索引，可将OR拆分为两个NOT EXISTS的AND组合，让数据库分别调用对应索引查询，再合并结果：

SELECT B.email, B.id
FROM #tableB B
WHERE NOT EXISTS (
    SELECT 1
    FROM #tableA A
    WHERE A.email = B.email
)
AND NOT EXISTS (
    SELECT 1
    FROM #tableA A
    WHERE A.id = B.id
)

该写法完全匹配需求逻辑：既没有匹配的email，也没有匹配的id，性能提升效果更明显。

方案3：提前创建索引（核心优化步骤） #

无论采用哪种查询写法，都需要给tableA的email和id字段创建单独索引，才能让查询优化器高效执行：

CREATE NONCLUSTERED INDEX IX_tableA_Email ON #tableA(email)
CREATE NONCLUSTERED INDEX IX_tableA_Id ON #tableA(id)

正式表场景可根据业务需求考虑复合索引，但单独索引已能满足大部分性能需求。

最终插入写法 #

将筛选结果直接插入tableA的完整语句：

INSERT INTO #tableA(email, id)
SELECT B.email, B.id
FROM #tableB B
WHERE NOT EXISTS (
    SELECT 1
    FROM #tableA A
    WHERE A.email = B.email
)
AND NOT EXISTS (
    SELECT 1
    FROM #tableA A
    WHERE A.id = B.id
)

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