Example of using With Clause in Oracle

With Clause is used to right the inline query or temporary table query. It help to improve query speed for complex subqueries. With help of With Clause it make the query more read able form.

With Clause syntax:

WITH ref_name AS
  (the aggregated or function based SQL statement)
SELECT   (query using the ref_name as join with select statement);

Example of using With Clause:

With dept_sum_salary as
(
select department_id,sum(salary) as sum_salary from employees group by department_id)
select department_name,b.sum_salary from departments a , dept_sum_salary b where a.department_id = b.department_id;

DEPARTMENT_NAME                SUM_SALARY
------------------------------ ----------
Administration                       4400
Marketing                           19000
Purchasing                          24900
Human Resources                      6500
Shipping                           156400
IT                                  28800
Public Relations                    10000
Sales                              304500
Executive                           58000
Finance                             51608
Accounting                          20308


Other way to write with using SUBQUery but if we use then 3 or 4 subquery it make the query more complex.

select department_name,b.sum_salary from departments a , 
(select department_id,sum(salary) as sum_salary from employees group by department_id) b where a.department_id = b.department_id;

DEPARTMENT_NAME                SUM_SALARY
------------------------------ ----------
Administration                       4400
Marketing                           19000
Purchasing                          24900
Human Resources                      6500
Shipping                           156400
IT                                  28800
Public Relations                    10000
Sales                              304500
Executive                           58000
Finance                             51608
Accounting                          20308

Example of multiple subquery used as WITH Clause:

WITH dept_sum_salary AS (
    SELECT department_id, SUM(salary) as sum_salary 
    FROM   employees e group by department_id
),
  dept_avg_salary AS (
    SELECT department_id,SUM(salary)/COUNT(*) dept_Avg_salary FROM employees group by department_id)
SELECT * FROM  dept_sum_salary a , dept_avg_Salary b
where a.department_id= b.department_id;


DEPARTMENT_ID SUM_SALARY DEPARTMENT_ID DEPT_AVG_SALARY
------------- ---------- ------------- ---------------
           50     156400            50      3475.55556
           40       6500            40            6500
          110      20308           110           10154
           90      58000            90      19333.3333
           30      24900            30            4150
           70      10000            70           10000
           10       4400            10            4400
           20      19000            20            9500
           60      28800            60            5760
          100      51608           100      8601.33333
           80     304500            80      8955.88235

11 rows selected.