Outer join operator
Queries can use the outer join operator (+) as alternative syntax within predicates of the WHERE clause. This capability does not apply to the Entry plan of the Db2® Warehouse on Cloud managed service.
A join is the process of combining data from two or more tables based on some common domain of information. Rows from one table are paired with rows from another table when information in the corresponding rows match on the basis of the joining criterion. An outer join returns all rows that satisfy the join condition and also returns some or all of the rows from one or both tables for which no rows satisfy the join condition. You should use the outer join syntax of RIGHT OUTER JOIN, LEFT OUTER JOIN, or FULL OUTER JOIN wherever possible.
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.
The previous query is equivalent to the following one, which uses the outer join operator:SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.PK1 = T2.FK1 AND T1.PK2 = T2.FK2
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.
The previous query is equivalent to the following one, which uses the outer join operator:SELECT * FROM T1 RIGHT OUTER JOIN T2 ON T1.FK1 = T2.PK1 AND T1.FK2 = T2.PK2
SELECT * FROM T1, T2 WHERE T1.FK1(+) = T2.PK1 AND T1.FK2(+) = T2.PK2
A set of predicates that are 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.
Rules
- 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.
- Each Boolean term can refer to at most two tables, for example,
- 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.