"IN" examples

gistfile1.sql

SQL> create function xt_fire (p int) return int as
  2  begin
  3    dbms_output.put_line('fire '||p);
  4    return p;
  5  end;
  6  /

Function created.

SQL> set serverout on

SQL> select * from dual where 1 in (xt_fire(1),xt_fire(2),xt_fire(3));

D
-
X

1 row selected.

fire 1

SQL> -- with filter
SQL> with t as (select 1 n from dual)
  2  select *
  3  from t
  4  where n in (select/*+ no_unnest */ xt_fire(level) from dual connect by level<=3);

         N
----------
         1

1 row selected.

fire 1

SQL> -- with nested loops:
SQL> with t as (select 1 n from dual)
  2  select *
  3  from t
  4  where n in (select xt_fire(level) from dual connect by level<=3);

         N
----------
         1

1 row selected.

fire 1
fire 2
fire 3