A nested loop join is one of the methods that DB2® uses to join tables.
DB2 scans the outer table once. For each qualified row in the outer table, DB2 scans the inner table once to find the corresponding row in the inner table. Nested loop joins can be used for both inner joins and left outer joins. Right outer joins are transformed to left outer joins during SQL optimization.
Graphical notation
A nested loop join construct consists of one NLJOIN node and two subtrees. The right subtree represents the inner table and the left subtree represents the outer table. Both tables can be accessed with either a table space scan, single-index access, or multiple-index access. The left subtree can also include another join operation (nested loop join, merge scan join, hybrid join, or star join).
Recommendations
- Use a nested loop join when the outer table has a small number of rows or qualifying rows or the inner table has a good index on the join column. Create a suitable, highly clustered index to speed up a nested loop join.
- When DB2 chooses a nested loop join, look at the number of qualifying rows and related predicates in the outer table to determine the efficiency of the join.