A.2 Old Outer Join Syntax

The old syntax of the outer join is a bit different from that of the inner join, because it includes a special operator called the outer join operator. The outer join operator is a plus sign enclosed in parentheses: (+). This operator is used in a join condition in the WHERE clause following a field name from the table that you wish to be considered the optional table.

For example, to list all the departments even if they are not related to any particular location, you can perform an outer join between the department and the location tables as shown in the following example:

SELECT d.dept_id, d.name, l.regional_group

FROM department d, location l

WHERE d.location_id = l.location_id (+);



   DEPT_ID NAME                 REGIONAL_GROUP

---------- -------------------- ------------------

        10 ACCOUNTING           NEW YORK

        20 RESEARCH             DALLAS

        30 SALES

        40 OPERATIONS           BOSTON

Notice the (+) operator following l.location_id. That makes location the optional table in this join, in the sense that you want to display a row from the department table, even though there exists no corresponding row in the LOCATION table. A corresponding query using the new join syntax is:

SELECT d.dept_id, d.name, l.regional_group

FROM department d LEFT OUTER JOIN location l

ON d.location_id = l.location_id;

In the new outer join syntax, the LEFT (or RIGHT) keyword corresponds to the table from which you want all the rows. This example uses LEFT to point to department, because we want all the rows from the department table irrespective of whether there are coresponding rows in the location table.

A.2.1 Restrictions on Old Outer Join Syntax

There are some rules and restrictions on how you can use the outer join operator in a query. When you use the (+) operator in a query, Oracle doesn't allow you to perform certain other operations in the same query. We discuss these restrictions and some of the workarounds in the following list:

  • 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 you are attempting a full outer join by placing the (+) operator on both sides in the join condition, please refer to Section A.2.2, which follows this section.


  • 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;

A.2.2 Full Outer Join Using the Old Syntax

In the previous section, you saw that a full outer join using the (+) operator is not allowed. A UNION of two SELECT statements is a workaround for this problem. In the following example, the first SELECT represents an outer join in which department is the optional table. The second SELECT has the location table as the optional table. Between the two SELECTS, you get all locations and all departments. The UNION operation eliminates duplicate rows, and the result is a full outer join:

SELECT d.dept_id, d.name, l.regional_group

FROM department d, location l

WHERE d.location_id (+) = l.location_id

UNION

SELECT d.dept_id, d.name, l.regional_group

FROM department d, location l

WHERE d.location_id = l.location_id (+) ;



   DEPT_ID NAME                 REGIONAL_GROUP

---------- -------------------- ----------------

        10 ACCOUNTING           NEW YORK

        20 RESEARCH             DALLAS

        30 SALES

        40 OPERATIONS           BOSTON

                                CHICAGO

                                SAN FRANCISCO



6 rows selected.

As you can see, this UNION query includes all the rows you would expect to see in a full outer join. UNION queries are discussed in Chapter 7.

Using the ANSI/ISO-compatible join syntax introduced in Oracle9i Database you can perform a full outer join in a much more straightforward way than shown in the previous example. See Section 3.3.3 in Chapter 3.