With Oracle, how can I use an IN clause with multiple Arrays?

user_80779 0
Viewed 73

For a while now, I have been using queries like this when I need to select several entities by ID at once and I can't predict how many entities I need to select:

select * from MY_ENTITY where ID in (select * table(?))`

The parameter is an SQL ARRAY of whatever is the relevant type for my entity's ID.

This is a bit ugly, but the query runs much faster that using something like in (?, ?, ?, ?, ....). On top of that, it doesn't bloat the server-side query cache with almost identical queries that only differ in the number of arguments passed to the IN clause.

So this works pretty well.

However, I have some cases where I would like to the same but with pairs of values. Conceptually, I'd like to do something like:

select * from MY_ENTITY where (TAG_NAME, TAG_VALUE) in (TABLE(?, ?))`

Where each of the parameters are SQL ARRAYs of identical length. But of course, this doesn't work with Oracle.

So far, the only way I've found to make that work is rather horrifying (though it seems to work)

select * 
from MY_ENTITY 
where (TAG_NAME, TAG_VALUE) IN (
    select TAG_NAME, TAG_VALUE
    from
        (select ROWNUM as IDX, column_value as TAG_NAME from TABLE(?)) A,
        (select ROWNUM as IDX, column_value as TAG_VALUE from TABLE(?)) B
    where
        A.IDX = B.IDX
    );

Is there any easier way to do that with Oracle?

oracle arrays sql
1 Answers

If you use the statement EXISTS or NOT EXISTS, the query will be faster.

In these statements, you only need to specify in the WHERE clause the condition to join the tables.

select * 
from MY_ENTITY my_ett
where EXISTS (
    select A.TAG_NAME, B.TAG_VALUE
    from
        (select ROWNUM as IDX, column_value as TAG_NAME from TABLE(?)) A,
        (select ROWNUM as IDX, column_value as TAG_VALUE from TABLE(?)) B
    where
        A.IDX = B.IDX AND A.TAG_NAME = my_ett.TAG_NAME AND B.TAG_VALUE = my_ett.TAG_VALUE
    );
user_128415580
Related