SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-183
SELECT Statement
ON search-condition
specifies a search-condition for the join. Each column reference in
search-condition must be a column that exists in either of the two
result tables derived from the table references to the left and right of the
JOIN keyword. A join of two rows in the result tables occurs if the condition
is satisfied for those rows.
ON rowset-search-condition
specifies a rowset-search-condition for the join. The array of search
conditions are evaluated successively, and for each condition a join of two
rows in the result tables occurs if the condition is satisfied for those rows.
Each column reference in rowset-search-condition must be a
column that exists in either of the two result tables derived from the table
references to the left and right of the JOIN keyword.
The type of join and the join specification if used determine which rows are
joined from the two table references, as follows:
table-ref CROSS JOIN table-ref
joins each row of the left table-ref with each row of the right
table-ref.
table-ref NATURAL JOIN table-ref
joins rows only where the values of all columns that have the same name
in both tables match. This option is equivalent to NATURAL INNER.
table-ref NATURAL LEFT JOIN table-ref
joins rows where the values of all columns that have the same name in
both tables match, plus rows from the left table-ref that do not meet this
condition.
table-ref NATURAL RIGHT JOIN table-ref
joins rows where the values of all columns that have the same name in
both tables match, plus rows from the right table-ref that do not meet
this condition.
table-ref JOIN table-ref ON
joins only rows that satisfy the condition in the ON clause. This option is
equivalent to INNER JOIN ... ON.
table-ref LEFT JOIN table-ref ON
joins rows that satisfy the condition in the ON clause, plus rows from the
left table-ref that do not satisfy the condition.
Embed