Part 1: Understanding The NVL/COALESCE Expression

[intapiuser]

What are Nulls?

The null value can be confusing--does it represent a zero, an empty space, a blank placeholder? In SQL, null represents an “unknown” type or the absence of a value, and the use case and queries involving nulls are often unique. 

For example, when there are calculations or expressions involving timestamps, strings, integers, or even booleans with a null value, the overall expression is also null. The logic is valid since if one aspect of the expression is unknown, then the overall expression must also be unknown. 

Now, let’s say we have columns with both null and non-null values. One way to check if there are null values in the columns is to use the “Is Null” function. If we wanted to return all the non-null values in a column, then we would simply use “Is Not Null”:

If the “Is Null” or “If Null” (“IsNull” equivalent in MySQL) functions filters for either null or non-null values, then what is the use case for NVL/Coalesce expressions? Well, there are a few key differences between “Is Null” and the Coalesce/NVL expression.

Comparison of "Is NULL" Function vs. NVL/Coalesce Expression:

1. Is NULL function is typically computationally faster than the Coalesce expression (although the difference is pretty negilgible)
2. Is NULL function is evaluated once, while the input values for the Coalesce expression can be evaluated multiple times.
3. Is NULL function uses the data type of the first parameter, while the Coalesce expression abides by the Case expression rules and returns the data type of value with the highest precedence.
4. Is NULL function returns a value for null values that is “Not Nullable,” while the Coalesce expression with non-null parameters is actually NULL.

NVL/Coalesce Expression

Now, that we have discussed the differences between “Is Null” and “Coalesce,” let’s take a deeper look at the NVL/Coalesce expression. 

Coalesce () or NVL() are synonymous expressions, and they are used to return the first non-null expression in a list of at least two inputs/arguments. In the case of where all the inputs/arguments are null, the Coalesce() expression will return “null.”

An example of returning the first non-null argument:

An example of when all the arguments are null:

Another way to think about the Coalesce expression is as an abbreviated version of the Case statement. The following coalesce expression and case statement achieve the same results.

Coalesce Expression:

Case Statement:

Tip: One of the advantages of the Sisense for Cloud Data Teams SQL editor is that it provides hints as to the purpose of most SQL expressions as well as their format.

Explanation:

Format:

Interested in the applications of the NVL/Coalesce Expressions? Click here!