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

Operators and Operator Groups
HP NonStop SQL/MX Query Guide—523728-003
7-28
MERGE_ANTI_SEMI_JOIN Operator
MERGE_ANTI_SEMI_JOIN Operator
Join Group
The MERGE_ANTI_SEMI_JOIN operator returns rows only when no match occurs in
the inner table. The operator discards all rows that have a match. Also see
MERGE_JOIN Operator on page 7-28 and MERGE_SEMI_JOIN Operator on
page 7-29.
The MERGE_ANTI_SEMI_JOIN operator has two child nodes. The description field for
this operator contains:
The example of MERGE_ANTI_SEMI_JOIN is based on:
INSERT INTO $sql06.sch184.d2pwl24
(SELECT* FROM $big18a.sqldpops.b2pwl24
WHERE sdec9_uniq NOT IN (SELECT DISTINCT(sdec2_500)
FROM $big18a.sqldpops.b2pwl10));
4 8 9 merge_anti_semi_join
merge_join_predicate:
(indexcol(\TESTSYS.$BIG18A.SQLDPOPS.B2PWL24.SDEC9_UNIQ) =
cast(indexcol(\TESTSYS.$BIG18A.SQLDPOPS.B2PWL10.SDEC2_500)))
join_type: inner anti-semi
join_method: merge
parallel_join_type: 1
MERGE_JOIN Operator
Join Group
The MERGE_JOIN operator describes a portion of an execution plan that involves a
merge join. This operator joins the data from its two child nodes. The data streams
from both children must be in the same order. The operator joins all matching rows
from each data stream. The MERGE_JOIN operator works only with equijoins.
Token Followed by... Data Type
merge_join_predicate Expression of the join
predicates
expr(text)
join_type Inner, left, natural,
inner semi, or inner
anti-semi-join
text
join_method Name of join method text
parallel_join_type Type1 or Type2,
depending on parallel
join algorithm
text