Binary join information

Output from the db2expln command can contain information about joins in an explained statement.

Whenever a binary join is performed, one of the following statements is displayed:

   Hash Join
   Merge Join
   Nested Loop Join

A left outer join is indicated by one of the following statements:

   Left Outer Hash Join
   Left Outer Merge Join
   Left Outer Nested Loop Join

In the case of a merge or nested loop join, the outer table of the join is the table that was referenced in the previous access statement (shown in the output). The inner table of the join is the table that was referenced in the access statement that is contained within the scope of the join statement. In the case of a hash join, the access statements are reversed: the outer table is contained within the scope of the join, and the inner table appears before the join.

In the case of a hash or merge join, the following additional statements might appear:

```
   Early Out: Single Match Per Outer Row
```
In some circumstances, a join simply needs to determine whether any row in the inner table matches the current row in the outer table.
```
   Residual Predicate(s)
   |  #Predicates = n
```
It is possible to apply predicates after a join has completed. This statement displays the number of predicates being applied.

In the case of a hash join, the following additional statements might appear:

```
   Process Hash Table For Join
```
The hash table is built from the inner table. This statement displays if the building of the hash table was pushed down into a predicate during access to the inner table.
```
   Process Probe Table For Hash Join
```
While accessing the outer table, a probe table can be built to improve the performance of the join. This statement displays if a probe table was built during access to the outer table.
```
   Estimated Build Size: n
```
This statement displays the estimated number of bytes that are needed to build the hash table.
```
   Estimated Probe Size: n
```
This statement displays the estimated number of bytes that are needed to build the probe table.

In the case of a nested loop join, the following statement might appear immediately after the join statement:

   Piped Inner

This statement indicates that the inner table of the join is the result of another series of operations. This is also referred to as a composite inner.

If a join involves more than two tables, the explain steps should be read from top to bottom. For example, suppose the explain output has the following flow:

   Access ..... W
   Join
   |  Access ..... X
   Join
   |  Access ..... Y
   Join
   |  Access ..... Z

The steps of execution would be:

Take a qualifying row from table W.
Join a row from W with the next row from table X and call the result P1 (for partial join result number 1).
Join P1 with the next row from table Y to create P2.
Join P2 with the next row from table Z to create one complete result row.
If there are more rows in Z, go to step 4.
If there are more rows in Y, go to step 3.
If there are more rows in X, go to step 2.
If there are more rows in W, go to step 1.