Oracle SQL：如何使用关键字检查给定的员工 ID 列表中不存在哪些记录"IN or NOT IN or other"？

Question

我有200个员工ID，下面的查询返回数据库中存在的那个ID。

SELECT * FROM WORKDAY_EMPLOYEE_CORE where PERSONID IN (1, 2, 3, 4)

很难检测出200个中不存在的那些。

SQL中是否有任何方法可以检查给定列表中不存在的内容？

Answer 1

您可以使用not exists。但你必须生成列表：

select x.personid
from (select 1 as personid from dual union all
select 2 as personid from dual union all
. . .
) x
where not exists (select 1
from workday_employee_core wec
where wec.personid = x.personid
);

Answer 2

通常将列表放在表中或派生表中，然后使用not exists:

select p.personid
from (
select 1 as personid from dual
union all select 2 from dual
union all select 3 from dual
union all select 4 from dual
) p
where not exists (select 1 from workday_employee_core e where e.personid = p.personid)

如果您经常这样做，我会更简单地实际创建一个表，并向它提供您想要搜索的值的列表。

create table persons (personid int);
insert into persons (personid) values (1);
insert into persons (personid) values (2);
insert into persons (personid) values (3);
insert into persons (personid) values (4);

然后：

select p.personid
from persons p
where not exists (select 1 from workday_employee_core e where e.personid = p.personid)