Azure Transact SQL查询中NOT IN逻辑未按预期运行求助

问题根因 #

你遇到的是T-SQL中NOT IN的经典NULL值陷阱：
当device_type_id字段存在NULL值时，device_type_id NOT IN (1135, 1163)的判定结果会是未知（UNKNOWN），而WHERE子句仅会保留判定结果为TRUE的记录，所以非1135、1163的记录（含device_type_id为NULL的记录）都会被误拦截，这就是你只保留NOT IN判断时其他ID全部无法通过的原因。

最优修正方案 #

你的需求可以用更简洁的反向过滤逻辑实现，完全规避NOT IN的坑，逻辑和你的需求完全等价，不会出错：

WHERE
-- 仅拦截「ID为1135/1163且di_1为NULL」的记录，其余全部放行
NOT (
    messagedata.device_type_id IN (1135, 1163)
    AND messagedata.parsed.di_1 IS NULL
)

原写法兼容修正 #

如果你还是想沿用原来的OR写法，只需要补充NULL值的判定即可正常运行：

WHERE 
(messagedata.device_type_id NOT IN (1135, 1163) OR messagedata.device_type_id IS NULL)
OR
(messagedata.device_type_id IN (1135, 1163) AND messagedata.parsed.di_1 IS NOT NULL)

根因验证方法 #

你可以先执行下面的查询确认是否存在device_type_id为NULL的记录，即可验证问题原因：

SELECT COUNT(*) AS null_device_type_count FROM messagedata WHERE device_type_id IS NULL

如果返回的计数大于0，就完全匹配你遇到的故障现象。

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