7.13.1 Specification format and rules for join method specifications

(1) Specification format

join-method-specification ::= /*>> BY {NEST|HASH} [({LEFT|RIGHT} FIRST)] <<*/

To Page Top

(2) Explanation of specification format

BY {NEST|HASH}:

NEST:

Specifies a nested loop join as the join method.

HASH:

Specifies a hash join as the join method.

({LEFT|RIGHT} FIRST):

LEFT FIRST:

Specifies that the outer table is to be the table reference on the left side of the joined table.

RIGHT FIRST:

Specifies that the outer table is to be the table reference on the right side of the joined table.

When neither LEFT FIRST nor RIGHT FIRST is specified, HADB automatically determines which of the joined tables in which the two table references are specified is to be the outer table.

You can check whether the join method specification was applied using the access path information. For details about how to check this, see Table joining methods in Information displayed in the tree view in the HADB Application Development Guide.

To Page Top

(3) Rules

1. If a join method that HADB cannot execute is specified, the join method specification is invalid. When the join method specification is invalid, HADB determines the join method automatically.

2. The character string enclosed in /*>> and <<*/ is not a comment. An error results if you specify something other than a join method specification.

To Page Top

(4) Examples

Example 1

SELECT * FROM "T1" INNER JOIN /*>>BY NEST<<*/ "T2"
                   ON "T1"."C1"="T2"."C1"

The underlined portion indicates the join method specification.

When the above SELECT statement is executed, a nested loop join is used to joined tables T1 and T2. The outer and inner tables are automatically determined by HADB.

Example 2

SELECT * FROM "T1" INNER JOIN /*>>BY NEST (LEFT FIRST)<<*/ "T2"
                   ON "T1"."C1"="T2"."C1"

The underlined portion indicates the join method specification.

When the above SELECT statement is executed, a nested loop join is used to join tables T1 and T2. T1 is the outer table and T2 is the inner table.

Example 3

SELECT * FROM "T1" INNER JOIN /*>>BY NEST (RIGHT FIRST)<<*/ "T2"
                   ON "T1"."C1"="T2"."C1"

The underlined portion indicates the join method specification.

When the above SELECT statement is executed, a nested loop join is used to join tables T1 and T2. T2 is the outer table and T1 is the inner table.

Example 4

SELECT * FROM "T1" INNER JOIN /*>>BY HASH<<*/ "T2"
                   ON "T1"."C1"="T2"."C1"

The underlined portion indicates the join method specification.

When the above SELECT statement is executed, a hash join is used to join tables T1 and T2. The outer and inner tables are automatically determined by HADB.

Example 5

SELECT * FROM "T1" INNER JOIN /*>>BY HASH (LEFT FIRST)<<*/ "T2"
                   ON "T1"."C1"="T2"."C1"

The underlined portion indicates the join method specification.

When the above SELECT statement is executed, a hash join is used to join tables T1 and T2. T1 is the outer table and T2 is the inner table.

Example 6

SELECT * FROM "T1" INNER JOIN /*>>BY HASH (RIGHT FIRST)<<*/ "T2"
                   ON "T1"."C1"="T2"."C1"

The underlined portion indicates the join method specification.

When the above SELECT statement is executed, a hash join is used to join tables T1 and T2. T2 is the outer table and T1 is the inner table.

To Page Top