确定哪些值不在sql表中 [英] Determine which values are not in sql table

问题描述

我在oracle中有以下查询:

I have this query in oracle:

select * from table where col2 in (1,2,3,4);

让我说我得到了这个结果

lets say I got this result

col1 | col2
-----------
a       1
b       2

我的(in，(1,2,3,4)'部分有20个或更多选项，如何确定我在表中找不到的值?在我的示例3和4中，表中不存在

My 'in (1,2,3,4)' part has like 20 or more options, how can I determinate which values I don't found in my table? in my example 3 and 4 doesn't exist in the table

推荐答案

IN列表很愚蠢，或者至少不是很有用.请使用SQL Type集合存储您的值，因为我们可以将它们转换为表.

IN lists are stupid, or at least not very useful. Use a SQL Type collection to store your values instead because we can turn them into tables.

在此示例中，我使用了晦涩的SYS.KU $ _OBJNUMSET类型，这是我知道的10g上唯一的Number嵌套表. (11g中还有更多内容.)

In this example I'm using the obscure SYS.KU$_OBJNUMSET type, which is the only nested table of Number I know of on 10g. (There's lots more in 11g).

所以

select t.column_value 
from table ( SYS.KU$_OBJNUMSET  (1,2,3,4) ) t
        left join your_table 
          on col2 = t.column_value
where col2 is null;

这篇关于确定哪些值不在sql表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！