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.
