SQL/MX 3.1 Query Guide (H06.23+, J06.12+)

SQL/MX Operators
HP NonStop SQL/MX Release 3.1 Query Guide—663851-001
7-44
MERGE_SEMI_JOIN Operator
MERGE_SEMI_JOIN Operator
Join Group
The MERGE_SEMI_JOIN operator returns one row for the first match it finds in the
inner table. Conversely, MERGE_JOIN returns a row for all matches in the inner table.
See MERGE_JOIN Operator on page 7-42.
The MERGE_SEMI_JOIN operator has two child nodes. The description field for this
operator contains:
The following is an example of the MERGE_SEMI_JOIN operator:
control query default hash_joins 'off';
control query default nested_joins 'off';
PREPARE TestQuery7 FROM
SELECT o_orderpriority, COUNT(*)
FROM orders
WHERE o_orderdate >= DATE '1993-07-01'
AND o_orderdate < DATE '1993-10-01'
AND EXISTS (SELECT *
FROM lineitem
WHERE l_orderkey = o_orderkey
AND l_commitdate < l_receiptdate)
GROUP BY o_orderpriority
ORDER BY o_orderpriority;
DESCRIPTION
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
merge_join_predicate Expression of the join predicate. expr(text)
join_type Inner, left, natural, inner semi, or inner anti-semi-
join.
text
join_method Name of join method: merge text
parallel_join_type Type1 or Type2, depending on parallel join
algorithm.
text
selection_predicates Expression of the WHERE clause that is not
included in the merge_join_predicate or in a
selection_predicate of any children.
expr(text)
TP663851.fm Page 44 Monday, October 17, 2011 11:48 AM