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:
idmscu19
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
This article describes the following information:
2
2
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;
For more information about expansion of table-reference, see Expansion of Table-reference.