The WHERE clause of a join query can include additional selection criteria as well as specifying the join condition. For example, to retrieve the names and publishers of all books for which advances greater than $7500 were paid, the statement is:
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 Moon 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 row(s) affected)
Notice that the columns being joined ( pub_id) need not appear in the select list (and thus do not appear in the results).
As many selection criteria as wanted can be included in a join statement. The order of the selection criteria and the join condition is not important.