Adaptive Server uses one of these flattening methods to resolve a quantified predicate subquery using a join:
A regular join – if the uniqueness conditions in the subquery mean that it returns a unique set of values, the subquery can be flattened to use a regular join.
An existence join, also known as a semi-join – instead of scanning a table to return all matching values, an existence join returns TRUE when it finds the first matching value and then stops processing. If no matching value is found, it returns FALSE.
A unique reformat – the subquery result set is selected into a worktable, sorted to remove duplicates, and a clustered index is built on the worktable. The clustered index is used to perform a regular join.
A duplicate elimination sort optimization – the subquery is flattened into a regular join that selects the results into a worktable, then the worktable is sorted to remove duplicate rows
