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

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:

Version history
Last update:
‎03-02-2023 08:32 AM
Updated by:
Community Toolbox

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

Developers Group:

Product Feedback Forum:

Need additional support?:

Submit a Support Request

The Legal Stuff

Have a question about the Sisense Community?

Email [email protected]

Share this page: