Joins with Additional Conditions

In a join query, both the where clause and the join condition can include selection criteria.

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.