Oracle SQL Query rewrite: IN clause to UNION ALL statement

IN clause to UNION ALL statement

COL1 IN (‘A’, ‘B’, ‘C’) means that COL1 is ‘A’ or ‘B’ or ‘C’ and or can be replaced with UNION ALL.
So. we can re-write IN list values using UNION ALL.
Also, we can re-write IN list values using ROWID, inline view and UNION ALL.

This is an article of Oracle SQL Query rewrite serials.

Take this sql for example:

SELECT *
FROM   fnd_concurrent_requests
WHERE  status IN ('R','E')
AND    request_date >= SYSDATE - 1 ;

SQL will be translated into:

SELECT *
FROM   fnd_concurrent_requests
WHERE  status = 'R'
       AND request_date >= SYSDATE - 1
UNION ALL
SELECT *
FROM   fnd_concurrent_requests
WHERE  status = 'E'
       AND request_date >= SYSDATE - 1 

Download this demo: C# version