Expansion of Joined-table

The expanded parameters of joined-table represent a table that is derived from joining two specified tables. A join operation on two tables is the result of the cross product of the two tables. A qualified join is followed by a filter operation. The cross or Cartesian product of two tables, left and right, is the result of extending each row of the left table with every row of the right table. The different types of join operations are specified through the following join types:

CROSS
UNION
INNER
LEFT OUTER
RIGHT OUTER
FULL OUTER

Syntax

►──────┬─ unqualified-joined-table ─┬─────────────────────────────────────────►◄
       └─ qualified-joined-table ───┘

Expansion of unqualified-joined-table

►─── table-reference ─┬────────────────────┬──┬─ CROSS ──┬───────────── JOIN ──►
                      └┬──────┬── alias-l ─┘  └─ UNION  ──┘
                       └─ AS ─┘
►─── table-reference ─┬────────────────────┬──────────────────────────────────►◄
                      └┬──────┬── alias-r ─┘
                       └─ AS ─┘

Expansion of qualified-joined-table

►─── table-reference ─┬────────────────────┬─┬──────────────────────┬─ JOIN ───►
                      └┬──────┬── alias-l ─┘ ├───── INNER ──────────┤
                       └─ AS ─┘              ├─ LEFT ───┬┬─────────┬┘
                                             ├─ RIGHT ──┤└─ OUTER ─┘
                                             └─ FULL ───┘

►─── table-reference ─┬────────────────────┬──── ON ─ join-condition ─────────►◄
                      └┬──────┬── alias-r ─┘
                       └─ AS ─┘

Expansion of join-condition

►──┬─ search-condition ──┬───┬──────────────────────────────────┬─────────────►◄
   └─ set-specification ─┘   │  ┌──────────────────────────────┐│
                             └──▼─ AND ─┬─ search-condition ──┬┴┘
                                        └─ set-specification ─┘

Parameters

unqualified-joined-table: Specifies a joined-table where the join operation is a cross or union.
qualified-joined-table: Specifies a joined-table where the join operation is an inner, left outer, right outer, or full outer.
table-reference: Represents a table-like object. In a joined-table specification, a left and a right table-reference are required to define the left and right components of the join operation.
AS alias-l: Defines a new name used to identify the left table-like object within the joined-table specification. Alias-l must be a 1-through 18-character name that follows the conventions for SQL identifiers.
AS alias-r: Defines a new name used to identify the right table-like object within the joined-table specification. Alias-r must be a 1-through 18-character name that follows the conventions for SQL identifiers.
CROSS: Specifies a cross join. A cross join is the cross product of the left and right table.
UNION: Specifies a union join. A union join is equivalent to a full outer join where the join-condition always evaluates to false.
INNER: Specifies an inner join. In an inner join, the cross product of the left and right table-like objects is made, and only the rows for which join-condition evaluates to true are kept in the result. This is the default.
LEFT/LEFT OUTER: Specifies a left outer join. In a left outer join, the cross product of the left and right table-like objects is made, and the rows for which join-condition evaluates to true are kept. The result is extended with all the missing rows from the left table, and the values of the columns in the result row, derived from the right table, are set to NULL.
RIGHT/RIGHT OUTER: Specifies a right outer join. In a right outer join, the cross product of the left and right table-like objects is made, and the rows for which join-condition evaluates to true are kept. The result is extended with all the missing rows from the right table, and the values of the columns in the result row, derived from the left table, are set to NULL.
FULL/FULL OUTER: Specifies a full outer join. In a full outer join, the cross product of the left and right table-like objects is made, and the rows for which join-condition evaluates to true are kept. The result is extended with all the missing rows from the left table, and the values of the columns in the result row, derived from the right table, are set to NULL. The result is further extended with all the missing rows from the right table, and the values of the columns in the result row, derived from the left table, are set to NULL.
join-condition: Represents the truth condition for joining two table-like objects. Expanded syntax for join-condition appears immediately after the joined-table syntax. If join-condition contains a set-specification both the left and the right table-reference must specify base tables of a non-SQL-defined database that identify the owner and member of the non-SQL set.

Usage

If a join type is not specified, INNER is assumed.
Joined-tables can be nested. Evaluation is from left to right.
It is advisable to use parenthesis when nesting joins.
In a nested joined-table, only the join-condition of the inner most join can contain a set-specification because a set-specification requires that the left and right table-reference are base tables of a non-SQL-defined database.
A query-expression that contains a joined-table is not updateable.

Examples

Selecting all Departments and Employees in Department

The following examples list all the departments and the employees of the department. The two statements give identical results.

select d.*, e.*
 from DEMOEMPL.DEPARTMENT d left join DEMOEMPL.EMPLOYEE e
   on  d.dept_id = e.dept_id
select d.*, e.*
 from DEMOEMPL.EMPLOYEE e  right join DEMOEMPL.DEPARTMENT d
   on  d.dept_id = e.dept_id

Selecting all Depts./Empls. in Dept. with or without Position

The following examples show nesting of joined tables. The two statements give identical results.

select d.*, e.*, p.*
    from DEMOEMPL.DEPARTMENT d left join
         (DEMOEMPL.EMPLOYEE  e left join DEMOEMPL.POSITION p
                                      on p.EMP_ID  = e.EMP_ID )
                                      on e.DEPT_ID = d.DEPT_ID;
select d.*, e.*, p.*
    from DEMOEMPL.DEPARTMENT d left join
         (DEMOEMPL.POSITION p right join DEMOEMPL.EMPLOYEE e
                                      on p.EMP_ID  = e.EMP_ID )
                                      on e.DEPT_ID = d.DEPT_ID;

Note: For more information about expansion of table-reference, see Expansion of Table-reference.

More information:

Expansion of Table-reference