SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-25
Nested Join
query. In this discussion, an outer table is a table that is examined before another
table. An inner table is a table examined after the outer table.
Two tables can be joined even if there are no joining predicates. In this case, SQL
creates the new table by concatenating every row in one table with every row in the
other table, using a nested join strategy.
Nested Join
The nested join method retrieves rows one-at-a-time from the outer table and
compares them with the rows in the inner table. If index predicates are specified for the
inner table, they are applied before accessing the inner table.
This method retrieves the rows from the inner table that satisfy the join predicate and
concatenates them with the corresponding rows from the outer table.
The nested join method uses VSBB. SQL sets the begin and end keys to the same
value. Therefore, the buffer contains only the rows that match the current input. The
key-sequenced merge join uses VSBB differently. For more information on the key-
sequenced merge join, see Key-Sequenced Merge Join on page 3-27. For information
on VSBB, see Section 4, Improving Query Performance With Environmental Options.
Figure 3-1 shows a nested join.
For information on forcing a nested join, see the SQL/MP Reference Manual. Also, see
Specifying a Join Method on page 3-43.
Sort Merge Join
The sort merge join method requires that the joining columns of the outer and inner
tables exist in ascending or descending order. The sort merge join method is used only
for equijoin queries (queries in which the operator in the join predicate is an equal (=)
comparison operator).
The inner table is always sorted into a temporary entry-sequenced table prior to
performing the join. The optimizer uses one column for the sort. From potential equijoin
Figure 3-1. Nested Join
Nested join result
Outer table:
One sequential
pass
In ner table:
Random access
0
27
VST0301.vsd