-> artist INNER JOIN album USING (artist_id)

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 inset 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

Download 4.24 Mb.

Share with your friends: