SELECT A, B, X, Y
FROM (SELECT FROM OUTERT WHERE A=10)
LEFT JOIN INNERT ON B=X;| A | B |
|---|---|
| 10 | 3 |
| 10 | 1 |
| 10 | 2 |
| 10 | 6 |
| 10 | 1 |
| X | Y |
|---|---|
| 5 | A |
| 3 | B |
| 2 | C |
| 1 | D |
| 2 | E |
| 9 | F |
| 7 | G |
- Db2 scans OUTERT for rows that satisfy the predicate A=10.
- Db2 evaluates each row in OUTERT that satisfies the predicate A=10 in the join predicate B=X.
- For those rows, Db2 compares the value of B in OUTERT 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 left outer join.
The composite (result) table is shown in Table 3.
Table 3. The composite table for the left inner join A B C D 10 3 3 B 10 1 1 D 10 2 2 C 10 2 2 E 10 6 null null 10 1 1 D
The following examples explain how Db2 made the composite table.
Example: In a row in OUTERT, 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.
Example: In two rows in OUTERT, A=10 and B=1. Therefore, Db2 checks column X in INNERT 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 OUTERT, A=10 and B=2. Therefore, Db2 checks column X in INNERT 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 OUTERT, A=10 and B=6. Therefore, Db2 checks column X in INNERT for the value 6. Db2 does not find a row where X=6. Because the SELECT statement specified a left outer join, the result table contains a row where A=10, B=6, and X and Y are null.