3.2 Outer Joins

3.2 Outer Joins

Sometimes while performing a join between two tables, you need to return all the rows from one table even when there are no corresponding rows in the other table. Consider the following two tables, SUPPLIER and PART:

SELECT * FROM SUPPLIER;

SUPPLIER_ID NAME
----------- ------------------------------
        101 Pacific Disks, Inc.
        102 Silicon Valley MicroChips
        103 Blue River Electronics

SELECT * FROM PART;

PART_NBR NAME               SUPPLIER_ID STATUS INVENTORY_QTY UNIT_COST RESUPPLY_DATE
-------- ------------------ ----------- ------ ------------- --------- -------------
HD211    20 GB Hard Disk            101 ACTIVE             5      2000 12-DEC-00
P3000    3000 MHz Processor         102 ACTIVE            12       600 03-NOV-00

If you want to list all the suppliers and all the parts supplied by them, it is natural to use the following query:

SELECT S.SUPPLIER_ID, S.NAME SUPPLIER_NAME, P.PART_NBR, P.NAME PART_NAME
FROM SUPPLIER S, PART P
WHERE S.SUPPLIER_ID = P.SUPPLIER_ID;

SUPPLIER_ID SUPPLIER_NAME                  PART_NBR   PART_NAME
----------- ------------------------------ ---------- -------------------
        101 Pacific Disks, Inc.            HD211      20 GB Hard Disk
        102 Silicon Valley MicroChips      P3000      3000 MHz Processor

Note that even though we have three suppliers, this query lists only two of them, because the third supplier (Blue River Electronics) doesn't currently supply any part. When Oracle performs the join between SUPPLIER table and PART table, it matches the SUPPLIER_ID from these two tables (as specified by the join condition). Since SUPPLIER_ID 103 doesn't have any corresponding record in the PART table, that supplier is not included in the result set. This type of join is the most natural, and is known as an inner join.

The concept of the inner join is easier to understand in terms of the Cartesian product. While performing a join of SUPPLIER and PART tables, a Cartesian product is first formed (conceptually, Oracle doesn't physically materialize this Cartesian product), and then the conditions in the WHERE clause restrict the results to only those rows where the SUPPLIER_ID values match.

However, we want to see all the suppliers even if they don't supply any parts. Oracle provides a special type of join to include rows from one table that don't have matching rows from the other table. This type of join is known as an outer join. An outer join allows us to return rows for all suppliers, and also for parts in cases where a supplier currently supplies parts. In cases where a supplier doesn't supply parts, NULLs are returned for the PART table columns in the result set.

The 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, i.e., (+). This operator is used in the join condition in the WHERE clause following a field name from the table that you wish to be considered the optional table. In our suppliers and parts example, the PART table doesn't have information for one supplier. Therefore, we will simply add a (+) operator to the join condition on the side of the PART table. The query and the result set look as follows:

SELECT S.SUPPLIER_ID, S.NAME SUPPLIER_NAME, P.PART_NBR, P.NAME PART_NAME
FROM SUPPLIER S, PART P
WHERE S.SUPPLIER_ID = P.SUPPLIER_ID (+);

SUPPLIER_ID SUPPLIER_NAME                  PART_NBR   PART_NAME
----------- ------------------------------ ---------- -------------------
        101 Pacific Disks, Inc.            HD211      20 GB Hard Disk
        102 Silicon Valley MicroChips      P3000      3000 MHz Processor
        103 Blue River Electronics

Note the (+) operator following P.SUPPLIER_ID. That makes PART the optional table in this join. If a supplier does not currently supply any parts, Oracle will fabricate a PART record with all NULLs for that supplier. Thus, the query results can include all suppliers, regardless of whether they currently supply parts. You can see that the PART columns for supplier 103 in this example all have NULL values.

The outer join operator (+) can appear on either the left or the right side of the join condition. However, make sure you apply this operator to the appropriate table in the context of your query. For example, it makes no difference to the result if you switch the two sides of the equality operator in the previous example:

SELECT S.SUPPLIER_ID, S.NAME SUPPLIER_NAME, P.PART_NBR, P.NAME PART_NAME
FROM SUPPLIER S, PART P
WHERE P.SUPPLIER_ID (+) = S.SUPPLIER_ID;

SUPPLIER_ID SUPPLIER_NAME                  PART_NBR   PART_NAME
----------- ------------------------------ ---------- --------------------
        101 Pacific Disks, Inc.            HD211      20 GB Hard Disk
        102 Silicon Valley MicroChips      P3000      3000 MHz Processor
        103 Blue River Electronics

However, if you associate the (+) operator with the wrong table, you may get unexpected results. For example:

SELECT S.SUPPLIER_ID, S.NAME SUPPLIER_NAME, P.PART_NBR, P.NAME PART_NAME
FROM SUPPLIER S, PART P
WHERE P.SUPPLIER_ID = S.SUPPLIER_ID (+);

SUPPLIER_ID SUPPLIER_NAME                  PART_NBR   PART_NAME
----------- ------------------------------ ---------- --------------------
        101 Pacific Disks, Inc.            HD211      20 GB Hard Disk
        102 Silicon Valley MicroChips      P3000      3000 MHz Processor

Here, the outer join operator is placed on the side of the SUPPLIER table in the join condition. By doing this, you are asking Oracle to print the parts and their corresponding suppliers, as well as the parts without a supplier. However, in our example data, all the parts have a corresponding supplier. Therefore, the results are the same as if we had done an inner join.

3.2.1 Restrictions on Outer Joins

There are some rules and restrictions on how you can use an outer join query. When you perform an outer join 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 work-arounds in this list.

The outer join operator can appear on only one side of an expression in the join condition. You get an ORA-1468 error if you attempt to use it on both sides. For example:
```
SELECT S.SUPPLIER_ID, S.NAME SUPPLIER_NAME, P.PART_NBR, P.NAME PART_NAME
FROM SUPPLIER S, PART P
WHERE S.SUPPLIER_ID (+) = P.SUPPLIER_ID (+);

WHERE S.SUPPLIER_ID (+) = P.SUPPLIER_ID (+)
                        *
ERROR at line 3:
ORA-01468: a predicate may reference only one outer-joined table
```
If you are attempting a two-sided outer join by placing the (+) operator on both sides in the join condition, please refer to Section 3.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. Let's look at the following example:

DESC EMPLOYEE
 Name                            Null?    Type
 ------------------------------- -------- ----
 EMP_ID                          NOT NULL NUMBER(4)
 LNAME                                    VARCHAR2(15)
 FNAME                                    VARCHAR2(15)
 DEPT_ID                                  NUMBER(2)
 MANAGER_EMP_ID                           NUMBER(4)
 SALARY                                   NUMBER(7,2)
 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(2)
 NAME                                     VARCHAR2(14)
 LOCATION_ID                              NUMBER(3)

If you want to list the job function and department name of all the employees, and 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 table and the DEPARTMENT table, 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 work around, you can create a view with an outer join between two tables, and then outer join the view with 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.

An outer join 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 OR 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 work around, 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;

Inline views are discussed in Chapter 5.

3.2.2 Full Outer Joins

An outer join extends the result of an inner join by including rows from one table (table A, for example) that don't have corresponding rows in another table (table B, for example). An important thing to note here is that the outer join operation will not include the rows from table B that don't have corresponding rows in table A. In other words, an outer join is unidirectional. There are situations when you may want a bidirectional outer join, i.e., you want to include all the rows from A and B that are:

From the result of the inner join.
From A that don't have corresponding rows in B.
From B that don't have corresponding rows in A.

Let's take an example to understand this further. Consider the following two tables: LOCATION and DEPARTMENT:

DESC LOCATION
 Name                            Null?    Type
 ------------------------------- -------- ----
 LOCATION_ID                     NOT NULL NUMBER(3)
 REGIONAL_GROUP                           VARCHAR2(20)

DESC DEPARTMENT
 Name                            Null?    Type
 ------------------------------- -------- ----
 DEPT_ID                         NOT NULL NUMBER(2)
 NAME                                     VARCHAR2(14)
 LOCATION_ID                              NUMBER(3)

Assume there are locations in the LOCATION table that don't have corresponding departments in the DEPARTMENT table, and that at the same time there are departments in the DEPARTMENT table without a LOCATION_ID pointing to corresponding LOCATION rows. If you perform an inner join of these two tables, you will get only the departments and locations that have corresponding rows in both the tables.

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          CHICAGO
           40 OPERATIONS     BOSTON
           12 RESEARCH       NEW YORK
           13 SALES          NEW YORK
           14 OPERATIONS     NEW YORK
           23 SALES          DALLAS
           24 OPERATIONS     DALLAS
           34 OPERATIONS     CHICAGO
           43 SALES          BOSTON

11 rows selected.

There are locations that don't have any departments. To include those locations in this list, you have to perform an outer join with the (+) operator on the department side, making the DEPARTMENT table the optional table in the query. Notice that Oracle supplies NULLs for missing DEPARTMENT data.

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
           12 RESEARCH       NEW YORK
           14 OPERATIONS     NEW YORK
           13 SALES          NEW YORK
           30 SALES          CHICAGO
           34 OPERATIONS     CHICAGO
           20 RESEARCH       DALLAS
           23 SALES          DALLAS
           24 OPERATIONS     DALLAS
                             SAN FRANCISCO
           40 OPERATIONS     BOSTON
           43 SALES          BOSTON

12 rows selected.

There are departments that don't belong to any location. If you want to include those departments in the result set, perform an outer join with the (+) operator on the location side.

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          CHICAGO
           40 OPERATIONS     BOSTON
           12 RESEARCH       NEW YORK
           13 SALES          NEW YORK
           14 OPERATIONS     NEW YORK
           23 SALES          DALLAS
           24 OPERATIONS     DALLAS
           34 OPERATIONS     CHICAGO
           43 SALES          BOSTON
           50 MARKETING
           60 CONSULTING

13 rows selected.

However, the previous query excluded any location that doesn't have a department. If you want to include the departments without a location as well as the locations without a department, you will probably try to use a two-sided outer join, correctly termed a full outer join, like the following:

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

As you can see, a two-sided outer join is not allowed. A UNION of two SELECT statements is a work around 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
           12 RESEARCH       NEW YORK
           13 SALES          NEW YORK
           14 OPERATIONS     NEW YORK
           20 RESEARCH       DALLAS
           23 SALES          DALLAS
           24 OPERATIONS     DALLAS
           30 SALES          CHICAGO
           34 OPERATIONS     CHICAGO
           40 OPERATIONS     BOSTON
           43 SALES          BOSTON
           50 MARKETING
           60 CONSULTING
                             SAN FRANCISCO

14 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 more detail in Chapter 7.

Oracle9i introduces new ANSI-compatible join syntax that enables full outer joins in a much more straightforward way than the previous example. The new syntax is discussed at the end of this chapter.