Hafiz Usman aftab Hafiz Usman aftab - 1 month ago 8
SQL Question

How to work with Table joins and Inline View

Can anyone help me to understand this SQL query; I don't understand the concept behind it:

SELECT
t2.*,
(
SELECT SUM (salary)
FROM (
SELECT
tb1.*,
ROWNUM AS rnk
FROM (
SELECT *
FROM employees e
ORDER BY E.DEPARTMENT_ID, E.EMPLOYEE_ID
) tb1
) tb2
WHERE tb2.DEPARTMENT_ID = t2.DEPARTMENT_ID AND tb2.rnk <= t2.rnk
) dept_salary
FROM (
SELECT
t1.*,
ROWNUM AS rnk
FROM (
SELECT
e.employee_id,
e.first_name,
e.department_id,
e.salary
FROM employees e
ORDER BY E.DEPARTMENT_ID, E.EMPLOYEE_ID
) t1
) t2

Answer

There are different type of SubQuery.

  1. Scalar Sub Query : Query that is totally independent from the main query.

    select empno, (select count(*) from emp) total_emp from emp

here the subquery is totally independent of the main query.

  1. Corelated Sub Query: The sub query reffers to the main query.

    select empno, (select deptname from dept d where e.deptno = d.deptno) deptname from emp e

  2. Inline view :A sub query that is used as a table or view in a SQL

    select * from (select empno, deptname from emp e, dept d where e.deptno = d.deptno) where sal > 100

From your query:

SELECT
    t2.*,
    (
        SELECT SUM (salary)  -- start Corelated Sub Query
        FROM (
            SELECT
            tb1.*, 
            ROWNUM AS rnk
            FROM ( 
                SELECT *  -- start Scalar Sub Query
                FROM employees e
                ORDER BY E.DEPARTMENT_ID, E.EMPLOYEE_ID -- end Scalar Sub Query
            ) tb1 
        ) tb2
        WHERE   tb2.DEPARTMENT_ID = t2.DEPARTMENT_ID AND tb2.rnk <= t2.rnk -- end Corelated Sub Query
    ) dept_salary
FROM (
    SELECT   -- start inline view
        t1.*, 
        ROWNUM AS rnk
    FROM (  
        SELECT   
            e.employee_id,
            e.first_name,
            e.department_id,
            e.salary
        FROM employees e
        ORDER BY   E.DEPARTMENT_ID, E.EMPLOYEE_ID
    ) t1
) t2  -- end inline view

NB:Now you can find out sub query within sub query and inline view within inline view in your question.