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-49
NESTED_SEMI_JOIN Operator
NESTED_SEMI_JOIN Operator
Join Group
The NESTED_SEMI_JOIN operator returns only one matched row from the inner table
and ignores duplicate matches. See NESTED_JOIN Operator on page 7-47.
The NESTED_SEMI_JOIN operator has two child nodes. The description field for this
operator contains the following:
The following is an example of the NESTED_SEMI_JOIN operator:
Control Query Default merge_joins 'off';
Control Query Default hash_joins 'off';
PREPARE TestQuery7a 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
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
parallel_join_type Type1 or Type2, depending on parallel join
algorithm.
text
join_predicate Expression of the ON clause that has not been
pushed down to the inner scan, typically empty.
expr(text)
selection_predicate 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 49 Thursday, August 2, 2012 4:47 PM