joined-table

Start of changeA joined-table specifies an intermediate result table that is the result of either an inner join, an outer join, or a cross join. The table is derived by applying one of the join operators: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, or CROSS to its operands.End of change

joined-table

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

Start of changeCross 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 rows produced by the inner join as well as the missing rows, depending on the type of outer join as follows:End of change

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 the default. The order in which a LEFT OUTER JOIN or RIGHT OUTER JOIN is performed can affect the result.

A joined-table can be used in any context in which any form of the SELECT statement is used. Both a view and a cursor is read-only if its SELECT statement includes a joined-table.

If LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN is specified:

  • A ROW CHANGE TIMESTAMP expression can only be referenced in a subselect of the outer join if the table designator identifies a base table that includes a row change timestamp column.
  • The RID built-in function and the ROW CHANGE TOKEN expression must not be specified in the subselect that contains the FROM clause.