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:
Partitioned outer joins are especially useful when you want to aggregate information over the time dimension.
SQL> SQL> drop table emp; Table dropped.-- from w ww . j a va 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> 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>