Database Reference
In-Depth Information
| Van Gogh | Starry Night |
| Van Gogh | The Potato Eaters |
+----------+-------------------+
Rows in the result that have
NULL
in the
title
column correspond to artists listed in
the
artist
table for whom you have no paintings.
The same principles apply when producing summaries using master and detail tables.
For example, to summarize your art collection by number of paintings per artist, you
might ask, “How many paintings are there per artist in the
painting
table?” To find the
answer based on artist ID but display artist name (from the
artist
table), count the
paintings with this statement:
mysql>
SELECT artist.name, COUNT(painting.a_id) AS paintings
->
FROM artist INNER JOIN painting ON artist.a_id = painting.a_id
->
GROUP BY artist.name;
+----------+-----------+
| name | paintings |
+----------+-----------+
| Da Vinci | 2 |
| Renoir | 1 |
| Van Gogh | 2 |
+----------+-----------+
On the other hand, you might ask, “How many paintings did each artist paint?” This is
the same question as the previous one (and the same statement answers it), as long as
every artist in the
artist
table has at least one corresponding
painting
table row. But
if you have artists in the
artist
table not yet represented by any paintings in your
collection, they do not appear in the statement output. To produce a summary that also
includes artists with no paintings in the
painting
table, use a
LEFT
JOIN
:
mysql>
SELECT artist.name, COUNT(painting.a_id) AS paintings
->
FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id
->
GROUP BY artist.name;
+----------+-----------+
| name | paintings |
+----------+-----------+
| Da Vinci | 2 |
| Monet | 0 |
| Renoir | 1 |
| Van Gogh | 2 |
+----------+-----------+
Beware of a subtle error that is easy to make when writing that kind of statement. Sup‐
pose that you write the
COUNT()
function slightly differently, like so:
mysql>
SELECT artist.name, COUNT(*) AS paintings
->
FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id
->
GROUP BY artist.name;
