The where clause of a join query can include selection criteria as well as the join condition. For example, to retrieve the names and publishers of all the books for which advances of more than $7500 were paid, use:
select title, pub_name, advance from titles, publishers where titles.pub_id = publishers.pub_id and advance > $7500
title pub_name advance ----------------------------- -------------------- --------- You Can Combat Computer Stress! New Age Books 10,125.00 The Gourmet Microwave Binnet & Hardley 15,000.00 Secrets of Silicon Valley Algodata Infosystems 8,000.00 Sushi, Anyone? Binnet & Hardley 8,000.00 (4 rows affected)
The columns being joined (pub_id from titles and publishers) need not appear in the select list and, therefore, do not show up in the results.
You can include as many selection criteria as you want in a join statement. The order of the selection criteria and the join condition has no effect on the result.
