Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

NULLs and Groups

Because NULLs represent “the great unknown,” there is no way to know whether one NULL is equal to any other NULL. Each unknown value may or may not be different from another.

However, if the grouping column contains more than one NULL, all of them are put into a single group.

COUNT(*) and COUNT()

The type column in the titles table contains a NULL. Here's an example that groups the rows by the type column and counts the number of rows in each group:

SQL

select type, count(*)
from titles
group by type
type            count(*)
============ ===========
popular_comp           3
business               4
psychology             5
mod_cook               2
trad_cook              3
(NULL)                 1
[6 rows]

Notice that there is one row that has a NULL type. If you used count(type) instead of count(*) in the query, ...