Selecting Data - Learning MySQL and MariaDB - page 143

There is one odd thing about the results here: there aren't any common names for the birds
returned. That's not a mistake. About 10,000 birds in the birds table are true species of
birds, and about 20,000 are subspecies. Many subspecies don't have a unique common
name. With about 30,000 species and subspecies of birds, with all of the minor nuances
between the subspecies bird families, there just aren't common names for all of them.
Each bird has a scientific name assigned by ornithologists, but everyday people who use
the common names for birds don't see the subtle distinctions that ornithologists see. This
is why the scientific_name column is necessary and why the common_name
column cannot be a key column in the table.
Let's execute that SQL statement again, but add another factor to the WHERE clause to
show only birds with a value for the common_name column:
SELECT common_name, scientific_name, family_id
FROM birds
WHERE family_id IN(103, 160, 162, 164)
AND common_name != ''
ORDER BY common_name
LIMIT 3;
+-----------------------+-----------------------+-----------+
| common_name | scientific_name | family_id |
+-----------------------+-----------------------+-----------+
| African Oystercatcher | Haematopus moquini | 160 |
| African Snipe | Gallinago nigripennis | 164 |
| Amami Woodcock | Scolopax mira | 164 |
+-----------------------+-----------------------+-----------+
In the WHERE clause, weadded the AND logical operator to specify a second filter. For a
row to match the WHERE clause, the family_id must be one in the list given and the
common_name must not be equal to a blank value.
Nonprogrammers will have to learn a few conventions to use large WHERE clauses. We've
seen that an equals sign says, “The column must contain this value,” but the != construct
says, “The column must not contain this value.” And in our statement, we used '' to refer
to an empty string. So we'll get the rows where the common name exists.
In this case, we couldn't ask for non-NULL columns. We could have set up the table so
that birds without common names had NULL in the common_name column, but we
chose to instead use empty strings. That's totally different in meaning: NULL means there
is no value, whereas the empty string is still a string even if there are no characters in it.
We could have used NULL, but having chosen the empty string, we must use the right
value in our WHERE clause.