Hack 11. Convert Aggregate Subqueries to JOINs

You can avoid subqueries using JOIN or OUTER JOIN if they don't use aggregate functions. But what about subqueries that do use aggregation?

Some subqueries are easy to eliminate [Hack #10], but others are a bit trickier. Suppose you have the orders table shown in Table 2-6.

Now suppose you need to show the date on which each customer purchased the most totalitems:

SELECT customer,whn,totalitems FROM orders o1 WHERE o1.whn = ( SELECT MAX(whn) FROM orders o2 WHERE o1.customer = o2.customer );

To do this you need to execute the subquery for every row of orders, so the preceding code may be slow to execute. In addition, older versions of MySQL cannot handle subqueries. To avoid using a subquery, you can use a HAVING clause with a self-join:

SELECT o1.customer,o1.whn,o1.totalitems FROM orders o1 JOIN orders o2 on (o1.customer = o2.customer) GROUP BY o1.customer,o1.whn,o1.totalitems HAVING o1.whn = max(o2.whn)

Here's what you'll get as a result:

+----------+------------+------------+ | customer | whn | totalitems | +----------+------------+------------+ | Brian | 2006-10-10 | 7 | | Jim | 2006-10-12 | 1 | +----------+------------+------------+ 2 rows in set (0.00 sec)

This approach works well for all aggregate functions.