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

Are you doing a `JOIN` on some other table, and comparing the results? You can always do an `OUTER JOIN` on this table, against the other, and test which ones are `Null` in the second table. — yurisich, Dec 29 '11 at 03:38
No, unfortunately a join will not work. We have IDs from one system and need to determine which of those IDs are not in another system. (Completely different systems and databases.) For example, we have employee IDs from the payroll system and need to find which of those IDs are not in the parking registration system. Thanks. — Lance Miller, Dec 29 '11 at 03:44
A join would work. You just might need to do some setup first, like putting the "other" ID's into a table of their own that can be joined. — wadesworld, Dec 29 '11 at 03:55
@wadesworld Thanks. I thought about a temp table. But I was under the impression that temp tables were expensive operations. And this is a query that will need to be run frequently. So from my limited knowledge on SQL optimization, I would think the programmatic solution would be more efficient than using a temp table. — Lance Miller, Dec 29 '11 at 03:59
@antisanity They are on different servers. And we do not have access to the originating system or database. The IDs are being submitted via a service request. We then need to extract the IDs we do not have records for. — Lance Miller, Dec 29 '11 at 04:28
What @antisanity is suggesting is the same thing I laid out in my answer below, though I used a nested table rather than a varray. Same concept though. — wadesworld, Dec 29 '11 at 05:15

score 4 · Accepted Answer · edited May 23 '17 at 10:27

4

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;

edited May 23 '17 at 10:27

Community

1
1

answered Dec 29 '11 at 06:46

Florin Ghita

17,525
6
57
76

Thanks, that works beautifully. I had searched for such a function. Guess I simply didn't have the correct keywords. Thanks for your help. – Lance Miller Dec 29 '11 at 14:00

score 2 · Answer 2 · answered Dec 29 '11 at 04:55

I don't have an Oracle instance handy to test and make sure my syntax is perfect, but this should work, or get you on the right track anyhow.

Pass the list in as a nested table and then treat that as a real table. You pass the list in as an array from Java/Perl or whatever language you're using to call the stored proc.

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;

Thanks @wadesworld. I think I can make that work. I was able to create the procedure as you describe. (Your syntax was dead on.) But I need to pack it in for the night as it's **very** late in my timezone. I just need to try calling it from my java code in the morning. I'll follow up some more then. Thanks again for your assistance. — Lance Miller, Dec 29 '11 at 05:54

edwardsharp · Answer 3 · 2011-12-29T18:15:40.837

1

How about this:

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

or

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

edited Dec 29 '11 at 18:15

answered Dec 29 '11 at 03:49

edwardsharp

1,232
10
17

That's resulting in an error: _ORA-00904: "id": invalid identifier_. What I tried: `SELECT * FROM dual WHERE id NOT IN (SELECT id FROM my_table WHERE id IN ('1', '2', '3', '4'))` I believe it is not liking the identifier in the where clause. Can selects from `dual` have a WHERE clause? Even the simple query `SELECT 'xyz' as id FROM DUAL WHERE id IS NOT NULL` results in the same error. – Lance Miller Dec 29 '11 at 04:23
watch your quotes! did you mean ID? – edwardsharp Dec 29 '11 at 04:47
2

Backquotes are invalid characters in SQL identifiers – Dec 29 '11 at 08:10

SQL query to get ids NOT in a table

3 Answers3