Oracle SQL intersect between select and simple collection

Question

I would like to write an SQL script like this:

select count(*) from table1 t1 where exists (
    select t2.desired_col from table2 t2 where t1.ID = t2.reference_t1_id 
        intersect 
    (2, 5, 9, 10)
);

The goal is to tell me, how many entries in t1 have at least one common value between desired_col of t2 and a given collection (i.e. (2, 5, 9, 10)). t2 has a ManyToOne relationship to t1. However, it seems I cannot intersect between a select and a simple collection. As a workaround, I wrapped the given collection like this:

select count(*) from table1 t1 where exists (
    select t2.desired_col from table2 t2 where t1.ID = t2.reference_t1_id 
        intersect  
    select desired_col from table t2 where desired_col in (2, 5, 9, 10)
);

I think this solution looks a little ugly and was wondering, if there is a better way to find the intersect between a select statement and a simple collection.

Answer 1

You need to turn your list of IDs into an actual set, which you can then use in SQL with a table() function. Something like this:

select count(*) from table1 t1 
where exists (
    select t2.desired_col from table2 t2 
    where t1.ID = t2.reference_t1_id 
    intersect
     select * from table(sys.odcinumberlist (2, 5, 9, 10))
);

There are several variant solutions. For instance, instead of intersection you could extend the WHERE clause with an IN subquery:

and t2.desired_col in (select * from table(sys.odcinumberlist (2, 5, 9, 10)))

or indeed

and t2.desired_col in (2, 5, 9, 10)

This uses the Oracle supplied collection type, sys.odcinumberlist, which is an array of numbers.