SQL Reference

joined-table

                      .-INNER-----.
>>-+-table-reference--+-----------+---JOIN--table-reference--ON--join-condition--+->
   |                  '-| outer |-'                                              |
   '-(--joined-table--)----------------------------------------------------------'
 
>--------------------------------------------------------------><
 
outer
 
                 .-OUTER-.
|----+-LEFT--+---+-------+--------------------------------------|
     +-RIGHT-+
     '-FULL--'
 

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: INNER, LEFT OUTER, RIGHT OUTER, or FULL OUTER to its operands.

Inner joins can be thought of as the cross product of the tables (combine each row of the left table with every row of the right table), keeping only the rows where the join-condition is true. The result table may 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:

1. left outer join includes rows from the left table that were missing from the inner join.
2. right outer join includes rows from the right table that were missing from the inner join.
3. 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 contain any subqueries, scalar or otherwise
• it cannot include any dereference operations or the DEREF function where the reference value is other than the object identifier column.
• it cannot include an SQL function
• 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.

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 (see Predicates).

Join Operations

A join-condition specifies pairings of T1 and T2, where T1 and T2 are the left and right operand tables of the JOIN operator of the join-condition. 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. 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 columns allow null values.

The following summarizes the result of the join operations:

• 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. All columns derived from T2 allow null values.
• 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. All columns derived from T1 allow null values.
• 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. All columns derived from T1 and T2 allow null values.

where-clause

>>-WHERE--search-condition-------------------------------------><
 

The WHERE clause specifies an intermediate result table that consists of those rows of R for which the search-condition is true. R is the result of the FROM clause of the subselect.

The search-condition must conform to the following rules:

• Each column-name must unambiguously identify a column of R or be a correlated reference. A column-name is a correlated reference if it identifies a column of a table-reference in an outer subselect.
• A column function must not be specified unless the WHERE clause is specified in a subquery of a HAVING clause and the argument of the function is a correlated reference to a group.

Any subquery in the search-condition is effectively executed for each row of R, and the results are used in the application of the search-condition to the given row of R. A subquery is actually executed for each row of R only if it includes a correlated reference. In fact, a subquery with no correlated references is executed just once, whereas a subquery with a correlated reference may have to be executed once for each row.