确定哪些值不在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屋!