a predicate may reference only one outer-joined table

http://www.oreillynet.com/network/2002/04/23/fulljoin.html

Among several new features, Oracle9i has introduced the ANSI standard join syntax. The new join syntax is not only SQL92 compliant, but it is elegant and makes the outer join syntax more intuitive. The biggest advantage of the new join syntax is its support for full outer joins, which is not directly feasible in Oracle8i. In this article I discuss the full outer join feature of Oracle9i, and provide some examples.

An outer join extends the result of an inner join by including rows from one table (say Table A) that don't have corresponding rows in another table (say Table B). 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. But there are situations when you may want a bidirectional outer join, i.e., you want to include all the rows from A and B:

• rows from the result of the inner join
• rows from A that don't have corresponding rows in B
• rows from B that don't have corresponding rows in A

Let's look at an example to understand this further. Consider the following two tables:

SQL> desc part
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 PART_ID                                   NOT NULL VARCHAR2(4)
 SUPPLIER_ID                                        VARCHAR2(4)

SQL> select * from part;

PART SUPP
---- ----
P1   S1
P2   S2
P3
P4

SQL> desc supplier
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 SUPPLIER_ID                               NOT NULL VARCHAR2(4)
 SUPPLIER_NAME                             NOT NULL VARCHAR2(20)

SQL> select * from supplier;

SUPP SUPPLIER_NAME
---- --------------------
S1   Supplier#1
S2   Supplier#2
S3   Supplier#3

Notice above that there are two parts (P3 and P4) that don't have a supplier yet. Also, there is a supplier (S3) who doesn't yet supply any part.

Result of Inner Join

Let's say our company is consolidating all of the parts and the suppliers for managing the inventory of its parts properly, and I was asked to generate a report of all the parts and their corresponding suppliers. So I performed a join of these two tables, which gave the following result:

SQL> select p.part_id, s.supplier_name
  2  from part p, supplier s
  3  where p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1   Supplier#1
P2   Supplier#2

The join shown above is an inner join, which results in just the rows that have corresponding rows in both tables. Therefore, the parts that don't have a supplier, or the suppliers that don't supply any part are excluded from the result set.

Result of Outer Join

If we want all parts to be listed in the result set, irrespective of whether they are supplied by any supplier or not, then we need to perform an outer join.

SQL> select p.part_id, s.supplier_name
  2  from part p, supplier s
  3  where p.supplier_id = s.supplier_id (+);

PART SUPPLIER_NAME
---- --------------------
P1   Supplier#1
P2   Supplier#2
P3
P4

The outer join above lists all of the parts. For the parts that don't have a corresponding supplier, null values are displayed for the SUPPLIER_NAME column. However, not all the suppliers are displayed. Since supplier S3 doesn't supply any parts, it gets excluded from the result set of the above outer join. If we want all the suppliers listed in the result set, irrespective of whether they supply any part or not, we need to perform an outer join like the following:

SQL> select p.part_id, s.supplier_name
  2  from part p, supplier s
  3  where p.supplier_id (+) = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1   Supplier#1
P2   Supplier#2
     Supplier#3

The outer join above lists all the suppliers. For the suppliers that don't supply any part, null values are displayed for the PART_ID column. However, not all the parts are displayed. Since parts P3 and P4 are not supplied by any suppliers, they get excluded from the result set of the above outer join.

Full Outer Join

If we want all the parts (irrespective of whether they are supplied by any supplier or not), and all the suppliers (irrespective of whether they supply any part or not) listed in the same result set, we have a problem. That's because the traditional outer join (using the '+' operator) is unidirectional, and you can't put (+) on both sides in the join condition. The following will result in an error:

SQL> select p.part_id, s.supplier_name
  2  from part p, supplier s
  3  where p.supplier_id (+) = s.supplier_id (+);
where p.supplier_id (+) = s.supplier_id (+)
                        *
ERROR at line 3:
ORA-01468: a predicate may reference only one outer-joined table

Up through Oracle8i, Oracle programmers have used a workaround to circumvent this limitation. The workaround involves two outer join queries combined by a UNION operator, as in the following example:

SQL> select p.part_id, s.supplier_name
  2  from part p, supplier s
  3  where p.supplier_id = s.supplier_id (+)
  4  union
  5  select p.part_id, s.supplier_name
  6  from part p, supplier s
  7  where p.supplier_id (+) = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1   Supplier#1
P2   Supplier#2
P3
P4
     Supplier#3

Notice the use of the UNION set operator to combine the results of two separate outer join queries, to list all the parts and all the suppliers in the same result set.

New Join Syntax

Oracle9i introduced the ANSI compliant join syntax. This new join syntax uses the new keywords inner join, left outer join, right outer join, and full outer join, instead of the (+) operator.

The inner join using this new join syntax will look like:

SQL> select p.part_id, s.supplier_name
  2  from part p inner join supplier s
  3  on p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1   Supplier#1
P2   Supplier#2

Remember, if we want to retain all the parts in the result set, irrespective of whether any supplier supplies them or not, then we need to perform an outer join. The corresponding outer join query using the new syntax will be:

SQL> select p.part_id, s.supplier_name
  2  from part p left outer join supplier s
  3  on p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1   Supplier#1
P2   Supplier#2
P4
P3

This is called a "left outer join" because all the rows from the table on the left (PART) are retained in the result set. If we want to retain all the suppliers in the result set, irrespective of whether they supply any part or not, then we need to perform a "right outer join". That would look like:

SQL> select p.part_id, s.supplier_name
  2  from part p right outer join supplier s
  3  on p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1   Supplier#1
P2   Supplier#2
     Supplier#3

However, the biggest advantage of the new join syntax is its support for full outer joins. Introduction of the ANSI standard join syntax in Oracle9i greatly simplifies the full outer join query. We are no longer limited by unidirectional outer join, and no longer need to use the UNION operation to perform the full outer join. Oracle9i introduced the full outer join operation to carry out such operations, as in the following example:

SQL> select p.part_id, s.supplier_name
  2  from part p full outer join supplier s
  3  on p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1   Supplier#1
P2   Supplier#2
P4
P3
     Supplier#3

The above SQL statement is not only smaller in size, it is much more elegant and intuitive as well. This ANSI join syntax is also more efficient than the UNION method of achieving a full outer join.