joined-table
A joined-table specifies an intermediate result table that is the result of either an inner join, an outer join, or a cross join. The table is derived by applying one of the join operators: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, or CROSS to its operands.
Cross joins represent the cross product of the tables, where each row of the left table is combined with every row of the right table. Inner joins can be thought of as the cross product of the tables, keeping only the rows where the join condition is true. The result table might be missing rows 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 as follows:
- Left outer join
- A left outer join result includes the rows from the left table that were missing from the inner join. The result of
T1 LEFT OUTER JOIN T2consists of their paired rows and, for each unpaired row of T1, the concatenation of that row with the null row of T2. All columns derived from T2 allow null values. For more information, see Left outer join. - Right outer join
- A right outer join result includes the rows from the right table that were missing from the inner join. The result of
T1 RIGHT OUTER JOIN T2consists of their paired rows and, for each unpaired row of T2, the concatenation of that row with the null row of T1. All columns derived from T1 allow null values. For more information, see Right outer join. - Full outer join
- A full outer join result includes the rows from both tables that were missing from the inner join. The result of
T1 FULL OUTER JOIN T2consists of their paired rows and, for each unpaired row of T1, the concatenation of that row with the null row of T2, and for each unpaired row of T2, the concatenation of that row with the null row in T1. All columns of the result table allow null values. For more information, see Full outer join.
A joined-table can be used in any context in which any form of the SELECT statement is used. Both a view and a cursor is read-only if its SELECT statement includes a joined-table.
If LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN is specified:
- A ROW CHANGE TIMESTAMP expression can only be referenced in a subselect of the outer join if the table designator identifies a base table that includes a row change timestamp column.
- The RID built-in function and the ROW CHANGE TOKEN expression must not be specified in the subselect that contains the FROM clause.
join-condition syntax
For INNER, LEFT OUTER, and RIGHT OUTER
For FULL OUTER
For full-join-expression
join-condition description
For INNER, LEFT OUTER, and RIGHT OUTER joins, the join-condition is a search-condition that must conform to these rules:
- With one exception, It cannot contain any subqueries. If the join-table that contains the join-condition in the associated FROM clause is composed of only INNER joins, the join-condition can contain subqueries.
- Any column that is referenced in an expression of the join-condition must be a column of one of the operand tables of the associated join operator (in the scope of the same joined-table clause).
For a FULL OUTER (or FULL) join, the join-condition is a search condition in which the predicates can only be combined with AND. In addition, each predicate must have the form 'expression = expression', where one expression references only columns of one of the operand tables of the associated join operator, and the other expression references only columns of the other operand table. The values of the expressions must be comparable. Implicit cast between numeric and string data types is not supported for FULL OUTER join.
Each full-join-expression in a FULL OUTER join must include a column name or a cast function that references a column. The COALESCE function is allowed.
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 Resolution of column name qualifiers and column names before any rules about which tables the columns must belong to are applied.
Join operations
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. 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.
The following list summarizes the results of the various inner and outer join operations:
- The result of
T1 INNER JOIN T2consists of their paired rows. If a join operator is not specified, INNER is the default. The order in which a LEFT OUTER JOIN or RIGHT OUTER JOIN is performed can affect the result. - The result of
T1 LEFT OUTER JOIN T2consists of their paired rows and, for each unpaired row of T1, the concatenation of that row with the null row of T2. All columns derived from T2 allow null values. - The result of
T1 RIGHT OUTER JOIN T2consists of their paired rows and, for each unpaired row of T2, the concatenation of that row with the null row of T1. All columns derived from T1 allow null values. - The result of
T1 FULL OUTER JOIN T2consists of their paired rows and, for each unpaired row of T1, the concatenation of that row with the null row of T2, and for each unpaired row of T2, the concatenation of that row with the null row in T1. All columns of the result table allow null values. - The result of
T1 CROSS JOIN T2consists of each row of T1 paired with each row of T2. CROSS JOIN is also known as the Cartesian product. A cross join can also be specified without the CROSS JOIN syntax, by listing the two tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria.
A join operation is part of a FROM clause. For the purpose of predicting which rows will be returned from a SELECT statement containing a join operation, assume that the join operation is performed before the other clauses in the statement.
Outer join examples
The following examples use two tables: the parts table (PARTS) and the products table (PRODUCTS), which consist of hardware supplies.
The following figure shows that each row in the PARTS table contains data for a single part: the part name, the part number, and the supplier of the part.
The following figure shows that each row in the PRODUCTS table contains data for a single product: the product number, name, and price.
The following figure shows the ways to combine the PARTS and PRODUCTS tables by using outer join functions. The illustration is based on a subset of columns in each table.
An inner join consists of rows that are formed from the PARTS and PRODUCTS tables, based on matching the equality of column values between the PROD# column in the PARTS table and the PROD# column in the PRODUCTS table. The inner join does not contain any rows that are formed from unmatched columns when the PROD# columns are not equal.
You can specify joins in the FROM clause of a query. Data from the rows that satisfy the search conditions are joined from all the tables to form the result table.
The result columns of a join have names if the outermost SELECT list refers to base columns. However, if you use a function (such as COALESCE) to build a column of the result, that column does not have a name unless you use the AS clause in the SELECT list.