Database Reference
In-Depth Information
| Swan Goose | Vulnerable |
| Lesser White-fronted Goose | Vulnerable |
| Hawaiian Goose | Vulnerable |
| Red-breasted Goose | Endangered |
| Blue-winged Goose | Vulnerable |
+----------------------------+--------------------+
The
ON
operator specifies the
conservation_status_id
columns from each table
as the common item on which to join the tables. MySQL knows the proper table in which
to find the
conservation_category
and
common_name
columns, and pulls the
rows that match.
That works fine, but it's a lot to type. Let's modify this statement to use the
USING
oper-
ator, specifing
conservation_status_id
just once to make the join. MySQL will
understand what to do. Here's that same SQL statement, but with the
USING
operator:
SELECT
common_name
,
conservation_state
FROM
birds
JOIN
conservation_status
USING
(
conservation_status_id
)
WHERE
conservation_category
=
'Threatened'
AND
common_name
LIKE
'%Goose%'
;
Now let's modify the SQL statement to include the bird family. To do that, we'll have to
add another table, the
bird_families
. Let's also include Ducks in the list. Try execut-
ing the following:
SELECT common_name AS 'Bird',
bird_families.scientific_name AS 'Family', conservation_state AS
'Status'
FROM birds
JOIN conservation_status USING(conservation_status_id)
JOIN bird_families USING(family_id)
WHERE conservation_category = 'Threatened'
AND common_name REGEXP 'Goose|Duck'
ORDER BY Status, Bird;
+----------------------------+----------+-----------------------+
| Bird | Family | Status |
+----------------------------+----------+-----------------------+
| Laysan Duck | Anatidae | Critically Endangered |
| Pink-headed Duck | Anatidae | Critically Endangered |
| Blue Duck | Anatidae | Endangered |
| Hawaiian Duck | Anatidae | Endangered |
| Meller's Duck | Anatidae | Endangered |
| Red-breasted Goose | Anatidae | Endangered |
