Sql Server Not IN的坑

?

1 2 3 4 5 6 7 8

;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

?

1 2 3 4 5 6 7 8

;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值

?

1 2 3 4 5 6 7 8 9

;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