Oracle SQL - Old Oracle-Specific Outer Join Syntax

Introduction

The following code shows the (+) Outer Join Syntax

select d.deptno, d.location
,      e.ename, e.init
from   emp e, departments d
where  e.deptno(+) = d.deptno
order  by d.deptno, e.ename;

From the result below you can see, department 40 now also appears in the result.

The effect of the addition (+) in the WHERE clause has combined department 40 with two null values for the employee data.

You must add the (+) operator in the right places in your SQL command, namely on the "outer" or optional side of the join condition.

Failing to do so normally results in disabling the outer join effect.

Demo

SQL>
SQL>--  ww  w  .  j a v a  2s.  c o  m
SQL> drop table emp;

Table dropped.

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 d.deptno, d.location
  2  ,      e.ename, e.init
  3  from   emp e, departments d
  4  where  e.deptno(+) = d.deptno
  5  order  by d.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>

Related Topic