Oracle数据库:从数据库中不存在的列表中选择值

Question

我有一个值列表。其中一些不存在于数据库中。我想查找列表中不存在的值。

例如。给定表格：

id name
--------
1 John
2 Amy
3 Kevin
4 Matt
5 Mark
6 Jim
7 Angela
8 Erin

列表包含：

John, Amy, Sarah, Sam

我只想退货：

Sarah, Sam

如何使用Oracle数据库实现这一点？我知道我可以创建一个新表，插入列表中的值，进行连接，然后删除表。有没有更简单的方法？

Answer 1

动态创建一个表：

select name from
(
select 'John'  as name from dual union all
select 'Amy'   as name from dual union all
select 'Sarah' as name from dual union all
select 'Sam'   as name from dual
) names
where name not in (select name from mytable);

Answer 2

您将使用left join或not exists:

select name
from (select 'John' as name from dual union all
select 'Amy' as name from dual union all
select 'Sarah' as name from dual union all
select 'Sam' as name from dual 
) n left join
t
using (name)
where t.name is null;

Answer 3

您可以使用集合构造函数，例如：

select column_value from soda_key_list_t('John', 'Amy', 'Sarah', 'Sam');

您可以创建自己的"；字符串表"；集合类型(如果您还没有(，例如

create or replace type varchar2_tt as table of varchar2(4000);

或者看看现有的类型有哪些：

select owner, type_name, length
from   all_coll_types t
where  t.coll_type = 'TABLE'
and    t.elem_type_name = 'VARCHAR2'
order by 1,2;

我使用了soda_key_list_t，它包含在Oracle 18c以后的版本中，是Simple Oracle Document Access功能的一部分。其他包括dbms_debug_vc2coll和ora_mining_varchar2_nt。

Answer 4

您还可以使用取消透视表方法：

select Number as list from (    
select * from (
select 'jn','jimmy','ronny' from dual ) 
unpivot
(  
"Values" FOR "Number" IN ("'JN'","'JIMMY'","'RONNY'")   
)
)
minus

select list from your_table;