5.5.1 About nested-loop join

HADB joins tables by repeating as many times as there are rows in the outer table a matching process that involves using the value in the joined column in the outer table as the basis for searching the joined column in the inner table. This joining method is called nested loop join.

If an index is defined for the column that is specified in the join condition for joining the outer and inner tables, the index is used when evaluating the join condition. This narrows the search range of the inner table.

The following explains the table joining method using a nested loop join by way of an example in which a nested loop join is used when the SELECT statement shown below is executed.

Example:

SELECT * FROM "T1","T2" WHERE "T1"."C2">10 AND "T1"."C1">"T2"."C1"

HADB determines the table that is to be the outer table and the table that is to be the inner table. In this example, table T1 is the outer table and table T2 is the inner table. Columns T1.C1 and T2.C1 specified in the underlined join condition are the joined columns.

Figure 5‒6: Table joining method using a nested loop join

Explanation:

HADB retrieves the value of joined column B3 from the outer table and matches it with the value of the joined column in the inner table. Next, HADB retrieves the value of joined column A1 from the outer table and matches it with the value of the joined column in the inner table. HADB repeats this processing as many times as there are rows in the outer table.

Consider the example of a nested loop join with table T1 as the outer table and table T2 as the inner table. If an index is defined for the columns specified in the join condition ("T1"."C1" and "T2"."C1"), the index might be used when evaluating the join condition ("T1"."C1">"T2"."C1"). This can narrow the search range of the inner table.

Note

In the case of a joined table, you can specify the outer table in a join method specification. For details about join method specifications, see Specification format and rules for join method specifications in the manual HADB SQL Reference.

To Page Top