A joined table specifies an intermediate result
table that is the result of either an inner join or an outer join.
The table is derived by applying one of the join operators: CROSS,
INNER, LEFT OUTER, RIGHT OUTER, or FULL OUTER to its operands.
.-INNER-----.
>>-+-table-reference--+-----------+--JOIN--table-reference--ONjoin-condition-+-><
| '-| outer |-' |
+-table-reference--CROSS JOIN--table-reference----------------------------+
'-(--joined-table--)------------------------------------------------------'
outer
.-OUTER-.
|--+-LEFT--+--+-------+-----------------------------------------|
+-RIGHT-+
'-FULL--'
Cross joins represent the cross product of
the tables, where each row of the left table is combined with every
row of the right table. Inner joins can be thought of as the
cross product of the tables, keeping only the rows where the join
condition is true. The result table might be missing rows from either
or both of the joined tables. Outer joins include the inner join and
preserve these missing rows. There are three types of outer joins:
- Left outer join includes rows from the left table
that were missing from the inner join.
- Right outer join includes rows from the right table
that were missing from the inner join.
- Full outer join includes rows from both the left
and right tables that were missing from the inner join.
If a join-operator is not specified, INNER is implicit.
The order in which multiple joins are performed can affect the result.
Joins can be nested within other joins. The order of processing for
joins is generally from left to right, but based on the position of
the required join-condition. Parentheses are recommended to make the
order of nested joins more readable. For example:
TB1 LEFT JOIN TB2 ON TB1.C1=TB2.C1
RIGHT JOIN TB3 LEFT JOIN TB4 ON TB3.C1=TB4.C1
ON TB1.C1=TB3.C1
is the same as:
(TB1 LEFT JOIN TB2 ON TB1.C1=TB2.C1)
RIGHT JOIN (TB3 LEFT JOIN TB4 ON TB3.C1=TB4.C1)
ON TB1.C1=TB3.C1
A joined table can
be used in any context in which any form of the SELECT statement is
used. A view or a cursor is read-only if its SELECT statement includes
a joined table.
A
join-condition is
a
search-condition, except that:
- It cannot include any dereference operations or the DEREF function,
where the reference value is other than the object identifier column
- Any column referenced in an expression of the join-condition must
be a column of one of the operand tables of the associated join (in
the scope of the same joined-table clause)
- Any function referenced in an expression of the join-condition of
a full outer join must be deterministic and have no external action
- It cannot include an XMLQUERY or XMLEXISTS expression
An error occurs if the join condition does not comply
with these rules (SQLSTATE 42972).
Column references are resolved
using the rules for resolution of column name qualifiers. The same
rules that apply to predicates apply to join conditions.
Join operations
When a row of T1 is joined
with a row of T2, a row in the result consists of the values of that
row of T1 concatenated with the values of that row of T2. The execution
might involve the generation of a null row. The null row of a table
consists of a null value for each column of the table, regardless
of whether the null values are allowed in the columns.
The following
list summarizes the result of the join operations:
- The result of T1 CROSS JOIN T2 consists of all possible pairings
of their rows.
- The result of T1 INNER JOIN T2 consists of their paired rows where
the join-condition is true.
- The result of T1 LEFT OUTER JOIN T2 consists of their paired rows
where the join-condition is true and, for each unpaired row of T1,
the concatenation of that row with the null row of T2. Null values
are allowed in all columns derived from T2.
- The result of T1 RIGHT OUTER JOIN T2 consists of their paired
rows where the join-condition is true and, for each unpaired row of
T2, the concatenation of that row with the null row of T1. Null values
are allowed in all columns derived from T1.
- The result of T1 FULL OUTER JOIN T2 consists of their paired rows
and, for each unpaired row of T2, the concatenation of that row with
the null row of T1 and, for each unpaired row of T1, the concatenation
of that row with the null row of T2. Null values are allowed in all
columns derived from T1 and T2.
Join conditions
A join-condition specifies
pairings of t1 and t2, where t1 and t2 represent the names of the
left (t1) and right (t2) operand tables of the JOIN operator. For
all possible combinations of rows of t1 and t2, a row of t1 is paired
with a row of t2 if the join-condition is true.
Use the ON clause to specify a join condition.
This clause any qualification expression that involves the two tables
to be joined. For example:
SELECT * FROM t1 JOIN t2 ON t1.c1 = t2.c1 AND t1.c2 = t2.c2;
The
join result contains all columns of t1 followed by all columns of
t2. Unless a projection column list is explicitly specified, the order
of the columns in the output is the same as in the join result (that
is, all columns of t1 followed by all columns of t2).
DB2 10.5 for Linux, UNIX, and Windows