If you execute an aggregate function against a column that contains nulls, the function ignores the nulls. This prevents unknown or inapplicable values from affecting the result of the aggregate. For example, if you apply the aggregate function avg() to a column that holds the ages of your employees, you want to be sure that any ages that have not been entered in the table are not treated as zeros by the function. This distorts the true average age. If a null is assigned to any missing ages, the aggregate returns a correct result: the average of all known employee ages.

Aggregate functions, with the exception of count(), return null for an aggregate over an empty set, even when the aggregate includes columns which are not nullable (count() returns 0).

In the following example, the retrieve returns null, because there are no rows in "test."

In the above example, you can use the ifnull function to return a zero (0) instead of a null:

For more information, see Ifnull on page 58.