Sql Server Not IN的坑
;with t as (select 1 id union all select 2 union all select 3 union all select null) select case when 5 in (select id from t) then 1 when 5 not in (select id from t) then -1 else 0 end
输出为0而不是-1
Not In 中包含null值 返回false
;with t as (select 1 id union all select 2 union all select 3 union all select null) select case when null in (select id from t) then 1 when null not in (select id from t) then -1 else 0 end
null也不行
输出为0而不是-1
除非集合中不包含null值
;with t as (select 1 id union all select 2 union all select 3 --union all select null ) select case when 5 in (select id from t) then 1 when 5 not in (select id from t) then -1 else 0 end
--输出-1