SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Operators and Operator Groups
HP NonStop SQL/MX Query Guide—523728-003
7-24
LEFT_MERGE_JOIN Operator
LEFT_MERGE_JOIN Operator
Join Group
The LEFT_MERGE_JOIN operator describes a portion of an execution plan that
involves a merge join. The LEFT_MERGE_JOIN differs from MERGE_JOIN only when
it does not find a match in the inner table. When no match is found, the left row is still
returned, and the data from the right table is set to null. See MERGE_JOIN Operator
on page 7-28.
The LEFT_MERGE_JOIN operator has two child nodes. The description field for this
operator contains:
The example of the LEFT_MERGE_JOIN operator is based on:
SELECT a.ten, MAX(a.unique2) FROM abase a LEFT JOIN bbase b
ON a.unique2=b.unique3 WHERE a.tenpercent = 9
GROUP BY a.ten FOR READ UNCOMMITTED ACCESS;
2 4 5 left_merge_join
merge_join_predicate:
(indexcol(\TESTSYS.$BIG18A.WISC32M.ABASE.UNIQUE2) =
indexcol(\TESTSYS.$BIG18A.WISC32M.IXB4.UNIQUE3))
join_type: left
join_method: merge
parallel_join_type: 1
LEFT_NESTED_JOIN Operator
Join Group
The LEFT_NESTED_JOIN operator describes a portion of an execution plan that
involves a nested join. The LEFT_NESTED_JOIN sends each outer (left) row to the
inner (right) child. The right child finds all the matches for a row and returns all the
matches. If an outer row finds no matches in the inner table, the outer row is returned,
and nulls are supplied for inner table values. See NESTED_JOIN Operator on
page 7-32.
Token Followed by ... Data Type
join_type Inner, left, natural, inner semi,
or inner anti-semi-join
text
join_method Name of join method text
merge_join_predicate Expression of the join
predicates
expr(text)
parallel_join_type Type1 or Type2, depending
on parallel join algorithm
text
selection_predicates Expression of the selection
predicates
expr(text)