查询数据库中不存在的ID

假设你数据库有个A表：

 

 

ID	NAME
1	aaa
2	bbb
3	ccc
4	ddd

 

需求：给你几个ID，返回A表中不存在的ID?  给你1,2,8,9 返回8,9.

 

1. 一般会这么处理：select id from A where A.ID in (1,2,8,9),然后程序处理。

 

2.现在用一个SQL处理：

 

 

select B.id
  from (select 1 as id from dual
        union
        select 2 as id from dual
        union
        select 8 as id from dual
        union
        select 9 as id from dual
        ) B
  left join A
    on A.id = B.id
 where A.id is null;