Database Reference
In-Depth Information
+----------+-----------+
| name | paintings |
+----------+-----------+
| Da Vinci | 2 |
| Monet | 1 |
| Renoir | 1 |
| Van Gogh | 2 |
+----------+-----------+
Now every artist appears to have at least one painting. Why the difference? The problem
is the use of
COUNT(*)
rather than
COUNT(painting.a_id)
. The way
LEFT
JOIN
works
for unmatched rows in the left table is that it generates a row with all the columns from
the right table set to
NULL
. In the example, the right table is
painting
. The statement
that uses
COUNT(painting.a_id)
works correctly because
COUNT(
expr
)
counts only
non-
NULL
values. The statement that uses
COUNT(*)
is incorrect because it counts
rows
, including those containing
NULL
that correspond to missing artists.
LEFT
JOIN
is suitable for other types of summaries as well. To produce additional col‐
umns showing the total and average prices of the paintings for each artist in the
ar
tist
table, use this statement:
mysql>
SELECT artist.name,
->
COUNT(painting.a_id) AS 'number of paintings',
->
SUM(painting.price) AS 'total price',
->
AVG(painting.price) AS 'average price'
->
FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id
->
GROUP BY artist.name;
+----------+---------------------+-------------+---------------+
| name | number of paintings | total price | average price |
+----------+---------------------+-------------+---------------+
| Da Vinci | 2 | 121 | 60.5000 |
| Monet | 0 | NULL | NULL |
| Renoir | 1 | 64 | 64.0000 |
| Van Gogh | 2 | 115 | 57.5000 |
+----------+---------------------+-------------+---------------+
Note that
COUNT()
is zero for artists that are not represented, but
SUM()
and
AVG()
are
NULL
. The latter two functions return
NULL
when applied to a set of values with no non-
NULL
values. To display a sum or average value of zero in that case, replace
SUM(
expr
)
and
AVG(
expr
)
with
IFNULL(SUM(
expr
),0)
and
IFNULL(AVG(
expr
),0)
.
14.6. Enumerating a Many-to-Many Relationship
Problem
You want to display a relationship between tables when any row in either table might
be matched by multiple rows in the other.
