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