cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member
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:

Rate this article:
Version history
Last update:
‎03-02-2023 08:32 AM
Updated by:
Contributors