The figure shows how Db2® performs a merge scan join for the following SELECT statement:
SELECT A, B, X, Y
FROM OUTER, INNER
WHERE A=10 AND B=X;
The table OUTER contains column A and column B, as shown in the following table.
Table 1. A sample of the data in the table OUTER
A B
10 1
10 1
10 2
10 3
10 6
The table INNER contains column X and column Y, as shown in Table 2.
Table 2. A sample of the data in the table INNER
X Y
1 D
2 C
2 E
3 B
5 A
7 G
9 F
Db2 performs the following steps to complete the merge scan join:
  1. Db2 condenses and sorts OUTER, or accesses OUTER through an index on column B.
  2. Db2 condenses and sorts INNER.
  3. Db2 scans OUTER for rows that satisfy the predicate A=10.
  4. Db2 evaluates each row in OUTER that satisfies the predicate A=10 in the join predicate B=X.
  5. For those rows, Db2 compares the value of B in OUTER to the value of X in INNERT. Db2 finds all matching rows by using a table space scan or an index scan.
  6. Db2 produces a composite table that includes the results of the merge scan join. The composite (result) table is shown in Table 3.
    Table 3. The composite table for the merge scan join
    A B C D
    10 1 1 D
    10 1 1 D
    10 2 2 C
    10 2 2 E
    10 3 3 B

The following examples explain how Db2 made the composite table.

Example: In two rows in OUTERT, A=10 and B=1. Therefore, Db2 checks column X in INNER for the value 1. Db2 finds a row where X=1 and Y=D. Therefore, the result table contains two rows where A=10, B=1, X=1, and Y=D.

Example: In a row in OUTER, A=10 and B=2. Therefore, Db2 checks column X in INNER for the value 2. Db2 finds two rows where X=2. In one of these rows, Y=C. In the other, Y=E. Therefore, the result table contains one row where A=10, B=2, X=2, and Y=C, and another row where A=10, B=2, X=2, and Y=E.

Example: In a row in OUTER, A=10 and B=3. Therefore, Db2 checks column X in INNERT for the value 3. Db2 finds a row where X=3 and Y=B. Therefore, the result table contains a row where A=10, B=3, X=3, and Y=B.