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

Operators and Operator Groups
HP NonStop SQL/MX Query Guide—523728-003
7-15
HYBRID_HASH_ANTI_SEMI_JOIN Operator
WHERE a.unique2=b.unique3 AND a.onepercent = 90
GROUP BY a.ten;
9 . 10 hash_partial_groupby_root
grouping_columns: indexcol(\TESTSYS.$BIG18A.WISC32M.ABASE.TEN)
aggregates:
max(max(indexcol(\TESTSYS.$BIG18A.WISC32M.ABASE.UNIQUE2)))
HYBRID_HASH_ANTI_SEMI_JOIN Operator
Join Group
The HYBRID_HASH_ANTI_SEMI_JOIN operator returns rows from the outer table
where no match occurs. Also see HYBRID_HASH_JOIN Operator on page 7-16 and
HYBRID_HASH_SEMI_JOIN Operator on page 7-17.
The HYBRID_HASH_ANTI_SEMI_JOIN has two child nodes. The description field for
this operator contains:
The difference between join_predicate and hash_join_predicate tokens is that the
former are the nonequijoin predicates, while the latter are equijoin predicates that you
use to help build and probe the hash table.
The example of the HYBRID_HASH_ANTI_SEMI_JOIN operator is based on:
SELECT * FROM partsupp, part
WHERE p_brand <>'Brand#45'
AND ps_suppkey
NOT IN (SELECT s_suppkey FROM supplier
WHERE
s_comment LIKE '%Better BusinessBureauComplaints%');
? XX
211932228834415605 11 HYBRID_HASH_ANTI_SEMI_JOIN
10 3
1.3823995E+015 8.185757
Token Followed by ... Data Type
join_type Inner, natural, left, inner
semi, or inner anti-semi-join
text
join_method Name of join method text
join_predicate Expression of the join
predicates
expr(text)
hash_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)