Compatibility: ANSI

As handy as NULLIFZERO is, it only converts a zero to a NULL. Like its predecessor, the newer ANSI standard NULLIF function also can convert a zero to a NULL. However, it can convert anything to a NULL. To use the NULLIF, the SQL must pass the name of the column to compare and the value to compare for equal.

The following is the syntax for using the NULLIF function.

To show the operation of the NULLIF, literal values are shown in the next example:

1 Row Returned

In the above SQL and its output:

• Col1 the value 0 was equal to 0, so a NULL is returned.
• Col2, the 0 is not equal to a 3, so the 0 is returned.
• Col3, the 3 is not equal to 0, so the 3 is returned.
• Col4, the 3 is equal to 3, so a NULL is returned.
• Col5, the NULL is not equal to 0, so the NULL is returned.

Like the NULLIFZERO the NULLIF is great for situations when the SQL is doing division and aggregation. If a need arises to eliminate a zero or any specific value from the aggregation, the NULLIF can convert that value to a NULL. Earlier we discussed aggregates and the fact that they do ignore a NULL value.

An example of using the NULLIF in division and aggregation follows:

Without the NULLIF, we get an error. Why?

The reason is that the grade point value is multiplied by 2 with the result being zero. The problem occurs when the grade point is divided by 0 and the SQL aborts with a 2619 error condition.

In the next example, the NULLIF is added to the denominator of the first column to fix the division error:

2 Rows Returned

There are two items to notice from this answer set. First, the 0 in Grade_pt is converted to a NULL and the 2619 error disappears. Next, the NULL value is multiplied by 2 with a result of NULL. Then, the value stored in Grade_pt is divided by a NULL and of course, the result is a NULL. Anytime a value is divided by itself times 2, .5 is the result. Therefore, all valid data values are combined in the output. The only other row(s) are those with a zero in the grade point column.

The second thing about the output is the heading. Notice that both headings contain the word CASE. Later in this chapter CASE is shown as a technique to test values. Now it is seen that the NULLIF and COALESCE both use CASE for their tests. Now that this is known, it also means that using alias or TITLE is probably a good idea to dress up the output.

The good news is that the NULLIF allows the SQL to complete and show values for all rows that do not contain a zero in the column used for division. These zero values probably need to be fixed in the long term.

For the second column in this SELECT, whenever Grade_pt contains a zero, it is converted to a NULL. The resulting NULL is passed to the AVG function and promptly ignored. Therefore, the resulting average will be a higher number than if the zero is allowed to become part of the overall average.

0