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

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

This is an article of Oracle SQL Query rewrite serials.

For this input sql:

SELECT *
FROM   emp, salary
WHERE  emp.emp_id = salary.emp_id
AND    emp.dept_no IN (SELECT dept_no
                       FROM   dept
                       WHERE  dept.location = 'SEOUL'
                       )
AND    emp.emp_id NOT IN (SELECT emp_id
                          FROM   emp_retirement c
                          WHERE  c.retire_type = 'NORMAL'
                          );

SQL will be translated into:

SELECT *
FROM   emp,
       salary
WHERE  emp.emp_id = salary.emp_id
       AND EXISTS (SELECT 1
                   FROM   dept
                   WHERE  dept.location = 'SEOUL'
                          AND emp.dept_no = dept.dept_no)
       AND emp.emp_id NOT IN (SELECT emp_id
                              FROM   emp_retirement c
                              WHERE  c.retire_type = 'NORMAL') 

or

SELECT *
FROM   emp,
       salary
WHERE  emp.emp_id = salary.emp_id
       AND emp.dept_no IN (SELECT dept_no
                           FROM   dept
                           WHERE  dept.location = 'SEOUL')
       AND NOT EXISTS (SELECT 1
                       FROM   emp_retirement c
                       WHERE  c.retire_type = 'NORMAL'
                              AND emp.emp_id = c.emp_id) 

Download this demo: C# version