Find values that do not exist in a table

Question

Say I have a table:

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

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.

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

How can I write this query in Oracle SQL?

Answer 1

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