Joined Table (joined_table) 

A joined table (joined_table) can be specified as part of a FROM TABLE specification (from_table_spec).

Syntax

<joined_table> ::=
  <from_table_spec> CROSS JOIN <from_table_spec>
| <from_table_spec> [INNER] JOIN <from_table_spec> <join_spec>
| <from_table_spec> [LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER]] JOIN <from_table_spec> <join_spec>

<join_spec> ::= ON <search_condition>

Explanation

If a FROM TABLE specification comprises a joined table, the result is generated as follows:

Let FT1 be the set of all rows in the table specified by the first from table specification. Let FT2 be the set of all rows in the table specified by the second from table specification.

●     If the joined table is specified as CROSS JOIN, a table is created that comprises all possible combinations of FT1 and FT2. From a mathematical perspective, the Cartesian product of the two tables is calculated.

●     If the joined table is specified with the keyword JOIN without the optional keywords INNER, LEFT, RIGHT, FULL, or OUTER, the join type is assumed to be INNER.

Let T be the set of result rows consisting of all possible combinations of FT1 and FT2. Each result row satisfies the join specification (join_spec) for this set.

●     If the joined table is specified with the join type INNER, the result is the set T.

●     If the joined table is specified with the join type LEFT, the result is the set T plus the rows from FT1 that are not in T. The result columns that are not formed from FT1 are assigned the NULL value.

●     If the joined table is specified with the join type RIGHT, the result is the set T plus the rows from FT2 that are not in T. The result columns that are not formed from FT2 are assigned the NULL value.

●     If the joined table is specified with the join type FULL, the result is the set T plus the rows from FT1 that are added by the join types LEFT and RIGHT.

The rules specified for the WHERE condition apply to the JOIN specification ON <search_condition>, with the restriction that no links using the Boolean operator OR are permitted.

See also:

JOIN Predicate (join_predicate)

Updateable Join View Table (CREATE VIEW Statement)

Join View Table in INSERT statements