Using WITH clause

WITH clause can be used only with the SELECT clause and can hold more than one query. The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block.

Using WITH clause has following advantages:

• It enables users to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
• It can improve performance of a large query by storing the result of a query block having the WITH clause in the user's temporary tablespace

Example:
SQL> with emp_sal as(select sno, sum(salary) as tot_sal from sample group by sno),
avg_sal as (select sum(tot_sal)/count(*) as tot_avg from emp_sal)
select * from emp_sal where tot_sal > (select tot_avg from avg_sal)
order by sno;

SNO TOT_SAL
---------- ----------
1 23000
2 10000
4 10000