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