SQL/MX 3.2 Query Guide (H06.25+, J06.14+)

SQL/MX Operators
HP NonStop SQL/MX Release 3.2 Query Guide—663851-002
7-38
LEFT_NESTED_JOIN Operator
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-47.
The LEFT_NESTED_JOIN has two child nodes. The description field for this operator
contains the following:
The following is an example of the LEFT_NESTED_JOIN operator:
prepare TestQuery37 from
SELECT *
FROM customer LEFT JOIN nation ON c_nationkey = n_nationkey
WHERE c_custkey > 1000 AND c_custkey < 1010
ORDER BY c_custkey;
DESCRIPTION
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
join_type .............. left
join_method .............. nested
Token Followed by ... Data Type
fragment_id A sequential number assigned to the fragment.
0 is always the master executor and 1 is
reserved for the EXPLAIN plan. Numbers 2 to n
will be ESP or DAM fragments.
integer
parent_frag The fragment_id for the parent of the current
fragment. The value is (none) for the master
executor.
integer
fragment_type Master, ESP, or DP2. text
join_type Inner, left, natural, inner semi, or inner anti-
semi-join.
text
join_method Name of join method: nested or in-order nested text
join_predicate Expression of the join predicate. expr(text)
parallel_join_type Type1 or Type2, depending on parallel join
algorithm.
text
selection_predicates Expression of the WHERE clause. expr(text)
TP663851.fm Page 38 Thursday, August 2, 2012 4:47 PM