Let's say you want to get a list of all customers in your database and also include all items from their last purchase. How would you do it?
Here's one way to do it using a sub query.
SELECT customers.firstname, customers.lastname, purchases.createdat, items.name FROM customers INNER JOIN ( SELECT customerid, MAX(createdat) AS createdat FROM purchases GROUP BY customerid ) AS lastpurchase ON customers.id = lastpurchase.customerid INNER JOIN purchases ON lastpurchase.customerid = purchases.customerid AND lastpurchase.createdat = purchases.createdat INNER JOIN items ON purchases.id = items.purchaseid
I like this solution because the query is easy to read and understand. However, there are other ways to accomplish the same thing. One improvement was suggested by Jacob Bogers on Twitter. You can speed up the query by replacing the sub query portion of the query above with the following SQL instead.
SELECT customerid, createdat FROM purchases a WHERE NOT EXISTS ( SELECT 1 FROM purchases b WHERE a.customerid = b.customerid AND a.createdat < b.createdat )
Some other considerations.
- If you want to include customers without purchases, just replace all
INNER JOINstatements with
LEFT OUTER JOINstatements instead.
- If you use auto-incrementing primary keys in the purchases table you can call
MAXon that column instead of the