SQL query to get ids NOT in a table

Question

Given a list of IDs, I need to determine those that are NOT in an Oracle database. For example, given the table:

my_table

+----+-------+
| ID | DATA  |
+----+-------+
|  1 | Foo   |
+----+-------+
|  3 | Bar   |
+------------+
|  5 | Etc   |
+------------+

...and given the list [1, 2, 3, 4], I need the list [2, 4].

I've come up with this syntax using a MINUS clause:

SELECT '1' as id FROM dual
UNION
SELECT '2' as id FROM dual
UNION
SELECT '3' as id FROM dual
UNION
SELECT '4' as id FROM dual
MINUS
SELECT id FROM my_table WHERE id IN ('1','2','3','4')

But that seems very clumsy and will get messy fast since in reality I will be dealing with hundreds of IDs at a time. Is there better syntax to get a list of values than the UNION syntax?

Something to the effect of:

/* Pseudo code */
SELECT id FROM VALUES ('1', '2', '3', '4')
MINUS
SELECT id FROM my_table WHERE id IN ('1','2','3','4')

The other alternative is to do it programmatically. I can query the database for the existing IDs. Then in code I can remove the list of those found from the original list to get the sub-list of those not in the database. This would work, but am I not correct that having the database do the work would be more efficient?

Thanks

Answer 1

As explained here: How to how-to-convert-csv-to-table-in-oracle you can convert your list of values into a table.

After this, things are simple:

select * from table(splitter('a,b,c,d'))
minus
select id from my_table;

or

select 
  column_value as id 
from table(splitter('a,b,c,d')) a
  left join my_table b on (a.column_value = b.id)
where b.id is null;