Skip to content
No results
We use cookies to ensure that we give you the best experience on our website.

# 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