Flattened subqueries executed as existence joins

All in, any, and exists queries test for the existence of qualifying values and return TRUE as soon as a matching row is found.

The optimizer converts the following subquery to an existence join:

select title 
    from titles
    where title_id in 
        (select title_id 
         from titleauthor)
    and title like "A Tutorial%"

The existence join query looks like the following ordinary join, although it does not return the same results:

select title 
    from titles T, titleauthor TA
    where T.title_id = TA.title_id
        and title like "A Tutorial%"

In the pubtune database, two books match the search string on title. Each book has multiple authors, so it has multiple entries in titleauthor. A regular join returns five rows, but the subquery returns only two rows, one for each title_id, since it stops execution of the join at the first matching row.

When subqueries are flattened to use existence joins, the showplan output shows output for a join, with the message “EXISTS TABLE: nested iteration” as the join type for the table in the subquery.