A Simple Correlated Subquery Example

A correlated subquery is a subquery that needs to refer to the data returned in the main query. They can seem pretty scary at first, but they're fairly simply once you get the hang of it.

In this example, I want to list all my artists, along with the most recent CD they have released. To do this, I have to select all the artists and their CDs, and then use a sub query to select the latested (MAX) date published for each artist.

With a corrolated subquery you can (usually) run the query without the subquery to see all the results, like this:

mysql> SELECT artist.name, cd.title, cd.yearpublished
    -> FROM artist, cdtable AS cd
    -> WHERE artist.id = cd.artid
    -> ;
+----------------+--------------------+---------------+
| name           | title              | yearpublished |
+----------------+--------------------+---------------+
| Phatfish       | Guaranteed         |          2007 |
| Phatfish       | 15                 |          2008 |
| Bethany Dillon | Waking Up          |          2007 |
| Bethany Dillon | Bethany Dillon     |          2004 |
| Bethany Dillon | Imagination        |          2005 |
| Nick Drake     | Made To Love Magic |          2004 |
| Nick Drake     | Five Leaves Left   |          2004 |
| Nick Drake     | Bryter Layter      |          2000 |
+----------------+--------------------+---------------+
8 rows in set (0.00 sec)

This lists out all the CDs for each artist, so we need to tell the query to only list the most recent:

mysql> SELECT artist.name, cd.title, cd.yearpublished
    -> FROM artist, cdtable AS cd
    -> WHERE artist.id = cd.artid
    -> AND cd.yearpublished =
    ->     (SELECT MAX(yearpublished)
    ->      FROM cdtable
    ->      WHERE cdtable.artid = cd.artid)
    -> ;
+----------------+--------------------+---------------+
| name           | title              | yearpublished |
+----------------+--------------------+---------------+
| Phatfish       | 15                 |          2008 |
| Bethany Dillon | Waking Up          |          2007 |
| Nick Drake     | Made To Love Magic |          2004 |
| Nick Drake     | Five Leaves Left   |          2004 |
+----------------+--------------------+---------------+
4 rows in set (0.00 sec)

mysql>

Notice that we have two rows returned for Nick Drake. This is because two CDs match the condition. Of course, Nick Drake wasn't publishing anything in 2004, unfortunately. Had I used the original vinyl publication date, it would have been different.