MS SQL Server 2017使用NOT IN或<> WHERE子句时NULL值被意外排除问题

底层逻辑：SQL的三值运算规则 #

SQL的布尔运算并非只有TRUE和FALSE两种结果，还存在第三种结果UNKNOWN，专门用于处理NULL值的比较场景：

• 任何涉及NULL的等值/不等值比较（=、<>、>、<等），返回结果都是UNKNOWN
• WHERE子句仅会保留判断结果为TRUE的行，结果为FALSE、UNKNOWN的行都会被排除

对应场景的具体表现 #

1. 使用WHERE a.Other <> 'Y'时：
   当a.Other为NULL时，NULL <> 'Y'的返回结果是UNKNOWN，直接被过滤，所以仅返回a.Other = 'N'的行。
2. 使用WHERE a.Other NOT IN ('Y')时：
   NOT IN的本质是对列表中每个值做不等比较后执行AND运算，NOT IN ('Y')等价于a.Other <> 'Y'，因此遇到NULL值同样返回UNKNOWN，触发过滤逻辑。

兼容NULL的推荐写法 #

你当前使用的WHERE (a.Other IS NULL OR a.Other = 'N')是最优写法，无需对字段做函数处理，不会影响字段索引的正常使用。
如果场景允许一定的性能损耗，也可以使用SQL Server内置函数简化写法：

WHERE ISNULL(a.Other, 'N') <> 'Y'
-- 或者
WHERE COALESCE(a.Other, 'N') = 'N'

注意：SQL Server 2017不支持IS DISTINCT FROM语法，该语法从SQL Server 2022版本开始提供，可以直接简化为WHERE a.Other IS DISTINCT FROM 'Y'同时覆盖NULL比较的场景。

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