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

# Oracle SQL Query rewrite: (NOT) EXISTS sub-query to (NOT) IN sub-query

(NOT) EXISTS sub-query to (NOT) IN sub-query

This is an article of Oracle SQL Query rewrite serials.

Take this sql for example:

    SELECT tax_line_id
      FROM zx_lines zl
     WHERE application_id = :B3
       AND entity_code = :B2
       AND event_class_code = :B1
       AND (process_for_recovery_flag = 'Y'
             OR item_dist_changed_flag = 'Y')
       AND EXISTS (SELECT 1
                     FROM zx_lines_det_factors
                    WHERE application_id = zl.application_id
                      AND entity_code = zl.entity_code
                      AND event_class_code = zl.event_class_code
                      AND trx_id = zl.trx_id
                      AND trx_line_id = zl.trx_line_id
                      AND trx_level_type = zl.trx_level_type
                      AND event_id = :B4);

SQL will be translated into:

    SELECT tax_line_id
    FROM   zx_lines zl
    WHERE  application_id = :B3
           AND entity_code = :B2
           AND event_class_code = :B1
           AND ( process_for_recovery_flag = 'Y'
                  OR item_dist_changed_flag = 'Y' )
           AND ( zl.application_id, zl.entity_code, zl.event_class_code, zl.trx_id,
                 zl.trx_line_id, zl.trx_level_type ) IN (SELECT application_id,
                                                                entity_code,
                                                                event_class_code,
                                                                trx_id,
                                                                trx_line_id,
                                                                trx_level_type
                                                         FROM   zx_lines_det_factors
                                                         WHERE  event_id = :B4)

Download this demo: C# version