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) 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