SELECT A, B, X, Y
FROM OUTER, INNER
WHERE A=10 AND B=X;| A | B |
|---|---|
| 10 | 1 |
| 10 | 1 |
| 10 | 2 |
| 10 | 3 |
| 10 | 6 |
| X | Y |
|---|---|
| 1 | D |
| 2 | C |
| 2 | E |
| 3 | B |
| 5 | A |
| 7 | G |
| 9 | F |
- Db2 condenses and sorts OUTER, or accesses OUTER through an index on column B.
- Db2 condenses and sorts INNER.
- Db2 scans OUTER for rows that satisfy the predicate A=10.
- Db2 evaluates each row in OUTER that satisfies the predicate A=10 in the join predicate B=X.
- 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.
- 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.