9.6. Nulls Revisited

Nulls present a complication with regard to aggregate functions and other queries, because nulls are never equal to, less than, greater than, or not equal to any value. Using aggregates by themselves on columns that contain nulls will ignore the null values. For example, suppose you have the following Table 9-4 called Sal.

Table 9-4. Sal table

Name salary ------------ ----------- Joe 1000.00 Sam 2000.00 Bill 3000.00 Dave NULL   (4 row(s) affected)

Table 9-4 (Sal) has not been created for you. You have to create it to run the queries that follow.

Now consider the following query:

SELECT COUNT(*) AS [count], AVG(salary) AS [average], SUM(salary) AS [sum], MAX(salary)
AS [max], MIN(salary) AS [min]
FROM Sal

which produces the following output:

count       average     sum         max         min
----------- ----------- ----------- ----------- -----------
4           2000.00     6000.00     3000.00     1000.00
 
Warning: Null value is eliminated by an aggregate or other SET operation.
 
(1 row(s) affected)

COUNT (*) counts all the rows. But, the AVERAGE, SUM, MAX, and MIN functions ignore the nulled salary row in computing the aggregates. Counting columns also indicates the presence of nulls. If you count by using the following query:

SELECT COUNT(name) AS [Count of Names]
FROM   Sal

you get:

Count of Names
--------------
4
 
(1 row(s) affected)

If you use the "salary" column, you get:

SELECT COUNT(salary) AS [Count of salary]
FROM   Sal

which produces:

Count of salary
---------------
3
 
Warning: Null value is eliminated by an aggregate or other SET operation.
 
(1 row(s) affected)

This result indicates that you have a null salary. If you want to include nulls in the aggregate and have a rational value to substitute for a value that is not known (a big assumption), you can use the ISNULL function.

The ISNULL function was introduced and discussed in Chapter 5.

ISNULL returns a value if the value is null. ISNULL has the form ISNULL(column name, value if null), which is used in place of the column name. For example, if you type the following:

SELECT name, ISNULL(salary, 0) AS [salary]
FROM   Sal

you get the following output:

name         salary
------------ -----------
Joe          1000.00
Sam          2000.00
Bill         3000.00
Dave         0.00
 
(4 row(s) affected)

If you type the following:

SELECT COUNT(ISNULL(salary,0)) AS [Count of salary]
FROM   Sal

you get:

Count of salary
---------------
4
 
(1 row(s) affected)

The "Count of salary" is now 4 instead of the 3 that you received earlier when the ISNULL function was not used.

If you type the following:

SELECT AVG(ISNULL(salary, 0)) AS [Average of salary]
FROM   Sal

you get:

Average of salary
-----------------
1500.00
 
(1 row(s) affected)

The "Average of salary" is now 1500.00, instead of the 2000.00 that you had received earlier because the zero value for the null was used in the calculation. What seems almost contradictory to these examples is that when grouping is added to the query, nulls in the grouped column are included in the result set. So, if the Sal table had another column like this:

Name         salary      job
------------ ----------- --------------------
Joe          1000.00     Programmer
Sam          2000.00     NULL
Bill         3000.00     Plumber
Dave         NULL        Programmer

And if you ran a query like this:

SELECT SUM(salary) AS [Sum of salary], job
FROM   Sal
GROUP BY job

You would get the following output:

Sum of salary Job
------------- --------------------
2000.00        NULL
3000.00        Plumber
1000.00        Programmer
 
Warning: Null value is eliminated by an aggregate or other SET operation.
 
(3 row(s) affected)

The aggregate will ignore values that are null, but grouping will compute a value for the nulled column value.