Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.
Similar to the Nested Loops 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 Loops 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.