DB2 Version 9.7 for Linux, UNIX, and Windows

Outer join operator

When the DB2_COMPATIBILITY_VECTOR registry variable is set to support the outer join operator, (+), queries can use this alternative syntax within predicates of the WHERE clause.

The outer join syntax should be used wherever possible, and the outer join operator should be used only when enabling applications from other relational database vendors on DB2®.

The outer join operator, (+), is applied following a column name within predicates that generally refer to columns from two table-references.

To write a query that performs a left outer join of tables T1 and T2, include both tables in the FROM clause separated by a comma, and apply the outer join operator to all columns of T2 in predicates that also reference T1. For example:
```
   SELECT * FROM T1
     LEFT OUTER JOIN T2 ON T1.PK1 = T2.FK1
       AND T1.PK2 = T2.FK2
```
is equivalent to:
```
   SELECT * FROM T1, T2
     WHERE T1.PK1 = T2.FK1(+)
       AND T1.PK2 = T2.FK2(+)
```
To write a query that performs a right outer join of tables T1 and T2, include both tables in the FROM clause separated by a comma, and apply the outer join operator to all columns of T1 in predicates that also reference T2. For example:
```
   SELECT * FROM T1
     RIGHT OUTER JOIN T2 ON T1.FK1 = T2.PK1
       AND T1.FK2 = T2.PK2
```
is equivalent to:
```
   SELECT * FROM T1, T2
     WHERE T1.FK1(+) = T2.PK1
       AND T1.FK2(+) = T2.PK2
```

A table-reference that has columns marked with the outer join operator is sometimes referred to as a NULL-producer.

A set of predicates separated by AND operators is known as an AND-factor. If there are no AND operators in a WHERE clause, the set of predicates in the WHERE clause is considered to be the only AND-factor.

The following rules apply to the use of the outer join operator.

The WHERE predicate is considered on a granularity of ANDed Boolean factors.
Each Boolean term can refer to at most two table-references; that is, T1.C11 + T2.C21 = T3.C3(+) is not allowed.
Each table can be the null producer with respect to at most one other table. If a table is joined to a third table, it must be the outer.
Local predicates, such as T1.A(+) = 5, can exist, but they are executed with the join. A local predicate without (+) is executed after the join.
Correlation for outer join Boolean terms is not allowed.
The outer join operator cannot be specified in the same subselect as the explicit JOIN syntax.
The outer join operator can be specified only in the WHERE clause on columns associated with table-references that are specified in the FROM clause of the same subselect.
An AND-factor can have only one table-reference as a NULL-producer. Each column reference followed by the outer join operator must be from the same table-reference.
An AND-factor that includes an outer join operator can reference at most two table-references.
If multiple AND-factors are required for the outer join between two tables, the outer join operator must be specified in all of these AND-factors. If an AND-factor does not specify the outer join operator, it is processed on the result of the outer join.
The outer join operator cannot be applied to an entire expression. Within an AND-factor, each column reference from the same table must be followed by the outer join operator (for example, T1.COL1 (+) - T1.COL2 (+) = T2.COL1).
An AND-factor with predicates that involve only one table-reference can specify the outer join operator if there is at least one other AND-factor that involves the same table-reference as the NULL-producer and involves another table-reference as the outer table.
An AND-factor with predicates involving only one table-reference and without an outer join operator is processed on the result of the join.
A table-reference can be used only once as the NULL-producer for one other table-reference within a query.
The same table-reference cannot be used as both the outer table and the NULL-producer in separate outer joins that form a cycle. A cycle can be formed across multiple joins when the chain of predicates comes back to an earlier table-reference. For example:
```
SELECT ... FROM T1,T2,T3
  WHERE T1.a1 = T2.b2(+)
    AND T2.b2 = T3.c3(+)
    AND T3.c3 = T1.a1(+)   -- invalid cycle
```
This example starts with T1 as the outer table in the first predicate and then cycles back to T1 in the third predicate. Note that T2 is used as both the NULL-producer in the first predicate and the outer table in the second predicate, but this usage is not itself a cycle.
An AND-factor that includes an outer join operator must follow the rules for a join-condition of an ON clause defined under joined-table.
The outer join operator can only be specified in the WHERE clause on columns that are associated with table-references specified in the FROM clause of the same subselect.