Outer join operator

You enable outer join operator support by setting the DB2_COMPATIBILITY_VECTOR registry variable to hexadecimal value 0x04 (bit position 3), and then stop and restart the instance to have the new setting take effect.

db2set DB2_COMPATIBILITY_VECTOR=04
db2stop
db2start

To take full advantage of the DB2 compatibility features for Oracle applications, the recommended setting for the DB2_COMPATIBILITY_VECTOR is ORA, which sets all of the compatibility bits.

You apply the outer join operator (+) in parentheses following a column name within predicates that refer to columns from two tables, as shown in the following examples:

• The following query performs a left outer join of tables T1 and T2. Include both tables in the FROM clause, separated by a comma. Apply the outer join operator to all columns of T2 in predicates that also reference T1.

     SELECT * FROM T1
       LEFT OUTER JOIN T2 ON T1.PK1 = T2.FK1
         AND T1.PK2 = T2.FK2

  The previous query is equivalent to the following one, which uses the outer join operator:

     SELECT * FROM T1, T2
       WHERE T1.PK1 = T2.FK1(+)
         AND T1.PK2 = T2.FK2(+)

• The following query 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.

     SELECT * FROM T1
       RIGHT OUTER JOIN T2 ON T1.FK1 = T2.PK1
         AND T1.FK2 = T2.PK2

  The previous query is equivalent to the following one, which uses the outer join operator:

     SELECT * FROM T1, T2
       WHERE T1.FK1(+) = T2.PK1
         AND T1.FK2(+) = T2.PK2

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

The following rules apply to the outer join operator:

• Predicates
  • The WHERE predicate is considered on a granularity of ANDed Boolean factors.
  • 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.
• Boolean
  • Each Boolean term can refer to at most two tables, for example, T1.C11 + T2.C21 = T3.C3(+) is not allowed.
  • Correlation for outer join Boolean terms is not allowed.
• Outer join operator
  • You cannot specify the outer join operator in the same subselect as the explicit JOIN syntax
  • You can specify the outer join operator only in the WHERE clause on columns that are associated with tables that you specify in the FROM clause of the same subselect.
  • You cannot apply the outer join operator 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.
  • You can specify the outer join operator only in the WHERE clause on columns that are associated with tables that you specify in the FROM clause of the same subselect.
• NULL-producer
  • 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 table.
  • You can use a table only once as the NULL-producer for one other table within a query.
  • You cannot use the same table 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.
    For example, the following query starts with T1 as the outer table in the first predicate and then cycles back to T1 in the third predicate. 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.

    SELECT ... FROM T1,T2,T3
      WHERE T1.a1 = T2.b2(+)
        AND T2.b2 = T3.c3(+)
        AND T3.c3 = T1.a1(+)   -- invalid cycle

• AND-factor
  • An AND-factor can have only one table as a NULL-producer. Each column reference that is followed by the outer join operator must be from the same table.
  • An AND-factor that includes an outer join operator can reference at most two tables.
  • If you require multiple AND-factors for the outer join between two tables, you must specify the outer join operator 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.
  • An AND-factor with predicates that involve only one table can specify the outer join operator if there is at least one other AND-factor that meets the following criteria:
    • The AND-factor must involve the same table as the NULL-producer.
    • The AND-factor must involve another table as the outer table.
  • An AND-factor with predicates involving only one table and without an outer join operator is processed on the result of the join.
  • An AND-factor that includes an outer join operator must follow the rules for a join-condition of an ON clause that is defined under a joined-table.