With Clause

 WITH Clause 
======================================
What?
With Clause is a Sub-Query factoring or (Common Table Expressions-CTEs)
This features is introduced in Oracle 9i.Using this feature:
We assign query result set to a temporary tables.
We can access data from temporary tables in main Select clause as many times we require with out repeating the sub-queries.
When?
Usage of (2,3 level-Nested Queries) can 
be minimized or avoided when using With Clause.
If we use sub-queries up to 1 level then performance may be good but when we use 2 level or 3 level sub-queries then there may chance of performance issues when we try to fetch large large volume of data.


How to implement CTEs
======================
SELECT deptno,
       Sum(sal) total_sal
FROM   emp
GROUP  BY deptno; --fetch sum of sal for each dept.
SELECT Avg(total_sal)
FROM  (SELECT deptno,
              Sum(sal) total_sal
       FROM   emp
       GROUP  BY deptno); --fetch avg of sal for all the deptno's
--fetching deptno whoes sum sal is greater than the avg of all deptno's. 
SELECT deptno,
       Sum(sal)
FROM   emp
GROUP  BY deptno
HAVING Sum(sal) > (SELECT Avg(total_sal)
                   FROM  (SELECT deptno,
                                 Sum(sal) total_sal
                          FROM   emp
                          GROUP  BY deptno));
--fetching deptno whoes sum sal is greater than the avg of all deptno's.
SELECT deptno,
       Sum(sal)
FROM   emp
GROUP  BY deptno
HAVING Sum(sal) < (SELECT Avg(total_sal)
                   FROM  (SELECT deptno,
                                 Sum(sal) total_sal
                          FROM   emp
                          GROUP  BY deptno)); 


The above is 2 level sub-query and it takes time to process the results in case of large volumes. So, let us try with With Clause.
----fetching deptno whoes sum sal is greater than the avg of all deptno's
WITH q1
     AS (SELECT deptno,
                Sum(sal) total_sal
         FROM   emp
         GROUP  BY deptno),
     q2
     AS (SELECT Avg(total_sal) avg_sal
         FROM   q1)
SELECT q1.deptno,
       q1.total_sal
FROM   q1,
       q2
WHERE  q1.total_sal > q2.avg_sal; 
----fetching deptno whoes sum sal is less than the avg of all deptno's.
WITH q1
     AS (SELECT deptno,
                Sum(sal) total_sal
         FROM   emp
         GROUP  BY deptno),
     q2
     AS (SELECT Avg(total_sal) avg_sal
         FROM   q1)
SELECT q1.deptno,
       q1.total_sal
FROM   q1,
       q2

WHERE  q1.total_sal<q2.avg_sal;

Please Watch on YouTube: Click Here