The outer join operator can appear on only one side of an expression in the join condition. You get an ORA-01468 error if you attempt to use it on both sides. For example:

                     SELECT d.dept_id, d.name, l.regional_group
                     FROM department d, location l
                     WHERE d.location_id (+) = l.location_id(+);
WHERE d.location_id (+) = l.location_id(+)
                        *
ERROR at line 3:
ORA-01468: a predicate may reference only one outer-joined table

What this means is that the outer join operation using the (+) operator is unidirectional. You can’t perform a bidirectional outer join (known as a full outer join) using the (+) operator.

If a join involves more than two tables, then one table can’t be outer joined with more than one other table in the query. Consider the following tables:

                     DESC employee
Name                                      Null?    Type
----------------------------------------- -------- --------------
EMP_ID                                    NOT NULL NUMBER(5)
FNAME                                              VARCHAR2(20)
LNAME                                              VARCHAR2(20)
DEPT_ID                                   NOT NULL NUMBER(5)
MANAGER_EMP_ID                                     NUMBER(5)
SALARY                                             NUMBER(5)
HIRE_DATE                                          DATE
JOB_ID                                             NUMBER(3)

DESC job
 Name                            Null?    Type
 ------------------------------- -------- ----
 JOB_ID                          NOT NULL NUMBER(3)
 FUNCTION                                 VARCHAR2(30)

DESC department
 Name                            Null?    Type
 ------------------------------- -------- ----
 DEPT_ID                         NOT NULL NUMBER(5)
 NAME                                     VARCHAR2(20)
 LOCATION_ID                              NUMBER(3)

If you want to list the job function and department name of all the employees, and you want to include all the departments and jobs that don’t have any corresponding employees, you would probably attempt to join the employee table with the job and department tables, and make both the joins outer joins. However, since one table can’t be outer-joined with more than one table you get the following error:

                     SELECT e.lname, j.function, d.name
                     FROM employee e, job j, department d
                     WHERE e.job_id (+) = j.job_id
                     AND e.dept_id (+) = d.dept_id;

WHERE e.job_id (+) = j.job_id
                   *
ERROR at line 3:
ORA-01417: a table may be outer joined to at most one other table

As a workaround, you can create a view with an outer join between two tables, and then outer join the view to the third table:

CREATE VIEW v_emp_job
AS SELECT e.dept_id, e.lname, j.function
FROM employee e, job j
WHERE e.job_id (+) = j.job_id;

SELECT v.lname, v.function, d.name
FROM v_emp_job v, department d
WHERE v.dept_id (+) = d.dept_id;

Instead of creating a view, you can use an inline view to achieve the same result:

SELECT v.lname, v.function, d.name
FROM (SELECT e.dept_id, e.lname, j.function
      FROM employee e, job j
      WHERE e.job_id (+) = j.job_id) v, department d
WHERE v.dept_id (+) = d.dept_id;

Inline views are discussed in Chapter 5.

A condition containing the (+) operator may not use the IN operator. For example:

                     SELECT e.lname, j.function
                     FROM employee e, job j
                     WHERE e.job_id (+) IN (668, 670, 667);
WHERE e.job_id (+) IN (668, 670, 667)
                   *
ERROR at line 3:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN

An outer join condition containing the (+) operator may not be combined with another condition using the OR operator. For example:

                     SELECT e.lname, d.name
                     FROM employee e, department d
                     WHERE e.dept_id = d.dept_id (+)
                     OR d.dept_id = 10;
WHERE e.dept_id = d.dept_id (+)
                *
ERROR at line 3:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN

A condition containing the (+) operator may not involve a subquery. For example:

                     SELECT e.lname
                     FROM employee e
                     WHERE e.dept_id (+) =
                     (SELECT dept_id FROM department WHERE name = 'ACCOUNTING'),
(SELECT DEPT_ID FROM DEPARTMENT WHERE NAME = 'ACCOUNTING')
                                                         *
ERROR at line 4:
ORA-01799: a column may not be outer-joined to a subquery

As a workaround, you can use an inline view to achieve the desired effect:

SELECT e.lname
FROM employee e,
(SELECT dept_id FROM department WHERE name = 'ACCOUNTING') V
WHERE e.dept_id (+) = v.dept_id;