How Aggregates Evaluate Null
Because nulls have no value, the aggregate functions SUM()
, and MAX()
ignore them. Using best practices, you should set a default value, such as 0, to avoid the problem of nulls in your calculations. However, keep in mind that aggregates will evaluate 0 and that might not be what you want. SQL Server lends a bit of help in this regard. When eliminating null values, SQL Server can warn you if you simply turn on the following option:
SET ANSI_WARNINGS ON
However, the SET ANSI_WARNINGS ON
setting also affects how SQL Server handles division by zero, arithmetic overflow, and character/binary data truncation, so be sure you learn its full potential. The important distinction is that adding null to any value returns null and the aggregate functions circumvent that standard by eliminating nulls.
If you want to evaluate all values, you must account for those values at the table level by forcing a default value. If that's not possible, use COALESCE() to return a value other than null in the expression. For example, the first AVG() function shown in Figure 7 doesn't evaluate null values. However, the second one doestechnically, the second one is evaluating 0's, not nulls. Neither statement is better than the other. Whether a function ignores nulls or not depends upon your objective.
|Figure 7. First AVG() Function Doesn't Evaluate Null Values: Control whether aggregates evaluate null values.|
The COUNT() function is a bit different from the other four aggregates because it has two forms:
- COUNT(column): Returns the count of rows in a table, less the null values in column
- COUNT(*): Returns the count of rows in a table
As you can see in Figure 8, the following forms return the same results:
SELECT COUNT(*) Count(column) AS NullCount
SELECT COUNT(*) AS NullCount
WHERE column IS NULL
However, if you specify the column as follows, the statement returns 0:
|Figure 8. COUNT(column) Eliminates All Nulls Before Evaluating: Depending on the form you use, COUNT() will ignore or evaluate nulls.|
SELECT COUNT(column) AS NullCount
WHERE column IS NULL
That's because COUNT(column)
eliminates all the nulls before it evaluates anything. Consequently, there are no null values to count.
If You Allow Them, You Must Handle Them
If even one column in your database is nullable, you have to consider how a null value will affect your data. Knowing how SQL Server evaluates nulls and how to handle them will prevent those nulls from working their way into your data and returning errors.