Databases Reference
In-Depth Information
You can again see that the expression
COUNT(*)
is used in both the
SELECT
and
HAVING
clauses.
Now let's consider an example where you shouldn't use
HAVING
. You want to know
how many tracks are on albums by New Order. Here's the query you shouldn't use:
mysql>
SELECT artist_name, album_name, COUNT(*) FROM
-> artist INNER JOIN album USING (artist_id)
-> INNER JOIN track USING (artist_id, album_id)
-> GROUP BY artist.artist_id, album.album_id
-> HAVING artist_name = "New Order";
+-------------+------------------------------------------+----------+
| artist_name | album_name | COUNT(*) |
+-------------+------------------------------------------+----------+
| New Order | Retro - John McCready FAN | 15 |
| New Order | Substance (Disc 2) | 12 |
| New Order | Retro - Miranda Sawyer POP | 14 |
| New Order | Retro - New Order / Bobby Gillespie LIVE | 15 |
| New Order | Power, Corruption & Lies | 8 |
| New Order | Substance 1987 (Disc 1) | 12 |
| New Order | Brotherhood | 10 |
+-------------+------------------------------------------+----------+
7 rows in set (0.00 sec)
It gets the right answer, but in the wrong—and, for large amounts of data, much slower
—way. It's not the correct way to write the query because the
HAVING
clause isn't being
used to decide what rows should form each group, but is instead being incorrectly used
to filter the answers to display. For this query, we should really use a
WHERE
clause as
follows:
mysql>
SELECT artist_name, album_name, COUNT(*) FROM
-> artist INNER JOIN album USING (artist_id)
-> INNER JOIN track USING (artist_id, album_id)
-> WHERE artist_name = "New Order"
-> GROUP BY artist.artist_id, album.album_id;
+-------------+------------------------------------------+----------+
| artist_name | album_name | COUNT(*) |
+-------------+------------------------------------------+----------+
| New Order | Retro - John McCready FAN | 15 |
| New Order | Substance (Disc 2) | 12 |
| New Order | Retro - Miranda Sawyer POP | 14 |
| New Order | Retro - New Order / Bobby Gillespie LIVE | 15 |
| New Order | Power, Corruption & Lies | 8 |
| New Order | Substance 1987 (Disc 1) | 12 |
| New Order | Brotherhood | 10 |
+-------------+------------------------------------------+----------+
7 rows in set (0.00 sec)
This correct query forms the groups, and then picks which groups to display based on
the
WHERE
clause.
