Oracle SQL - New Outer Join Syntax

Introduction

The following code shows how to use the new ANSI/ISO outer join syntax.


select deptno, d.location
,      e.ename, e.init
from   emp e
       right outer join
       departments d
       using (deptno)
order  by deptno, e.ename;

Here, we used a RIGHT OUTER JOIN, because we suspect the presence of rows at the right-hand side (DEPARTMENTS table) without corresponding rows at the left-hand side (EMPLOYEES table).

If you switched the two table names in the FROM clause, you would need the LEFT OUTER JOIN operator.

Oracle supports the FULL OUTER JOIN syntax, where both tables participating in the join operation handle rows without corresponding rows on the other side in a special way.

The outer join operator is useful when aggregating or summarizing data.

For example, to produce a course overview showing the number of attendees for each scheduled course.

We need to see all scheduled courses for which no registrations are entered yet.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- ww w  . j  a v  a 2s.c  om

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> select deptno, d.location
  2  ,      e.ename, e.init
  3  from   emp e
  4         right outer join
  5         departments d
  6         using (deptno)
  7  order  by deptno, e.ename;

   DEPTNO | LOCATION | ENAME    | INIT
--------- | -------- | -------- | -----
    1     | NEW YORK | BLAKE    | R
    1     | NEW YORK | CLARK    | AB
    1     | NEW YORK | KING     | CC
    1     | NEW YORK | MARY     | ABC
    1     | NEW YORK | WARD     | TF
    2     | DALLAS   | ADAMS    | AA
    2     | DALLAS   | FORD     | MG
    2     | DALLAS   | JACK     | JM
    2     | DALLAS   | SCOTT    | DEF
    2     | DALLAS   | SMITH    | N
    30    | CHICAGO  | ALLEN    | JAM

   DEPTNO | LOCATION | ENAME    | INIT
--------- | -------- | -------- | -----
    30    | CHICAGO  | BREAD    | JJ
    30    | CHICAGO  | BROWN    | P
    30    | CHICAGO  | JONES    | R
    4     | BOSTON   |  [N/A]   |  [N/A
          |          |          | ]


15 rows selected.

SQL>

Oracle SQL - New Outer Join Syntax

Introduction

Demo

Related Topic