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

Operators and Operator Groups
HP NonStop SQL/MX Query Guide—523728-003
7-29
MERGE_SEMI_JOIN Operator
The MERGE_JOIN operator has two child nodes. The description field for this operator
contains:
The example of the MERGE_JOIN operator is based on:
SELECT l_orderkey,
CAST(SUM(l_extendedprice*(1-l_discount))AS
NUMERIC(18,2)), o_orderdate, o_shippriority
FROM customer,orders,lineitem
WHERE c_mktsegment = 'BUILDING'
AND c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND o_orderdate < DATE '1995-03-15'
AND l_shipdate > DATE '1995-03-15'
GROUP BY l_orderkey, o_orderdate, o_shippriority
ORDER BY 2 DESC,3 ASC;
? XX
211862597497309538 12 MERGE_JOIN
8 1 1
3.3329999E-001 1.4655510E-004 2.2940764E+000 CPU_TIME:
0.345902 IO_TIME: 0.033786 MSG_TIME: 0.067074 IDLETIME: 0
PROBES: 1
merge_join_predicate:
(indexcol(\TESTSYS.$DATA14.SPTPCD.ORDERS.O_CUSTKEY) =
indexcol(\TESTSYS.$DATA14.SPTPCD.CUSTOMER.C_CUSTKEY))
join_type: inner
join_method: merge
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-28.
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)