The figure shows how Db2® performs a left outer join using a nested loop join for the following SELECT statement:
SELECT A, B, X, Y
FROM (SELECT FROM OUTERT WHERE A=10)
LEFT JOIN INNERT ON B=X;
The table OUTERT contains column A and column B, as shown in Table 1.
Table 1. A sample of the data in the table OUTERT
A B
10 3
10 1
10 2
10 6
10 1
The table INNERT contains column X and column Y, as shown in Table 2.
Table 2. A sample of the data in the table INNERT
X Y
5 A
3 B
2 C
1 D
2 E
9 F
7 G
Db2 performs the following steps to complete the left inner join by using a nested loop join:
  1. Db2 scans OUTERT for rows that satisfy the predicate A=10.
  2. Db2 evaluates each row in OUTERT that satisfies the predicate A=10 in the join predicate B=X.
  3. 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.
  4. 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.