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