查找表中不存在的值 [英] Find values that do not exist in a table

问题描述

说我有一张桌子:

ID   ACCNT   DESC
1    123     aaa
2    234     bbb
3    345     ccc

我有一个帐号列表123,345,555,777.
所以我可以得到一个存在的列表

I have a list of account numbers 123,345,555,777.
So I can get a list that exist by

SELECT * FROM MY_TABLE WHERE ACCNT IN (...my list...)

现在我要做相反的事情.
从我的列表中，我想查询一个表中没有的帐号.

Now I want to accomplish the opposite.
From my list I want a query to show me the account numbers that are not in the table.

所以在我的示例中，我想报告表中不存在555和77.

So in my example I want to report that 555 and 77 does not exist in the table.

如何在Oracle SQL中编写此查询?

How can I write this query in Oracle SQL?

推荐答案

select column_value as missing_num
from   table (sys.odcinumberlist (123,345,555,777))
where  column_value not in (select accnt from my_table);

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