Some subqueries can be flattened into joins. The join, nl_join, m_join, and h_join operators leave it to the optimizer to detect when an existence join is needed. For example, this query includes a subquery introduced with exists:
select * from t1
where c12 > 0
and exists (select * from t2
where t1.c11 = c21 and c22 < 100)
The semantics of the query require an existence join between t1 and t2. The join order t1, t2 is interpreted by the optimizer as a semijoin, with the scan of t2 stopping on the first matching row of t2 for each qualifying row in t1:
(join
(scan t1)
(scan t2)
)
The join order t2, t1 requires other means to guarantee the duplicate elimination:
(join
(distinct
(scan t2)
)
(scan t1)
)
Using this abstract plan, the optimizer can decide to use:
A unique index on t2.c21, if one exists, with a regular join.
The unique reformatting strategy, if no unique index exists. In this case, the query will probably use the index on c22 to select the rows into a worktable.
The duplicate elimination sort optimization strategy, performing a regular join and selecting the results into the worktable, then sorting the worktable.
The abstract plan does not need to specify the creation and scanning of the worktables needed for the last two options.
