Limitations on Outer Joins : Outer Joins Limitations « Table Joins « Oracle PL/SQL Tutorial






You may only place the outer join operator on one side of the join (not both).

SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    EMPNO         NUMBER(3),
  3    ENAME         VARCHAR2(15 BYTE),
  4    HIREDATE      DATE,
  5    ORIG_SALARY   NUMBER(6),
  6    CURR_SALARY   NUMBER(6),
  7    REGION        VARCHAR2(1 BYTE)
  8  )
  9  /

Table created.

SQL>
SQL> create table job (
  2    EMPNO         NUMBER(3),
  3    jobtitle      VARCHAR2(20 BYTE)
  4  )
  5  /

Table created.

SQL>
SQL> insert into job (EMPNO, Jobtitle) values (1,'Tester');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (2,'Accountant');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (3,'Developer');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (4,'COder');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (5,'Director');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (6,'Mediator');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (7,'Proffessor');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (8,'Programmer');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (9,'Developer');

1 row created.

SQL>
SQL>
SQL> -- prepare data
SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION)
  2               values (1,      'Jason', to_date('19960725','YYYYMMDD'), 1234,              8767,         'E')
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION)
  2               values (2,      'John',  to_date('19970715','YYYYMMDD'), 2341,              3456,         'W')
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION)
  2               values (3,      'Joe',   to_date('19860125','YYYYMMDD'), 4321,              5654,         'E')
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION)
  2               values (4,      'Tom',   to_date('20060913','YYYYMMDD'), 2413,              6787,         'W')
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION)
  2               values (5,      'Jane',  to_date('20050417','YYYYMMDD'), 7654,              4345,         'E')
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION)
  2               values (6,      'James', to_date('20040718','YYYYMMDD'), 5679,              6546,         'W')
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION)
  2               values (7,      'Jodd',  to_date('20030720','YYYYMMDD'), 5438,              7658,         'E')
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION)
  2               values (8,      'Joke',  to_date('20020101','YYYYMMDD'), 8765,              4543,         'W')
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION)
  2               values (9,      'Jack',  to_date('20010829','YYYYMMDD'), 7896,              1232,         'E')
  3  /

1 row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /

     EMPNO ENAME           HIREDATE  ORIG_SALARY CURR_SALARY R
---------- --------------- --------- ----------- ----------- -
         1 Jason           25-JUL-96        1234        8767 E
         2 John            15-JUL-97        2341        3456 W
         3 Joe             25-JAN-86        4321        5654 E
         4 Tom             13-SEP-06        2413        6787 W
         5 Jane            17-APR-05        7654        4345 E
         6 James           18-JUL-04        5679        6546 W
         7 Jodd            20-JUL-03        5438        7658 E
         8 Joke            01-JAN-02        8765        4543 W
         9 Jack            29-AUG-01        7896        1232 E

9 rows selected.

SQL> select * from job
  2  /

     EMPNO JOBTITLE
---------- --------------------
         1 Tester
         2 Accountant
         3 Developer
         4 COder
         5 Director
         6 Mediator
         7 Proffessor
         8 Programmer
         9 Developer

9 rows selected.

SQL>
SQL>
SQL> SELECT e.ename, j.jobtitle FROM employee e, job j WHERE e.empno (+) = j.empno (+);
SELECT e.ename, j.jobtitle FROM employee e, job j WHERE e.empno (+) = j.empno (+)
                                                                    *
ERROR at line 1:
ORA-01468: a predicate may reference only one outer-joined table


SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /

Table dropped.

SQL> drop table job
  2  /

Table dropped.








7.5.Outer Joins Limitations
7.5.1.Limitations on Outer Joins
7.5.2.You cannot use an outer join condition with the IN operator
7.5.3.You cannot use an outer join condition with another join using the OR operator