ALLBASE/SQL Reference Manual (36216-90216)

Chapter 12 513
SQL Statements S - Z
SELECT
SearchCondition3 to limit the rows returned from the join, each column name
used must unambiguously reference a column in one of the tables being joined, or
must be an outer reference (as in the case of nested subqueries).
• For a SELECT *, the columns contained in the result of the join are the same as the
columns of the Cartesian product of the tables being joined.
• For any other SELECT, the columns displayed will be those specified in the select list,
in the order specified.
• The result of the INNER JOIN....ON
SearchCondition3 contains the multiset of
rows of the Cartesian Product of the tables being joined for which all predicates in
SearchCondition3 are true.
• When you specify JOIN....USING (
ColumnList ):
• You must not use the keyword NATURAL or the ON
SearchCondition3 clause.
• You place in the
ColumnList one unqualified column name for each pair of common
columns being used for the join.
• No column name may be used if it is not common to both tables being joined.
• For SELECT *, the result of the INNER JOIN....USING (
ColumnList ) contains the
multiset of rows of the Cartesian product of the tables being joined for which the
corresponding join columns have equal values. The coalesced common columns are
returned first. (No duplicate columns are displayed in the case of common columns).
The non-join columns from both tables appear next. If there is no common column,
the result contains the multiset of rows of the Cartesian product of the tables being
joined.
• The result of the [NATURAL] LEFT [OUTER] JOIN is the union of two components.
The first component is the result of the equivalent [NATURAL] INNER JOIN. The
second component contains those rows in the left hand table that are not in the INNER
JOIN result. These rows are extended to the right with null values in the column
positions corresponding to the columns from the right hand table. For a natural join, the
column values in the common columns are taken from the left hand table.
• The result of the [NATURAL] RIGHT [OUTER] JOIN is the union of two components.
The first component is the result of the equivalent [NATURAL] INNER JOIN. The
second component contains those rows in the right hand table that are not in the
INNER JOIN result. These rows are extended to the left with null values in the column
positions corresponding to the columns from the left hand table. For a natural join, the
column values in the common columns are taken from the right hand table.
• The ON clause (which is associated with the OUTER JOIN in a join condition) and all
predicates in a WHERE clause are filters. At each OUTER JOIN block, the INNER
JOIN result (which matches the join condition in an ON clause) will be presented. Then
all tuples in the preserving table (which is not in the INNER JOIN result) will be
presented by matching columns in the non-preserving table with nulls.
• For three or more table joins, care must be taken when mixing NATURAL....JOIN,
JOIN ON
SearchCondition3 , and JOIN USING ( ColumnList ) clauses.
• The JOIN ON
Searchcondition3 clause produces a result table with the common
columns appearing twice, once for each table participating in the join.