JDS JDS - 1 month ago 7
SQL Question

Find values that do not exist in a table

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 PL-SQL?

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