With Clause

With Clause:

With Clause works as temporary table while writing queries. We use  this clause simplify our queries when we use aggregate function in queries.


Syntax:

With <any_name> as 
(sql query)

eg:

with avg_sal as (
select avg(sal) avgsal,deptno from emp
group by deptno)
select ename,empno,e.deptno ,dname,avgsal from emp e join dept D1 on d1.deptno  = e.deptno

join avg_Sal a1 on a1.deptno  = e.deptno

here avg_sal works as temporary table for this query and used in join as well to get avgsal column from table.

SQL> with avg_sal as (

  2  select avg(sal) avgsal,deptno from emp

  3  group by deptno)

  4  select ename,empno,e.deptno ,dname,avgsal from emp e join dept D1 on d1.deptno  = e.deptno

  5  join avg_Sal a1 on a1.deptno  = e.deptno

  6  ;

 

ENAME      EMPNO DEPTNO DNAME              AVGSAL

---------- ----- ------ -------------- ----------

MILLER      7934     10 ACCOUNTING         3487.5

KING        7839     10 ACCOUNTING         3487.5

CLARK       7782     10 ACCOUNTING         3487.5

VENKY       1234     10 ACCOUNTING         3487.5

FORD        7902     20 RESEARCH             2175

ADAMS       7876     20 RESEARCH             2175

SCOTT       7788     20 RESEARCH             2175

JONES       7566     20 RESEARCH             2175

SMITH       7369     20 RESEARCH             2175

JAMES       7900     30 SALES          1566.66666

TURNER      7844     30 SALES          1566.66666

BLAKE       7698     30 SALES          1566.66666

MARTIN      7654     30 SALES          1566.66666

WARD        7521     30 SALES          1566.66666

ALLEN       7499     30 SALES          1566.66666

 

15 rows selected