ASA SQL User's Guide
Query Optimization and Execution
Query execution algorithms
Join algorithms
The hash semijoin variant of the hash join algorithm performs a semijoin between the left-hand side and the right-hand side. As with nested loop semijoin described above, the right-hand side is only used to determine which rows from the left-hand side appear in the result. With hash semijoin the right-hand side is read to form an in-memory hash table which is subsequently probed by each row from the left-hand side. As soon as any match is found, the left-hand row is output to the result and the match process starts again for the next left-hand row. At least one equality join condition must be present in order for hash semijoin to be considered by the query optimizer. As with nested loop semijoin, hash semijoin will be utilized in cases where the join's inputs include table expressions from an existentially-quantified (IN, SOME, ANY, EXISTS) nested query that has been rewritten as a join. Hash semijoin will tend to outperform nested loop semijoin when the join condition includes inequalities, or if a suitable index does not exist to make indexed retrieval of the right-hand side sufficiently inexpensive.
As with hash join, the hash semijoin algorithm may revert to a nested loops semijoin strategy if there is insufficient cache memory to enable the operation to complete. Should this occur, a performance counter is incremented. You can read this monitor with the QueryLowMemoryStrategy database/connection property, or in the Query: Low Memory Strategies counter in Windows Performance Monitor.
Note: Windows Performance Monitor may not be available on Windows CE, 95, 98, or Me.
For more information, see QueryLowMemoryStrategy in Connection-level properties.