如何在IF EXISTS语句中正确使用值为NULL的变量

根本原因 #

SQL 中NULL代表未知值，使用=对两个NULL做等值判断时，返回结果是UNKNOWN而非TRUE，因此EXISTS子查询会判定为无匹配行，才会出现不符合预期的结果。

更简便的解决方法 #

方法1：使用IS NOT DISTINCT FROM（最优方案，适配新数据库版本）

这是SQL标准定义的专门用于等值比较时自动匹配NULL的运算符，不需要写冗余的OR判断，写法最简洁：

if exists (
    select 1 from test 
    where test='ahzdii' 
      and test2 IS NOT DISTINCT FROM @test 
      and test3='ahsuioho'
)
begin
    print 'exists'
end
else
    print 'dont exist'

适配范围：SQL Server 2022及以上版本、Azure SQL、PostgreSQL、MySQL 8.0等主流数据库的新版本均支持该语法。

方法2：使用ISNULL/COALESCE函数（兼容旧版本，需确认占位符）

如果使用的是旧版本不支持上述运算符，可以用空值替换函数，注意选择的占位值必须是业务中不可能出现在test2字段中的值，避免误匹配：

if exists (
    select 1 from test 
    where test='ahzdii' 
      and ISNULL(test2, '§NULL_MARKER§') = ISNULL(@test, '§NULL_MARKER§') 
      and test3='ahsuioho'
)

注：如果字段允许的字符范围广，可以替换为更特殊的、业务无可能用到的字符串作为占位标记即可。

方法3：使用INTERSECT语法（兼容旧版本，无需占位符）

利用INTERSECT集合运算默认把NULL判定为相等的特性，不需要自定义占位符，适配所有SQL Server版本：

if exists (
    select 1 from test 
    where test='ahzdii' 
      and EXISTS (SELECT test2 INTERSECT SELECT @test)
      and test3='ahsuioho'
)

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