ASA SQL User's Guide
Query Optimization and Execution
Query execution algorithms
Join algorithms
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.