oracle 11g sql compare larger number of values

Question

I am writing the java program in which I need to compare the large number of List<String> ids to see are these ids are exist in the database ? I know it can be done with big select query select id from my_table where id in ('1','2''3''4''5'...) which leads very big sql query.

is there any other way of doing this in Oracle 11g ?

Thanks

Use BETWEEN if you have distinct ids. – Mihai Mar 06 '14 at 07:30 — Mihai, Mar 06 '14 at 07:30

score 0 · Answer 1 · edited May 23 '17 at 10:31

First of all you should be aware of Oracles IN clause limit (SQL IN Clause 1000 item limit). So you could avoid this if you split your large in into smaller ones like

select id from my_table where id in ('1','2','3') or id in ('4','5'...)

or run multiple requests in Oracle

select id from my_table where id in ('1','2','3');
select id from my_table where id in ('4','5'...);

or there are other possibilites (see linked question).

oracle 11g sql compare larger number of values

1 Answers1