Oracle SQL – WITH Clause

Last Updated on :September 29, 2019

SQL WITH Clause was introduced by oracle in oracle 9.2 release. SQL WITH clause also known as subquery factoring.

WITH clause will create temporary dataset and will allow you to assign a name to the subquery block.

The advantage is that the temporary dataset is generated once and can be referenced many places with in the main query.

Example

Using WITH clause if you want to display department name and number of employees in each department. You can place the subquery that retrieves employee count with DEPTNO wise from EMP table.

The result set generated by the subquery is referenced with name ‘em’. You can use reference name ‘em’ in the main query and retrieves department name from DEPT table.

WITH em AS (SELECT deptno, COUNT (*) AS emp_count
                FROM emp
            GROUP BY deptno)
SELECT d.dname, em.emp_count
  FROM dept d, em
 WHERE d.deptno = em.deptno;

Output

DNAME,EMP_COUNT
"ACCOUNTING",3
"RESEARCH",5
"SALES",6