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:
- 5 * null = null
- null – null = null
- 5 + null = null
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:
Updated 03-02-2023
