Database Reference
In-Depth Information
| Anatidae | 248 |
| Charadriidae | 119 |
| Laridae | 168 |
| Sternidae | 39 |
| Caprimulgidae | 223 |
| Sittidae | 92 |
| ... | |
+--------------------+-------------------+
225 rows in set (0.17 sec)
That's nicer looking, and the results are more interesting. I've shortened the results again,
but notice that we now have only 225 rows. That's because we have some rows in the
birds
table in which the
family_id
is NULL. When using a database, watch for dis-
crepancies like this; don't ignore them just because you weren't looking for problems.
They can help you catch problems you overlooked.
Let's modify the
SELECT
statement to show the number of rows in
birds
that do not
have matching values in
bird_families
. We'll do thiswith a
LEFT JOIN
(covered
in
Joining Tables
, which included examples, but let's apply that concept again here):
SELECT bird_families.scientific_name AS 'Bird Family',
COUNT(*) AS 'Number of Species'
FROM birds LEFT JOIN bird_families USING(family_id)
GROUP BY birds.family_id;
+--------------------+-------------------+
| Bird Family | Number of Species |
+--------------------+-------------------+
| NULL | 4 |
| NULL | 1 |
| Gaviidae | 6 |
| Anatidae | 248 |
| Charadriidae | 119 |
| Laridae | 168 |
| Sternidae | 39 |
| Caprimulgidae | 223 |
| Sittidae | 92 |
| ... | |
+--------------------+-------------------+
225 rows in set (0.17 sec)
Some of these rows may have a
family_id
of NULL, and one may have a
fam-
ily_id
not contained in
bird_families
. To resolve this problem, we would run a
