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.