Convert Aggregate Subqueries to JOINs

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 Figure.

The orders table

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 = (
  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.

 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows