Replace "minus" operator with "not in" or "not exists" - Oracle

Question

I have a query that I want to replace. I don't want to use the "minus" operator.

SELECT t1_col1, t1_col2 
FROM t1 
WHERE (t1_col1, t1_col3) 
IN (SELECT t2_col1, t2_col2 
    FROM t2 
    WHERE t2_col3 = '123456' 
    MINUS (SELECT t3_col1, t3_col2 
            FROM t3 
            WHERE t3_col4 = '123456' 
            UNION SELECT t3_col3, t3_col2 
                  FROM t3 WHERE t3_col4 = '123456'));

Any help would be appreciated. Thanks!

It is not supported with the framework I'm trying to use. I have tried that but it doesn't display the results I'm expecting, I just replaced the MINUS with AND NOT EXISTS. I don't know if I have to update something else. — Tomarto, Jun 24 '14 at 17:03

score 3 · Accepted Answer · answered Jun 25 '14 at 02:58

SELECT t1_col1, t1_col2 
FROM t1 
WHERE (t1_col1, t1_col3) 
IN 
(
    SELECT t2_col1, t2_col2 
    FROM t2 , (
                SELECT t3_col1 as C1, t3_col2 AS C2
                FROM t3 
                WHERE t3_col4 = '123456' 
                UNION 
                SELECT t3_col3, t3_col2 
                FROM t3 
                WHERE t3_col4 = '123456'
               ) x
    WHERE t2_col3 = '123456' 
    and   t2_col1 = X.C1 (+) 
    and   t2_col2 = x.c2 (+)
    and   X.C1 is  null
    and   x.c2 is  null 
)

Replace "minus" operator with "not in" or "not exists" - Oracle

1 Answers1