ASA SQL User's Guide
Query Optimization and Execution
Query execution algorithms
Join algorithms
The hash anti-semijoin variant of the hash join algorithm performs an anti-semijoin between the left-hand side and the right-hand side. As with nested loop anti-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 anti-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. Each left-hand row is output only if it fails to match any row from the right-hand side. As with nested loop anti-semijoin, hash anti-semijoin will be utilized in cases where the join's inputs include table expressions from a universally-quantified (NOT IN, ALL, NOT EXISTS) nested query that has been rewritten as an anti-join. Hash anti-semijoin will tend to outperform nested loop anti-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 anti-semijoin algorithm may revert to a nested loops anti-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.