A joined-table specifies an intermediate result table that is the result of either an inner, outer, cross, or exception join. The table is derived by applying one of the join operators: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, LEFT EXCEPTION, RIGHT EXCEPTION, or CROSS to its operands.
.-INNER----------------. >>-+-table-reference--+-+----------------------+--JOIN--table-reference--+-ON--join-condition-----------+-+-+->< | | | .-OUTER-. | | .-,-----------. | | | | | +-+-LEFT--+--+-------+-+ | V | | | | | | | +-RIGHT-+ | '-USING--(----column-name-+--)-' | | | | | '-FULL--' | | | | | | .-LEFT--. | | | | | '-+-------+--EXCEPTION-' | | | | '-RIGHT-' | | | '-CROSS JOIN--table-reference-------------------------------------------------------' | '-(--joined-table--)-------------------------------------------------------------------------------------'
If a join operator is not specified, INNER is implicit. The order in which multiple joins are performed can affect the result. Joins can be nested within other joins. The order of processing for joins is generally from left to right, but based on the position of the required join-condition or USING clause. Parentheses are recommended to make the order of nested joins more readable. For example:
TB1 LEFT JOIN TB2 ON TB1.C1=TB2.C1 LEFT JOIN TB3 LEFT JOIN TB4 ON TB3.C1=TB4.C1 ON TB1.C1=TB3.C1
is the same as
(TB1 LEFT JOIN TB2 ON TB1.C1=TB2.C1) LEFT JOIN (TB3 LEFT JOIN TB4 ON TB3.C1=TB4.C1) ON TB1.C1=TB3.C1
An inner join combines each row of the left table with every row of the right table keeping only the rows where the join-condition (or USING clause) is true. Thus, the result table may be missing rows of from either or both of the joined tables. Outer joins include the rows produced by the inner join as well as the missing rows, depending on the type of outer join. Exception joins include only the missing rows, depending on the type of exception join as follows:
A joined table can be used in any context in which any form of the SELECT statement is used. A view or a cursor is read-only if its SELECT statement includes a joined table.
For any type of join, column references in an expression of the join-condition are resolved using the rules for resolution of column name qualifiers specified in Column names before any rules about which tables the columns must belong to are applied.
The USING clause specifies a shorthand way of defining the join condition. This form is known as a named-columns-join.
The
result table of the join only contains the column from the first table
of the join. Any column specified in the USING clause cannot be qualified
in the select-list of the query.
The USING clause is equivalent to a join-condition in which each column from the left table-reference is compared equal to a column of the same name in the right table-reference. For example, assume that TB1 and TB2 have columns C1, C2, ... Cn, D1, D2 named-columns-join of the form:
TB1 INNER JOIN TB2 USING (C1, C2, ... Cn)
defines a result table that is equivalent to:
SELECT TB1.*, TB2.D1, TB2.D2 FROM TB1 INNER JOIN TB2 ON TB1.C1 = TB2.C1 AND TB1.C2 = TB2.C2 AND ... TB1.Cn = TB2.Cn
A join-condition (or USING clause) specifies pairings of T1 and T2, where T1 and T2 are the left and right operand tables of the JOIN operator of the join-condition (or USING clause). For all possible combinations of rows of T1 and T2, a row of T1 is paired with a row of T2 if the join-condition (or USING clause) is true. When a row of T1 is joined with a row of T2, a row in the result consists of the values of that row of T1 concatenated with the values of that row of T2. In the case of OUTER joins, the execution might involve the generation of a null row. The null row of a table consists of a null value for each column of the table, regardless of whether the columns allow null values.
Using the INNER JOIN syntax with a join-condition (or USING clause) will produce the same result as specifying the join by listing two tables in the FROM clause separated by commas and using the where-clause to provide the join condition.