Contents Index Nested loops join Nested block join and sorted block

ASA SQL User's Guide
  Query Optimization and Execution
    Query execution algorithms
      Join algorithms

Nested loops semijoin

Similar to the nested loop join described above, the nested loops semijoin algorithm joins each row of the left-hand side with the right-hand side using a nested loops algorithm. As with nested loops join, the right-hand side may be read many times, so for larger inputs an index scan is preferable. However, nested loops semijoin differs from nested loop join in two respects. First, semijoin only outputs values from the left-hand side; the right hand side is used only for restricting which rows of the left-hand side appear in the result. Second, the nested loops semijoin algorithm stops each search of the right-hand side as soon as the first match is encountered. Nested loops semijoin can be used as the join algorithm when join's inputs include table expressions from an existentially-quantified (IN, SOME, ANY, EXISTS) nested query that has been rewritten as a join.


Contents Index Nested loops join Nested block join and sorted block