Include NULL values when applying functions in Visual SQL
If you have NULL values in your column, you won’t be able to perform functions on it or combine it with another column. NULL results will be ignored and excluded from your results. This is a result of what’s called three-value logic (3VL) in SQL. Please refer to the following page for a detailed explanation.
To get around this, you’d convert the NULL values to another value (such as 0) or to values from other columns using SQLite’s coalesce function or CASE statement.
Replacing NULL values with zero using coalesce
If you’d like to replace all NULL values in a column with another value, the
coalesce function easily lets you do this. This function accepts two or more arguments and returns the first non-NULL argument; the function must have at least 2 arguments.
The basic format looks like this:
coalesce("column", replace_value, ...)
column: The column containing the NULL values you want to replace with the specified
replace_value: The column or values you want to replace your NULL values with. You can have more than one
To replace NULL values with the
coalesce() in the Pipeline, either add a Formula Column or use Apply Formula to edit the existing column, select Custom as the formula type, then type the following formula to replace NULL values in your column with
Column Name with the name of your column:
coalesce("Column Name", 0)
If you add a Formula Column, you’ll likely want to hide your original column from your final result set.
Tip! To minimize the number of Pipeline Actions, you can combine multiple functions in a single formula, as shown in the screenshot above.
Replacing NULL and blank values with a CASE statement
coalesce() doesn’t fully replace all empty values in your query results, your result set may contain both NULL and blank values. To account for both cases, use a CASE statement instead of
Selecting Custom as the formula type for either a Formula Column or Apply Formula Action, use the following CASE statement, replacing
Column Name with your column’s name:
case when "Column Name" is null then 0 when "Column Name" = '' then 0 else "Column Name" end
Replacing NULL values using another column
You can also use
coalesce() to replace NULL values found in one column with another column’s values. This would be carried out at the row level. For example, if column A’s value is NULL in row 3 and you want to replace that NULL value with a value from column B, the function would replace the NULL from column A with the value from column B in row 3.
coalesce("Column A", "Column B", ...)
Check out our step-by-step video showing how to do this: