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

Operators and Operator Groups
HP NonStop SQL/MX Query Guide—523728-003
7-33
NESTED_SEMI_JOIN Operator
AND p_size = 15
AND p_type like '%BRASS'
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'EUROPE'
AND ps_supplycost = (SELECT MIN(ps_supplycost)
FROM partsupp ps1,supplier s1, nation n1,region r1
WHERE p_partkey = ps1.ps_partkey
AND s1.s_suppkey = ps1.ps_suppkey
AND s1.s_nationkey = n1.n_nationkey
AND n1.n_regionkey = r1.r_regionkey
AND r1.r_name = 'EUROPE’
ORDER BY s_acctbal desc, n_name, s_name, p_partkey;
? XX
211862597476194167 11 NESTED_JOIN
7 1 0
1.0000000E+001 6.9649995E-006 1.8949854E+000 CPU_TIME:
0.792485 IO_TIME: 0.091286 MSG_TIME: 0.111842 IDLETIME: 1.1025
PROBES: 1
join_type: inner
join_method: nested
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-32.
The NESTED_SEMI_JOIN operator has two child nodes. The description field for this
operator contains:
The example of the NESTED_SEMI_JOIN operator is based on:
DELETE FROM $big18b.sch183.d2pns03
WHERE ts1_n100 in
(SELECT ts1_n100 FROM $big18a.sqldpops.b2pns03
WHERE ts1_n100 BETWEEN
(TIMESTAMP '1900-01-01:00:10:00.000000') AND
(TIMESTAMP '2100-01-01:00:20:00.000000'));
2 6 7 nested_semi_join
join_type: inner semi
join_method: nested
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
parallel_join_type Type1 or Type2, depending
on parallel join algorithm
text