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.
SQL> SQL>-- from w w w. j a va2 s . co 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>