Oracle SQL - Partitioned Outer Joins

Introduction

Consider the following regular right outer join.

break on department skip 1 on job

select d.dname as department
,      e.job   as job
,      e.ename as employee
from   emp e
       right outer join
       departments d
       using (deptno)
order  by department, job;

The SQL*Plus BREAK command allows you to enhance the readability of query results.

We use the BREAK command to suppress repeating values in the DEPARTMENT and JOB columns, and to insert an empty line between the departments.

The result shows 15 rows, as expected.

We have 14 emp, and the additional row is added by the outer join for the HR department without emp.

The following code uses partitioned Outer Join

select d.dname as department
,      e.job   as job
,      e.ename as employee
from   emp e
       PARTITION BY (JOB)
       right outer join
       departments d
       using (deptno)
order  by department, job;

The script shows at least one row for each combination of a department and a job highlighting all nonexisting department/job combinations.

A regular outer join considers full tables when searching for matching rows in the other table.

The partitioned outer join works as follows:

  • Split the driving table in partitions based on a column expression (for example, this column JOB).
  • Produce separate outer join results for each partition with the other table.
  • Merge the results of the previous step into a single result.

Partitioned outer joins are especially useful when you want to aggregate information over the time dimension.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from  w  w  w.  ja  va  2 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> drop table departments;

Table dropped.

SQL>
SQL> create table departments(
  2  deptno    NUMBER(2)     primary key,
  3  dname     VARCHAR2(10)  not null unique check (dname = upper(dname)),
  4  location  VARCHAR2(8)   not null        check (location = upper(location)),
  5  mgr       NUMBER(4)) ;
SQL>
SQL> insert into departments values (10,'ACCOUNTING','NEW YORK',7007);
SQL> insert into departments values (20,'TRAINING',  'DALLAS',  7004);
SQL> insert into departments values (30,'SALES',     'CHICAGO', 7006);
SQL> insert into departments values (40,'HR',        'BOSTON',  7009);
SQL>
SQL>
SQL> break on department skip 1 on job
SQL>
SQL> select d.dname as department
  2  ,      e.job   as job
  3  ,      e.ename as employee
  4  from   emp e
  5         right outer join
  6         departments d
  7         using (deptno)
  8  order  by department, job;

DEPARTMENT | JOB      | EMPLOYEE
---------- | -------- | --------
ACCOUNTING | ADMIN    | MARY
           | DIRECTOR | KING
           | MANAGER  | BLAKE
                      | CLARK
           | SALESREP | WARD

HR         |  [N/A]   |  [N/A]

SALES      | ADMIN    | JONES
           | SALESREP | BROWN
                      | ALLEN

DEPARTMENT | JOB      | EMPLOYEE
---------- | -------- | --------
SALES      | SALESREP | BREAD

TRAINING   | MANAGER  | JACK
           | TRAINER  | ADAMS
                      | FORD
                      | SMITH
                      | SCOTT


15 rows selected.

SQL>
SQL> select d.dname as department
  2  ,      e.job   as job
  3  ,      e.ename as employee
  4  from   emp e
  5         PARTITION BY (JOB)
  6         right outer join
  7         departments d
  8         using (deptno)
  9  order  by department, job;

DEPARTMENT | JOB      | EMPLOYEE
---------- | -------- | --------
ACCOUNTING | ADMIN    | MARY
           | DIRECTOR | KING
           | MANAGER  | BLAKE
                      | CLARK
           | SALESREP | WARD
           | TRAINER  |  [N/A]

HR         | ADMIN    |  [N/A]
           | DIRECTOR |  [N/A]
           | MANAGER  |  [N/A]
           | SALESREP |  [N/A]

DEPARTMENT | JOB      | EMPLOYEE
---------- | -------- | --------
HR         | TRAINER  |  [N/A]

SALES      | ADMIN    | JONES
           | DIRECTOR |  [N/A]
           | MANAGER  |  [N/A]
           | SALESREP | BROWN
                      | ALLEN
                      | BREAD
           | TRAINER  |  [N/A]

TRAINING   | ADMIN    |  [N/A]

DEPARTMENT | JOB      | EMPLOYEE
---------- | -------- | --------
TRAINING   | DIRECTOR |  [N/A]
           | MANAGER  | JACK
           | SALESREP |  [N/A]
           | TRAINER  | ADAMS
                      | FORD
                      | SMITH
                      | SCOTT


26 rows selected.

SQL>

Related Topic