SQL中NOT IN与NOT EXISTS返回结果异常差异问题排查

你对NOT EXISTS的基础运行逻辑存在认知偏差，错误和NULL值处理无关，核心是NOT EXISTS子查询缺少和外层查询的关联条件。

EXISTS/NOT EXISTS的基础判断逻辑 #

• EXISTS只做存在性判断：针对外层查询返回的每一行，执行一次子查询，如果子查询能返回至少1条记录，EXISTS返回真，否则返回假；NOT EXISTS逻辑正好相反。
• EXISTS不会自动匹配内外层的同名字段，必须手动在子查询的WHERE条件中写明内外层数据的匹配规则。
• EXISTS的子查询中SELECT后面写什么字段完全不影响判断结果，行业惯例统一写SELECT 1，减少不必要的字段解析开销。

极简测试用例的错误说明 #

你写的测试语句：

select id from #T1 where not exists (select id from #T2)

逻辑等价于：只要#T2表中存在任意数据，NOT EXISTS就返回假，外层#T1的所有行都会被过滤。你在#T2中插入了2条数据，所以查询结果为空是完全符合语法逻辑的。

对应的正确NOT EXISTS写法需要补全关联条件：

select id from #T1 where not exists (select 1 from #T2 where #T2.id = #T1.id)

这个写法才会和id not in (select id from #T2)返回一致的结果。

业务SQL的修正方案 #

你的业务SQL有两个问题：

1. 内外层查询使用了完全相同的表别名，很容易导致关联逻辑写错
2. NOT EXISTS的子查询没有写内外层ClinicLocationId的匹配条件，导致只要12个月滚动窗口内有任意符合条件的诊所，外层所有记录都会被过滤，最终返回0结果。

修正后的NOT EXISTS部分代码如下（注意内层表别名加了_sub后缀做区分，最后一行是你缺失的核心关联条件）：

AND NOT EXISTS (
    SELECT 1
    FROM [order].package p_sub WITH(NOLOCK)
        INNER JOIN [order].[order] o_sub WITH(NOLOCK) ON o_sub.packageid = p_sub.packageid
        INNER JOIN profile.ClinicLocationInfo cli_sub WITH(NOLOCK) 
            ON cli_sub.LocationId = o_sub.ClinicLocationId
            AND cli_sub.FacilityType IN('CLINIC', 'HOSPITAL')
    WHERE CAST(p_sub.ShipDTM AS DATE) >= @StartDate
        AND CAST(p_sub.ShipDTM AS DATE) < DATEADD(day,-1,@EndDate)
        AND p_sub.isshipped = 1
        AND o_sub.IsShipped = 1
        AND ISNULL(o_sub.iscanceled, 0) = 0
        AND o_sub.ClinicLocationId = o.ClinicLocationId -- 缺失的关联条件
)

补充说明 #

• NOT IN不需要写显式关联，是因为它的逻辑是拿外层指定字段和子查询返回的整个结果集做值比对，但如果子查询返回结果中存在NULL值，整个NOT IN的判断结果会变为UNKNOWN，最终过滤掉所有数据，这也是大部分资料提到的NOT IN坑点。
• 生产环境优先使用写对关联条件的NOT EXISTS，不会受NULL值影响，性能通常也优于NOT IN。

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