Oracle SQL - Build a factored subquery in three step

  1. HOME
  2. Oracle SQL
  3. Subquery
  4. Subquery

Introduction

First step: 

select   x.deptno
,        avg(x.msal) avg_sal
from     emp x
group by x.deptno;

Second step: 

WITH g AS
      (select   x.deptno
       ,        avg(x.msal) avg_sal
       from     emp x
       group by x.deptno)
select *
from   g;

Third step 

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;

The multiple subqueries in the WITH clause can refer to any subquery name that you defined earlier in the same WITH clause.

For example, the definition of subquery V2 can refer to V1 in its FROM clause, and the definition of V3 can refer to both V1 and V2: 

WITH   v1 AS (select ... from ...)
    ,  v2 AS (select ... from V1)
    ,  v3 AS (select ... from V2 join V1)
   select ...
   from   ...

Demo

SQL>
SQL> drop table emp;

Table dropped.-- www. ja  v a 2 s  .  co 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>
SQL> select   x.deptno
  2  ,        avg(x.msal) avg_sal
  3  from     emp x
  4  group by x.deptno;

   DEPTNO |   AVG_SAL
--------- | ---------
    30    |  03087.50
    2     |  02375.00
    1     |  02570.00

3 rows selected.

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 *
  7  from   g;

   DEPTNO |   AVG_SAL
--------- | ---------
    30    |  03087.50
    2     |  02375.00
    1     |  02570.00

3 rows selected.

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>

Related Topic