Oracle SQL Query rewrite: Co-related sub-query to inline View

Co-related sub-query to inline View

Co-related sub-query has join conditions to outer table. So, we can remove join conditions from co-related sub-query and make it to inline view or make it NOT EXISTS sub-query.

This is an article of Oracle SQL Query rewrite serials.

Take this sql for example:

SELECT sum(salary)
FROM   emp, salary, dept
WHERE  emp.emp_id  = salary.emp_id 
AND    emp.dept_no = dept.dept_no
AND    dept.location = 'SEOUL' 
AND    salary.yyyymmdd = (SELECT MAX(yyyymmdd)
                          FROM   salary s2
                          WHERE  s2.emp_id = salary.emp_id );

SQL will be translated into:

SELECT SUM(salary)
FROM   emp,
       salary,
       dept
WHERE  emp.emp_id = salary.emp_id
       AND emp.dept_no = dept.dept_no
       AND dept.location = 'SEOUL'
       AND NOT EXISTS (SELECT 1
                       FROM   salary s2
                       WHERE  s2.emp_id = salary.emp_id
                              AND salary.yyyymmdd < s2.yyyymmdd) 

or

SELECT SUM(salary)
FROM   emp,
       salary,
       dept,
       (SELECT s2.emp_id,
               Max(yyyymmdd) MAX_yyyymmdd
        FROM   salary s2
        GROUP  BY s2.emp_id) epi001
WHERE  emp.emp_id = salary.emp_id
       AND emp.dept_no = dept.dept_no
       AND dept.location = 'SEOUL'
       AND salary.yyyymmdd = epi001.MAX_yyyymmdd
       AND epi001.emp_id = salary.emp_id 

Download this demo: C# version