17.5.6 Types of join methods

In the editing result, L indicates an outer table and R indicates an inner table. For details about the join methods, see the HiRDB Version 8 UAP Development Guide.

Organization of this subsection
(1) Merge join
(2) Nested loops join
(3) Hash join
(4) SELECT-APSL
(5) Distributed nest-loop-join
(6) Direct product

(1) Merge join

The merge join method is effective when the outer table cannot be narrowed down significantly.

SORT MERGE JOIN
This method retrieves rows from the outer and inner tables, creates a work table for each of them, sorts the rows in the work tables, then joins the rows satisfying the join conditions.
KEY SCAN MERGE JOIN
This method retrieves rows from the outer and inner tables by KEY SCAN and joins the rows satisfying the join conditions.
LIST SCAN MERGE JOIN
This method creates work tables from the outer and inner tables, retrieves rows in ascending order of the joined columns without sorting, then joins the rows satisfying the join conditions.
L-KEY R-LIST MERGE JOIN
This method retrieves rows from the outer table by KEY SCAN, creates a work table for the inner table and retrieves rows without sorting, then joins the rows satisfying the join conditions.
L-KEY R-SORT MERGE JOIN
The method retrieves rows from the outer table by KEY SCAN, creates a work table for the inner table and retrieves rows after sorting, then joins the rows satisfying the join conditions.
L-LIST R-KEY MERGE JOIN
This method creates a work table for the outer table and retrieves rows without sorting, retrieves rows from the inner table by KEY SCAN, then joins the rows satisfying the join conditions.
L-LIST R-SORT MERGE JOIN
This method creates a work table for the outer table and retrieves rows without sorting, creates a work table for the inner table and retrieves rows after sorting, then joins the rows satisfying the join conditions.
L-SORT R-KEY MERGE JOIN
This method creates a work table for the outer table and retrieves rows after sorting, retrieves rows from the inner table by KEY SCAN, then joins the rows satisfying the join conditions.
L-SORT R-LIST MERGE JOIN
This method creates a work table for the outer table and retrieves rows after sorting, creates a work table for the inner table and retrieves rows without sorting, then joins the rows satisfying the join conditions.

(2) Nested loops join

The nested loops join method is effective when an index is defined for the inner table and the outer table can be narrowed down.

NESTED LOOPS JOIN
This join method involves nested loop processing; that is, it retrieves one row at a time from the outer table, matches it with each row in the inner table, then retrieves the row if it satisfies the join conditions.
R-LIST NESTED LOOPS JOIN
This method first retrieves rows from the inner table and creates a work table. Next, it retrieves one row at a time from the outer table, matches it with each row of the work table that was created from the inner table, then retrieves the row if it satisfies the join conditions.

(3) Hash join

HASH JOIN{FOR EACH}
This method first creates a hash table by hashing with the value of joined columns in the inner table. Next, it retrieves one row at a time from the outer table and hashes it with the value of joined columns in the outer table, then matches the row with the hash table created from the inner table to join the row.

(4) SELECT-APSL

SELECT-APSL is a method for dynamically determining the join method during SQL execution.

SELECT-APSL (applicable only to HiRDB/Parallel Server)
If the specified conditions contain a ? parameter, the optimum join method may depend on the value of the ? parameter. The optimum join method cannot be selected during SQL optimization processing because the value of the ? parameter cannot be determined. This method selects a join method by calculating the hit rate during SQL execution.

(5) Distributed nest-loop-join

Distributed nest-loop-join is a method for using nested loop join when a foreign table is different from the inner table.

DISTRIBUTED NESTED LOOPS JOIN
This join method uses nested loop processing, which means that it retrieves rows from the outer table, executes an SQL statement to pass a row values in the outer table as a variable to the foreign server that contains the foreign table to be used as the inner table for each retrieved row, and then compares the rows to retrieve only those that satisfy the join conditions.

(6) Direct product

CROSS JOIN
This method joins all rows in the outer table and inner table. After joining the tables, it checks any condition involving two or more tables.