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 to NOT IN hash anti-join

NOT EXISTS to NOT IN hash anti-join

If SQL statement has NOT EXISTS sub-query then we can re-write it using NOT IN hash anti-join.

This is an article of Oracle SQL Query rewrite serials.

Take this sql for example:

    SELECT sum(salary)
    FROM   emp, salary
    WHERE  emp.emp_id = salary.emp_id
    AND    NOT EXISTS (SELECT 1
                       FROM   dept
                       WHERE  dept.location = 'SEOUL'
                       AND    emp.dept_no   = dept.dept_no
                       ) ;

SQL will be translated into:

    SELECT SUM(salary)
    FROM   emp,
           salary
    WHERE  emp.emp_id = salary.emp_id
           AND emp.dept_no IS NOT NULL
           AND emp.dept_no NOT IN (SELECT /*+ HASH_AJ */ dept.dept_no
                                   FROM   dept
                                   WHERE  dept.location = 'SEOUL'
                                          AND dept.dept_no IS NOT NULL)

Download this demo: C# version