sql in 语句，如何求 结果集 与 in 列表的差集

Question

sql 语句, 如 select id from table where id in ( id 列表)，如何求出 id结果集 与 in 括号里面的 id列表 的差集

Answer 1

假设test表的数据如下：

value
1
2
3
4
5

而list为(1,5,11,55)，则可以如此实现

with temp as (
  select 1 as value union
 (select 5 as value) union
 (select 11 as value) union
 (select 55 as value) 
) 
select 
    value 
from 
    temp 
where 
    value not in (select value from test where value in (select value from temp)) 

以上代码在mysql8中通过