Database Reference
In-Depth Information
| White-headed Duck | Anatidae | Endangered |
| White-winged Duck | Anatidae | Endangered |
| Blue-winged Goose | Anatidae | Vulnerable |
| Hawaiian Goose | Anatidae | Vulnerable |
| Lesser White-fronted Goose | Anatidae | Vulnerable |
| Long-tailed Duck | Anatidae | Vulnerable |
| Philippine Duck | Anatidae | Vulnerable |
| Swan Goose | Anatidae | Vulnerable |
| West Indian Whistling-Duck | Anatidae | Vulnerable |
| White-headed Steamer-Duck | Anatidae | Vulnerable |
+----------------------------+----------+-----------------------+
We gave two
JOIN
clauses in this SQL statement. It doesn't usually matter which table is
listed where. For instance, although
bird_families
is listed just after the join for the
conservation_status
table, MySQL determined that
bird_families
is to be
joined to the
birds
table. Without using
JOIN
, we would have to be more emphatic in
specifying the join points, and we would have to list them inthe
WHERE
clause. It would
have to be entered like this:
SELECT
common_name
AS
'Bird'
,
bird_families
.
scientific_name
AS
'Family'
,
conservation_state
AS
'Status'
FROM
birds
,
conservation_status
,
bird_families
WHERE
birds
.
conservation_status_id
=
conservation_status
.
conservation_status_id
AND
birds
.
family_id
=
bird_families
.
family_id
AND
conservation_category
=
'Threatened'
AND
common_name
REGEXP
'Goose|Duck'
ORDER BY
Status
,
Bird
;
That's a very cluttered
WHERE
clause, making it difficult to see clearly the conditions by
which we're selecting data from the tables. Using
JOIN
clauses is much tidier.
Incidentally, the SQL statement with two
JOIN
clauses used a regular expression —the
REGEXP
operator in the
WHERE
clause — to specify that the clause find either
Goose
or
Duck
. We also addedan
ORDER BY
clause to order first by
Status
, then by
Bird
name.
In this example, though, there's little point in listing the bird family name, because the
birds are all of the same family. Plus, there may be similar birds that we might like to have
in the list, but that don't have the words
Goose
or
Duck
in their name. So let's change
that in the SQL statement. Let's also order the results differently and list birds from the
least endangered to the most endangered. Enter the following:
