Januay 2005 Technical Tip SQL Queries and Nulls: The COALESCE Function

Special care must be taken when querying a table with fields which can contain nulls, particularly if these fields are used in an arithmetic expression. Most expressions involving fields whose values are null will evaluate to null.

If a field may contain nulls, you will likely want to make use of the coalesce function. Coalesce returns the first argument that is not null. The arguments are evaluated in the order in which they are specified, and the result of the function is the first argument that is not null. The results can be null only if all the arguments can be null, and the result is null only if all the arguments are null. The following example should serve to illustrate and is given without additional comment:

mysql> create table nulltest
    -> (fld1 char(1), fld2 smallint, fld3 smallint);

mysql> insert into nulltest values ('a', 1, 1);

mysql> insert into nulltest values ('b', 1, null);

mysql> insert into nulltest values ('c', null, 1);

mysql> insert into nulltest values ('d', 1, 1);

mysql> insert into nulltest values ('e', null, null);

mysql> select * from nulltest;
+------+------+------+
| fld1 | fld2 | fld3 |
+------+------+------+
| a    |    1 |    1 |
| b    |    1 | NULL |
| c    | NULL |    1 |
| d    |    1 |    1 |
| e    | NULL | NULL |
+------+------+------+

mysql> select fld1, (fld2 + fld3) as sum from nulltest;
+------+------+
| fld1 | sum  |
+------+------+
| a    |    2 |
| b    | NULL |
| c    | NULL |
| d    |    2 |
| e    | NULL |
+------+------+

mysql> select fld1, (coalesce(fld2,0) + coalesce(fld3,0))
    -> as sum from nulltest;
+------+------+
| fld1 | sum  |
+------+------+
| a    |    2 |
| b    |    1 |
| c    |    1 |
| d    |    2 |
| e    |    0 |
+------+------+

I used MySQL for the examples in this article. For information on getting started with MySQL, see our January 2004 article.

Want to know more about SQL? Give us a call! You can count on Caliber Data Training for top quality mainframe and/or internet programming education.


Go to the articles index. Written by Bill Qualls. Copyright 2005 by Caliber Data Training 800.938.1222