cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
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:

Community_Admin_0-1634390659280.png
Answer

 

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:

Community_Admin_1-1634390677037.png

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):

Community_Admin_2-1634390677023.png

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).

Community_Admin_3-1634390677018.png

Using SQL Case:

Community_Admin_4-1634390677059.png

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

Community_Admin_5-1634390677021.png

Both queries bear the same correct result:

Community_Admin_6-1634390677070.png

 

Version history
Last update:
‎10-16-2021 06:25 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request