ALLBASE/SQL Reference Manual (36216-90216)
510 Chapter12
SQL Statements S - Z
SELECT
one used to describe a three or more table outer join.
NATURAL indicates that for both inner and outer joins, columns which are common to
two tables being joined will be coalesced into a single column when the
query result is returned. Also, ALLBASE/SQL will automatically identify
and use the columns common to both tables to execute the join. When
using the keyword NATURAL you do not use an ON
SearchCondition3
clause or a USING (
ColumnList
) clause to specify the join columns.
INNER join type indicates that the only rows selected in the join will be those rows
for which a match is found in the join column(s) of both tables being joined.
If the join type is not specified, INNER is the default.
LEFT defines the join as a LEFT OUTER JOIN. For a LEFT OUTER JOIN the
query result will contain not only the matched rows from both tables being
joined, but will also preserve (contain) those rows from the left hand table
in the
FromSpec
for which there is no match in the right hand table. The
preserved rows are extended to the right with null column values for each
column obtained from the right hand table.
For each instance of the keyword JOIN in a
FromSpec
, the named table or
the result table immediately preceding JOIN is the left hand table, the
named table or the result table immediately following JOIN is the right
hand table.
RIGHT defines the join as a RIGHT OUTER JOIN. For a RIGHT OUTER JOIN
the query result will contain not only the matched rows from both tables
being joined, but will also preserve (contain) those rows from the right
hand table in the
FromSpec
for which there is no match in the left hand
table. The preserved rows are extended to the left with null column values
for each column obtained from the left hand table.
For each instance of the keyword JOIN in a
FromSpec
, the named table
immediately following JOIN is the right hand table, the named table
immediately preceding JOIN is the left hand table.
OUTER is optional as a keyword. If either LEFT or RIGHT are used, the join type
is, by default, an outer join.
JOIN specifies that a join is being defined. Evaluation of the
FromSpec
is from
left to right. For a three or more table join, the two tables associated with
the left most instance of the JOIN keyword are joined first, and the result
of that join is considered the left hand table for the next occurring instance
of the keyword JOIN. The same algorithm applies for each additional
occurrence of JOIN. Parentheses can be used to force a change in this
order of evaluation of the
FromSpec
.
ON
SearchCondition3
may only be used when the keyword NATURAL is not used. Two
types of predicates are specified in
SearchCondition3
.
The first type of predicate contains the equality which specifies the join
columns to be used for the associated join. For each occurrence in the
FromSpec
of the keyword JOIN, in the ON
SearchCondition3
clause the
column names specified on each side of the equality must be fully