Anti Join
An anti join returns rows from the outer table that have no match in the inner table, used to optimize NOT EXISTS / NOT IN subqueries.
Purpose
To optimize NOT EXISTS/NOT IN subqueries and recognize anti join execution plans.
Mechanism
An outer row with no inner match is output. Implementations: Hash Anti Join (build a hash table on the inner table; outer probes that miss produce output) or Nested Loop Anti Join. Mind NULL semantics: NOT IN with NULLs returns an empty result.
Use cases
- Subquery optimization
- NOT EXISTS optimization
- Join optimization