Oracle SQL - WITH Clause for Subqueries in the FROM Clause

Introduction

We could have written the same query with a slightly different syntax.

This construct is called a factored subquery (or subquery factoring).

WITH g AS
      (select   x.deptno
       ,        avg(x.msal) avg_sal
       from     emp x
       group by x.deptno)
select e.ename, e.init, e.msal
from   emp e
       join      g
       using (deptno)
where  e.msal > g.avg_sal;

Here, we have isolated the subquery definition from the actual query.

This makes the structure of the main query clearer.

Using the WITH clause syntax becomes even more attractive if you refer multiple times to the same subquery from the main query.

You can define as many subqueries as you like in a single WITH clause, separated by commas.

WITH   v1 AS (select ... from ...)
,      v2 AS (select ... from ...)
,      v3 AS ...

select ...
from   ...

Demo

SQL>
SQL> drop table emp;

Table dropped.--   ww w .  j  av a2  s.c o m

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL>
SQL> WITH g AS
  2        (select   x.deptno
  3         ,        avg(x.msal) avg_sal
  4         from     emp x
  5         group by x.deptno)
  6  select e.ename, e.init, e.msal
  7  from   emp e
  8         join      g
  9         using (deptno)
 10  where  e.msal > g.avg_sal;

ENAME    | INIT  |      MSAL
-------- | ----- | ---------
JONES    | R     |  08000.00
FORD     | MG    |  03000.00
SCOTT    | DEF   |  03000.00
JACK     | JM    |  02975.00
KING     | CC    |  05000.00
BLAKE    | R     |  02850.00

6 rows selected.

SQL>

Oracle SQL - WITH Clause for Subqueries in the FROM Clause

Introduction

Demo

Related Topic