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.
join_expression ::={ table_specification CROSS JOIN table_specification |
table_specification [ INNER | { LEFT | RIGHT | FULL } [OUTER] ]JOIN
table_specification ON search_condition |
table_specification UNION JOIN table_specification |
( join_expression ) }
table_specification
Specification of a table from which data is to be read (see section "Table specifications" ).
CROSS
CROSS operator for forming a cross join. A cross join corresponds to the Cartesian product of the tables involved (see section "Cross joins").
INNER
INNER operator for creating an inner join. In an inner join, the derived table only contains the rows that satisfy the join condition (see section "Inner joins").
LEFT, RIGHT, FULL
Operators for creating an outer join. A table that is part of an outer join cannot include multiple columns.
In an outer join, the type of outer join defines the dominant table(s) (see section "Outer joins").
If a row in the dominant table does not satisfy the join condition, the row is nevertheless included in the derived table. The derived column that references the other table is set to NULL values.
LEFT RIGHT FULL | The table to the left of the LEFT operator is the dominant table. The table to the right of the RIGHT operator is the dominant table. The table to the left and the right of the FULL operator are both dominant |
search_condition
Search condition to be used as the join condition for joining the specified tables.
The following applies to any column specified in search_condition:
The column must either be part of one of the tables to be joined or, in the case of subqueries, part of one of the tables from a higher-level SELECT expression.
If an aggregate function occurs in search_condition, one of the following conditions must be satisfied:
The aggregate function is part of a subquery.
The join expression is in a SELECT list or HAVING clause, and the column specified in the argument of the aggregate function is an external reference.
UNION
UNION operator for forming a union join. A table that is part of a union join cannot contain any multiple columns.
The derived table of a union join contains both the records of the table to the left of the UNION operator and the records of the table to the right of the UNION operator, including in each case the columns of the other table set to NULL values (see section "Union joins").
join_expression
Nested join expression for creating a join from more than two tables.