Using SQL Coalesce To Handle Null Values

Question:

SQL NULL is not technically a value, but rather a state. Fields are not equal or not equal to null, they are or are not null.  As such, null fields can get a little tricky when attempting different kinds of transformations.

For example, mathematical equations across multiple fields where any of the fields are null will result in a null value:

Consider a salary calculation where an employee as Base but no Commission:

Solution:

In this case the Total for Regina should equal 49000, not null. To transform the null into a not null value like 0 we could use SQL Case:

More efficiently we can use Coalesce which checks for null values in the specified expression (i.e., Base or Commission) and does the translation to the specified value (i.e., 0):

Coalesce is also useful for finding the first populated (i.e., non-null) value across multiple fields in the same record (e.g., select the first non-null phone number from home, mobile, office fields).

Using SQL Case:

The same logic using Coalesce results in more efficient and decipherable query:

Both queries bear the same correct result: