Nulls and Aggregate Functions
If an aggregate function against a column that contains nulls is executed, the function ignores the nulls. This prevents unknown or inapplicable values from affecting the result of the aggregate. For example, if the aggregate function, AVG(), is applied to a column that holds the ages of your employees, 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, except COUNT(), return null for an aggregate that has an argument that evaluates to an empty set. (COUNT() returns 0 for an empty set.) In the following example, the select returns null, because there are no rows in the table named test.
CREATE TABLE testnull (col1 INTEGER NOT NULL);
SELECT MAX(col1) AS x FROM testnull;
In the above example, use the IFNULL function to return a zero (0) instead of a null:
SELECT IFNULL(MAX(coll),0) AS x FROM testnull;
Note: When specifying a column that contains nulls as a grouping column (that is, in the GROUP BY clause) for an aggregate function, nulls in the column are treated as equal for the purposes of grouping as if IS NOT DISTINCT FROM were used. In all other situations, nulls do not compare as equal to other nulls.
Last modified date: 08/29/2024