Flattened subqueries

Some subqueries can be flattened into joins. The g_join and nl_g_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 an existence join, with the scan of t2 stopping on the first matching row of t2 for each qualifying row in t1:

    (scan t1)
    (scan (table t2 (in (subq 1) ) )) 

The join order t2, t1 requires other means to guarantee the duplicate elimination:

    (scan (table t2 (in (subq 1) ) ) )
    (scan t1)

Using this abstract plan, the optimizer can decide to use:

The abstract plan does not need to specify the creation and scanning of the worktables needed for the last two options.

For more information on subquery flattening, see “Flattening in, any, and exists subqueries”.