Oracle PL/SQL Tutorial/Table Joins/Outer Joins Limitations
Limitations on Outer Joins
You may only place the outer join operator on one side of the join (not both).
<source lang="sql">
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.</source>
You cannot use an outer join condition with another join using the OR operator
<source lang="sql">
SQL> 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> SELECT e.ename, j.jobtitle FROM employee e, job j WHERE e.empno (+) = j.empno OR e.empno = 1; SELECT e.ename, j.jobtitle FROM employee e, job j WHERE e.empno (+) = j.empno OR e.empno = 1
*
ERROR at line 1: ORA-01719: outer join operator (+) not allowed in operand of OR or IN
SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> drop table job
2 /
Table dropped. SQL></source>
You cannot use an outer join condition with the IN operator
<source lang="sql">
SQL> 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> SELECT e.ename, j.jobtitle
2 FROM employee e, job j 3 WHERE e.empno (+) IN (1, 2, 3, 4); WHERE e.empno (+) IN (1, 2, 3, 4) *
ERROR at line 3: ORA-01719: outer join operator (+) not allowed in operand of OR or IN
SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> drop table job
2 /
Table dropped. SQL></source>