T-SQL带NOT IN条件的表变量DELETE语句未按预期生效

核心根因 #

你遇到的是SQL开发中极易被忽略的NOT IN搭配NULL值的逻辑失效问题，和表数据范围、关联匹配规则没有冲突，具体触发逻辑：

• SQL的条件判断存在三种结果：TRUE、FALSE、UNKNOWN，WHERE子句只会保留判断结果为TRUE的行，UNKNOWN结果的行和FALSE一样会被过滤。
• 当NOT IN对应的子查询结果集中包含NULL值时，整个条件判断会永远无法返回TRUE，完全匹配不到需要删除的行：
  将逻辑展开来看，PatientVisitOID NOT IN (1, 2, NULL) 等价于判断 NOT (PatientVisitOID=1 OR PatientVisitOID=2 OR PatientVisitOID=NULL)，其中任意值和NULL做等值比较的结果都是UNKNOWN，整个表达式的最终结果永远为UNKNOWN，导致DELETE语句无法匹配任何待删除记录。
• 你之前用右连接、内连接校验出的422/76条记录数量是准确的，原因是等值连接判断时，NULL=任意值会返回UNKNOWN，自动过滤掉关联不上的场景，这套逻辑和NOT IN的执行规则完全独立，因此才会出现“关联查询能查出待删记录，但DELETE执行后一行都没删”的反差。
• 你之前写的普通IN条件的DELETE能正常生效，是因为IN的判断逻辑只要匹配到任意一个非NULL的目标值就会返回TRUE，不会因为结果集混了NULL就完全失效，这也是两个写法一个正常一个失效的核心差异。

修复方案 #

可以根据使用习惯选择以下任意一种写法，都能达到删除422条非目标记录、保留76条目标记录的效果：

1. 优先推荐NOT EXISTS写法，从根源上规避三值逻辑的影响，执行逻辑和你之前的关联校验完全一致：

   DELETE FROM @PatientVisitDataPRD
   WHERE NOT EXISTS (
       SELECT 1
       FROM @VisitOID AS A
       WHERE A.PatientVisitOID = @PatientVisitDataPRD.PatientVisitOID
   )
   

2. 保留NOT IN写法的调整方案，在子查询中主动排除NULL值，切断NULL对判断逻辑的干扰：

   DELETE FROM @PatientVisitDataPRD
   WHERE PatientVisitOID NOT IN (
       SELECT A.PatientVisitOID
       FROM @VisitOID AS A
       WHERE A.PatientVisitOID IS NOT NULL
   )
   

3. 直接复用已验证的连接删除逻辑，和你之前右连接校验待删集合的规则完全匹配，结果100%对齐你校验的数量：

   DELETE B
   FROM @PatientVisitDataPRD AS B
   LEFT JOIN @VisitOID AS A
       ON A.PatientVisitOID = B.PatientVisitOID
   WHERE A.PatientVisitOID IS NULL
   

正式执行删除前可以快速验证根因：

• 执行SELECT COUNT(*) FROM @VisitOID WHERE PatientVisitOID IS NULL，一定能查到非0的NULL值记录
• 跑原NOT IN语句的计数版本：SELECT COUNT(*) FROM @PatientVisitDataPRD WHERE PatientVisitOID NOT IN (SELECT PatientVisitOID FROM @VisitOID)，返回值必然是0，和你预期的422条结果完全不符，这就是NULL值干扰的直接证据。

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