Oracle SQL - 查找表中不存在的值

Question

拿这个表格 WORDS 来看

WORD
Hello
Aardvark
Potato
Dog
Cat

还有这个列表：

('Hello', 'Goodbye', 'Greetings', 'Dog')

如何返回一个不在“words”表中但在我的列表中的单词列表？

如果我有一个包含“所有可能单词”的表，可以这样做：

SELECT * from ALL_WORDS_TABLE
where word in ('Hello', 'Goodbye', 'Greetings', 'Dog')
and word not in 
(SELECT word from WORDS
where word in ('Hello', 'Goodbye', 'Greetings', 'Dog')
);

然而我没有这样的表格。还有什么其他的方法可以做到这一点？

另外，构建一个新的表格不是一个选项，因为我没有那个访问级别。

- Jeremy

Answer 1

不要硬编码将列表值写入行中，而是使用 DBMS_DEBUG_VC2COLL 将您的分隔列表动态转换为行，然后使用 MINUS 运算符消除第二个查询中不在第一个查询中的行：

select column_value 
from table(sys.dbms_debug_vc2coll('Hello', 'Goodbye', 'Greetings', 'Dog'))
minus
select word
from words;

- Wolf

---

不错的技巧) 但是如果被拒绝访问sys.*函数怎么办？ - denied

@denied 嗯，那确实很尴尬。也许有其他更优雅的方法将一个分隔符分隔的字符串列表转换为列，而不是使用 sys.dbms_debug_vc2coll。也许可以使用纯 SQL 来解决问题？ - WoMo

在这个上下文中，sys 不是什么坏东西。理论上某人可以拒绝您访问该类型，但这可能不是您需要担心的事情。这与有人删除对 sys.dbms_output 的访问权限的可能性大致相同。 - Jon Heller

非常完美且非常干净。谢谢！ - Jeremy

有没有解决方案，如果我有4-5k个字符串需要检查，而不是4个，我们可以使用类似的东西，因为目前ORACLE在dbms_debug_vc2coll（）中限制了超过1000个参数，并抛出ORA-00939：函数参数太多。 - Dwij Sheth

Answer 2

您可以将列表转换为如下所示的视图：

select 'Hello' as word from dual
union all
select 'Goodbye' from dual
union all
select 'Greetings' from dual
union all
select 'Dog' from dual

然后你可以从中选择：

select * from
(
    select 'Hello' as word from dual
    union all
    select 'Goodbye' from dual
    union all
    select 'Greetings' from dual
    union all
    select 'Dog' from dual
)
where word not in (select word from words);

可能不是你期望的那么完美的解决方案...

你说你没有足够的权限来创建表，所以很可能也无法创建类型 - 但如果你在数据库中找到一个合适的类型“搁置”在那里，你可以这样做:

select * from table (table_of_varchar2_type('Hello','Goodbye','Greetings','Dog'))
where column_value not in (select word from words);

这里假设table_of_varchar2_type是一个类型的名称，其定义如下:

create type table_of_varchar2_type as table of varchar2(100);

你可能会找到一种类型，名为SYS.KU$_VCNT，它是一个VARCHAR2(4000)的表格。

- Tony Andrews

Answer 3

尝试这个解决方案：

请参考以下步骤：

SELECT
 a.word
FROM
(
 SELECT 'Hello' word FROM DUAL UNION
 SELECT 'Goodbye' word FROM DUAL UNION
 SELECT 'Greetings' word FROM DUAL UNION
 SELECT 'Dog' word FROM DUAL
) a
LEFT JOIN ALL_WORDS_TABLE t ON t.word = a.word
WHERE
 t.word IS NULL

- denied