SQL query to get NOT identifiers in a table

Question

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

my_table

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

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

I came up with this syntax using a sentence MINUS:

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 awkward and will be messy, because in fact I will be dealing with hundreds of identifiers at a time. Is there a better syntax for getting a list of values ​​than UNION syntax?

Sort of:

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

- . . , , . , , ?

Answer 1

: how-to-convert-csv-to-table-in-oracle .

:

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

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;

Answer 2

:

SELECT * FROM dual WHERE id NOT IN (SELECT id FROM my_table);

SELECT * FROM dual WHERE id NOT EXISTS (SELECT id FROM my_table);

Answer 3

Oracle, , , , .

Pass the list as a nested table, and then treat it as a real table. You pass the list as an array from Java / Perl or any other language that you use to invoke the stored process.

TYPE ID_ARRAY_T is TABLE of NUMBER;


PROCEDURE FIND_IDS_NOT_IN_LIST( i_list IN ID_ARRAY_T, o_output OUT SYS_REFCURSOR)
IS
v_id ID_ARRAY_T;
BEGIN
OPEN O_OUTPUT FOR
 SELECT column_value FROM TABLE(v_id) WHERE column_value 
   NOT IN (select ID from my_table);
END FIND_IDS_NOT_IN_LIST;