The description of the WHERE clause on "Query expression" introduced the simplest form of the join, which allows tables to be linked using a join condition. The join expression provides greater flexibility in using joins.
A join expression consists of the tables to be joined, the desired join operation and possibly a join condition.
A join expression can be specified
as a query expression in an SQL statement
in the FROM clause of a SELECT expression or SELECT statement
in a subquery in the SELECT list and HAVING clause
The derived table of a join expression cannot be updated.
If two tables are linked with a join, this is referred to as simple join. If more than two tables are linked, it is referred to as a compound join. To form a compound join, join expressions are nested within other join expressions. The use of parentheses can influence the grouping of the operators in nested joins of this type.
The keywords CROSS, INNER, OUTER and UNION define the type of join.
CROSS JOIN
A CROSS JOIN between two tables results in the Cartesian product (cross product) of the tables. In the derived table of a CROSS JOIN, every row of the first table is concatenated with every row of the second table.
INNER JOIN
INNER defines a so-called inner join. In the case of an inner join, only those rows of the Cartesian product whose join columns fulfil the join condition are included in the derived table.
OUTER JOIN
OUTER defines a so-called outer join. The source table for an outer join is the same as for an inner join, namely the Cartesian product. In a one-way outer join, a distinction is made between a dominant table and a dependent table. Unlike the inner join, the outer join also contains a row if there is no row in the dependent table which fulfils the join condition for a given row in the dominant table. A row containing null values is used for the missing row in the dependent table.
The keyword LEFT OUTER defines the table to the left of the LEFT OUTER operator as the dominant table and the keyword RIGHT OUTER defines the table to the right of the RIGHT OUTER operator as the dominant table. In the case of a FULL OUTER join the tables to the right and left of the FULL operator are each defined once as the dominant and as the dependent table. The derived table of the FULL OUTER join contains all the rows of the LEFT OUTER and of the RIGHT OUTER join. Any row which occurs in the derived table of the LEFT OUTER and RIGHT OUTER joins is only included once in the derived table of the FULL OUTER. Duplicates in the LEFT OUTER table and in the RIGHT OUTER table are included as duplicates in the derived table of the FULL OUTER join.
Inner and outer joins can be combined in a join expression.
UNION JOIN
Unlike the inner and outer joins, the Cartesian product is not the source table for UNION JOIN. Instead, the tables to the left and right of the UNION operator are used as source tables. The UNION JOIN is formed as follows:
The first table of the UNION JOIN is expanded to the right to include the columns of the second table and NULL is entered for the column values of the second table.
The second table is expanded to the left to include the columns from the first table and NULL is entered for the column values of the first table.
UNION is employed to join the first table (expanded to include NULL values) to the second table (expanded to include NULL values).