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. Learn more about 3VL.

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.

Replace 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 two arguments.

The basic format looks like this:

COALESCE("column_name", replace_value, ...)

Parameters

column_name: The column containing the NULL values you want to replace with the specified replace_value.

replace_value: The column or values you want to replace your NULL values with. You can have more than one replace_value.

To replace NULL values with the COALESCE() in a Visual SQL step:

1. Either add a “Formula column” step or use an “Apply formula” step to edit the existing column.

2. Select Custom as the formula type.

3. Type the following formula to replace NULL values in your column with 0, replacing column_name with the name of your column:

   COALESCE("column_name", 0)

   

4. If you add a “Formula column” step, you can use a “Hide column” step to hide your original column from your final result set.

Replace NULL and blank values with a CASE statement

If 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 COALESCE().

Selecting Custom as the formula type for either a “Formula column” or “Apply formula” step, 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

Replace 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 the third row, 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 the third row.

COALESCE("Column A", "Column B", ...)