There's an interesting
article by Stéphane Faroult on ONLamp about when to use subselects rather than JOINs
in SQL queries. He says that only tables from which you are returning columns
or those tables necessary to join such tables should appear in the FROM clause.
All other tables used to limit the rows returned should appear in subselects in
the WHERE clause. He rewrites
select distinct a.CUSTOMER_ID, a.CUSTOMER_NAME from CUSTOMERS a, ORDERS b where a.ZIP_CODE in ... and b.ORDERED_DATE >= ... and b.CUSTOMER_ID = a.CUSTOMER_ID order by a.CUSTOMER_NAME
select a.CUSTOMER_ID, a.CUSTOMER_NAME from CUSTOMERS a where a.ZIP_CODE in ... and a.CUSTOMER_ID in (select b.CUSTOMER_ID from ORDERS b where b.ORDERED_DATE >= ...) order by a.CUSTOMER_NAME
He also gives another way to rewrite the query as an uncorrelated subquery. (Read the article for an explanation of the difference between correlated and uncorrelated queries.)
Having done almost exclusively MySQL development, I have yet to ever use subselects, but I'll definitely keep Faroult's article in mind for when I do.
The state is that great fiction by which everyone tries to live at the expense of everyone else. - Frederic Bastiat