Compatibility: ANSI

As helpful as the ZEROIFNULL is, it only converts a NULL into a zero. The newer ANSI standard COALESCE can also convert a NULL to a zero. However, it can convert a NULL value to any data value as well. The COALESCE searches a value list, ranging from one to many values, and returns the first Non-NULL value it finds. At the same time, it returns a NULL if all values in the list are NULL.

To use the COALESCE, the SQL must pass the name of a column to the function. The data in the column is then compared for a NULL. Although one column name is all that is required, normally more than one column is normally passed to it. Additionally, a literal value, which is never NULL, can be returned to provide a default value if all of the previous column values are NULL.

Whereas NULLIF works with two parameters and compares a column with a value passed to it, the COALESCE can examine many values and continues to check each data value until it finds one that is not a NULL and that value is returned.

However, if all the values passed to the function are NULL, it has no choice and can only return a NULL. The values passed to the COALESCE function can be via column names or a literal value.

The syntax for the COALESCE follows:

In the above syntax the <column-list> is a list of columns. It is written as a series of column names separated by commas.

1 Row ReturnedCOALESCE follows

In the above SQL and its output:

  • Col1 the first value is a NULL, so the 0 is checked. Since it is not a NULL, 0 is returned.
  • Col2, the first value is a NULL, the second and third values are also NULL. Since all values are NULL, there is no alternative, a NULL is returned.
  • Col3, the 3 is not a NULL, so the 3 is returned. It will never make sense to use a single column because the result is exactly the same as selecting the column. Always use a minimum of two values with the COALESCE.
  • Col4, is an interesting case. Since both have a value, the first value ‘A’ is returned and the 3 is never displayed. This means that when coding a COALESCE, never place the literal first, it should always be last if used. Otherwise, the data in subsequent column names will never be checked. The columns should always precede a literal.

Like the ZEROIFNULL, one of the best uses for the COALESCE is in a mathematics formula. In an earlier chapter it was seen that anytime a NULL is used in math, the answer is a NULL. Therefore, the COALESCE can convert a NULL to a zero so that an answer is returned.

The next SELECT displays the student's last name and GPA. However, if there is no GPA, it includes the phrase "Missing GPA" in the output using the COALESCE to search Grade points for a NULL:

10 Rows Returned

COALESCE to search Grade points for a NULL

COALESCE to search Grade points for a NULL

The next example uses basically the same SELECT as above, but adds a second column to the column list of the COALESCE.

3 Rows Returned

second column to the column list of the COALESCE

In this example, 2 columns and a literal are used in the COALESCE. Since Johnson had a first name, the literal is not displayed. It is there as an insurance policy in case both columns contained NULL. COALESCE is a great tool any time there is a need to display a single column and insure that missing data is replaced with a substitute value.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics