Skip to content
No results
We use cookies to ensure that we give you the best experience on our website.

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