SQL Server查询用NOT IN无限显示执行中但IN运行正常问题求助

问题成因分析 #

• 三值逻辑导致无结果返回：SQL的NOT IN遵循三值逻辑规则，如果子查询返回的PARENT_ALERT_ID中存在任意一个NULL值，整个NOT IN的判定结果会永久为UNKNOWN，不会匹配到任何行，自然无结果返回。而IN运算不会受NULL值的这个特性影响，只要存在匹配的非NULL值就能正常返回结果。
• 执行计划效率差异导致无限执行：IN运算通常可以被数据库优化器转换为高效的半连接（SEMI JOIN）、哈希连接逻辑，匹配到符合条件的行就可以直接返回，执行速度快。但NOT IN在没有合适索引、或者子查询存在NULL值的场景下，优化器很难生成高效的执行计划，大概率会触发全表扫描、嵌套循环遍历，当两张表的数据量较大时，执行耗时会指数级上升，看起来就像处于无限执行状态。

解决方案 #

你可以选择以下任意一种方式修复问题：

1. 过滤子查询中的NULL值，避免三值逻辑陷阱：

select *
from ALERTTRAN_01
where PARENT_ALERT_ID not in (select PARENT_ALERT_ID
                              from nonbulkclosealert
                              where PARENT_ALERT_ID is not null)

2. 改用NOT EXISTS替代NOT IN，既可以规避NULL问题，执行效率通常也更高：

select *
from ALERTTRAN_01 a
where not exists (
    select 1 from nonbulkclosealert b 
    where b.PARENT_ALERT_ID = a.PARENT_ALERT_ID
)

3. 给ALERTTRAN_01和nonbulkclosealert表的PARENT_ALERT_ID字段添加索引，进一步提升查询执行效率。

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