2019-05-22

More than 1000 elements in IN clause

All solutions are well-known:

-- 1. The most elegant
WHERE ('fake', val) IN (
  ('fake', val1),
  ('fake', val2),
  ('fake', val3),  
  ...
); 
-- 2. The most obvious
val IN (
SELECT val1 FROM dual UNION ALL
SELECT val2 FROM dual UNION ALL
SELECT val3 FROM dual UNION ALL
...
) 
-- 3. XMLTable magic
val IN (SELECT trim(COLUMN_VALUE) FROM XMLTABLE('"val1","val2","val3",...')) 

I just want to mention one thing about XMLTable solution: string parameter for this function can exceed 4000 symbols (checked in Oracle 11.2.0.4)