The situation we talk about in this section is that of a small result set based on the intersection of several broad criteria. Each criterion individually would produce a large result set, yet the intersection of those individual, large sets is a very small, final result set returned by the query.
Continuing on with our query example from the preceding section, if the existence test on the article that was ordered is not selective, we must necessarily apply some other criteria elsewhere (otherwise the result set would no longer be a small result set). In this case, the question of whether to use a regular join, a correlated subquery, or an uncorrelated subquery usually receives a different answer depending on both the relative "strength" of the different criteria and the existing indexes.
Let's suppose that instead of checking people who have ordered a Batmobile, admittedly not our best-selling article, we look for customers who have ordered something that I hope is much less unusual, in this case some soap, but purchased last Saturday. Our query then becomes something like this:
select distinct orders.custid from orders join orderdetail on (orderdetail.ordid = orders.ordid) join articles on (articles.artid = orderdetail.artid) where articles.artname = 'SOAP' and
<selective criterion on the date in the orders table>
Quite logically, the processing flow will be the reverse of what we had with a selective article: get the article, then the order ...