Aggregate and Numeric Functions - Learning MySQL and MariaDB - page 274

That's a wildcard to tell MySQL that we want to count all of the rows found. Because we
don't have a WHERE clause, all of the rows will be selected.
Many of the bird species lack common names. So the common_name column in birds
is blank for these species. COUNT() has a special convention: if you pass a column name
instead of an asterisk as its argument, it counts only the columns that are notNULL. Let's
change that data and then see how it might look. Enter these two SQL statements:
UPDATE birds
SET common_name = NULL
WHERE common_name = '';
SELECT COUNT(common_name)
FROM birds;
+--------------------+
| COUNT(common_name) |
+--------------------+
| 9553 |
+--------------------+
That's the number of birds with a common name in the table. We could have gotten the
same results with a WHERE clause, and without having modified the data as we did. This
lets us select only rows where the common_name does not equal '' . We've changed
those values to NULL, though, so let's use the WHERE clause to see how that would look
based on NULL values. Enter the following:
SELECT COUNT(*) FROM birds
WHERE common_name IS NULL;
+----------+
| COUNT(*) |
+----------+
| 19338 |
+----------+
This gave us a different number. That's because we're counting the rows where the com-
mon_name is NULL — we used theoperator IS NULL . Before, we counted the rows
where the common_name was not NULL. We can count those with the WHERE clause
like so:
SELECT COUNT(*) FROM birds
WHERE common_name IS NOT NULL;
+----------+