Rewriting Correlated Subquery with "not in" clause

Question

For obvious performance reasons, I would like to rewrite an existing Oracle SQL query that includes correlated subqueries involving "not in" clauses. Can this be accomplished through outer joins or perhaps some other technique?

Here is the code:

SELECT TRIM(et.event_id), TRIM(et.cancel_evt_id)
FROM external_transactions et
JOIN transaction_type tt
    ON et.transaction_type_id = tt.transaction_type_id
WHERE et.acct = 'ABCDEF' 
AND tt.transaction_type_class != 'XYZXYZ'
AND 
(
    TRIM(et.event_id) NOT IN
        (
        SELECT TRIM(t1.transaction_evt_id)
        FROM transactions t1
        WHERE t1.acct = et.acct
        AND t1.asset_id = et.asset_id
        AND t1.cancel_flag = 'N' 
        )
    OR TRIM(et.cancel_evt_id) NOT IN
        (
        SELECT TRIM(t2.cancel_evt_id)
        FROM transactions t2
        WHERE t2.acct = et.acct
        AND t2.asset_id = et.asset_id
        AND t2.cancel_flag = 'Y'
        )
)
;

Answer 1

Aside from comment, this is assuming your "ID" columns are integer based and not string, don't try to convert them.

Also, to help optimize the query, I would ensure you have indexes

External_Transactions    ( acct, event_id, cancel_evt_id )
Transaction_Type   ( transaction_type_id, transaction_type_class )
Transactions ( transaction_evt_id, acct, asset_id, cancel_flag )
Transactions ( cancel_evt_id, acct, asset_id, cancel_flag )


SELECT 
      et.event_id, 
      et.cancel_evt_id
   FROM 
      external_transactions et
         JOIN transaction_type tt
            ON et.transaction_type_id = tt.transaction_type_id
           AND tt.transaction_type_class != 'XYZXYZ'
         LEFT OUTER JOIN transactions t1
            ON  et.event_id = t1.transaction_evt_id
            AND et.acct = t1.acct
            AND et.asset_id = t1.asset_id
            AND t1.cancel_flag = 'N'
         LEFT OUTER JOIN transactions t2
            ON  et.cancel_evt_id = t2.cancel_evt_id
            AND et.acct = t2.acct
            AND et.asset_id = t2.asset_id
            AND t2.cancel_flag = 'Y'
   WHERE 
          et.acct = 'ABCDEF' 
      AND (    t1.transaction_evt_id IS NULL
            OR t2.cancel_evt_id IS NULL )

You might even benefit slightly if the transaction table had an index on

Transactions ( acct, asset_id, cancel_flag, transaction_evt_id, cancel_evt_id )

and the left-join was like

SELECT 
      et.event_id, 
      et.cancel_evt_id
   FROM 
      external_transactions et
         JOIN transaction_type tt
            ON et.transaction_type_id = tt.transaction_type_id
           AND tt.transaction_type_class != 'XYZXYZ'
         LEFT OUTER JOIN transactions t1
            ON  et.acct = t1.acct
            AND et.asset_id = t1.asset_id
            AND ( 
                  (   t1.cancel_flag = 'N'
                  AND et.event_id = t1.transaction_evt_id )
                OR
                  (   t1.cancel_flag = 'Y'
                  AND et.cancel_event_id = t1.cancel_evt_id )
                )
   WHERE 
          et.acct = 'ABCDEF' 
      AND t1.transaction_evt_id IS NULL

In both cases, the indexes would be COVERING indexes so it did not have to go back to the raw data pages to confirm other elements of the records

Answer 2

Using NOT EXISTS:

SELECT TRIM(et.event_id), TRIM(et.cancel_evt_id)
FROM external_transactions et
JOIN transaction_type tt
    ON et.transaction_type_id = tt.transaction_type_id
WHERE et.acct = 'ABCDEF' 
AND tt.transaction_type_class != 'XYZXYZ'
AND NOT EXISTS
        (
        SELECT 1
        FROM transactions t1
        WHERE t1.acct = et.acct
        AND t1.asset_id = et.asset_id
        AND ( (   t1.cancel_flag = 'N'
              AND TRIM(et.event_id) = TRIM(t1.transaction_evt_id) )
            OR
              (   t1.cancel_flag = 'Y'
              AND TRIM(et.cancel_evt_id) = TRIM(t1.cancel_evt_id) )
            )
        );