Semi Join
A semi join returns outer-table rows that have a match in the inner table, without returning inner-table columns; used for EXISTS subquery optimization.
Purpose
To optimize EXISTS/IN subqueries, avoiding unnecessary data transfer and joins.
Mechanism
Only checks whether an outer row has an inner match; if yes, output the outer row (no inner data attached). Implementation: deduplicate the inner join column, then probe the outer table. Produces less output and fewer intermediate results than a regular join.
Use cases
- Subquery optimization
- EXISTS optimization
- Join optimization