NVL and NVL2 Functions in BigQuery and Alternatives

Like many relational databases, GCP BigQuery supports many useful built-in functions. Not all the functions those are available in other relational databases are supported in BigQuery. But, BigQuery provides alternative built-in functions that you can use. In this article, we will check NVL and NVL2 functions in BigQuery database.

NVL and NVL2 Functions in BigQuery

The databases such as Redshift, Teradata, Vertica, Netezza, Oracle, etc. supports standard NULL handling functions such as NVL and NVL2. But as of now, BigQuery supports alternative functions in place of NVL and NVL2.

NVL Function in BigQuery

The NVL function lets you replace null (returned as a blank) with a string in the results of a query. You can use following BigQuery built-in functions as an NVL alternative:

BigQuery IFNULL as an NVL Alternative
COALESCE as an NVL Alternative in BigQuery

Now, let us check these functions with some examples

BigQuery IFNULL as an NVL Alternative

The IFNULL null handling function returns the non-null value if the input value is NULL. Following is the BigQuery IFNULL syntax.

IFNULL(expr, null_result)

If expr is NULL, return null_result. Otherwise, return expr. If expr is not NULL, null_result is not evaluated.

For example, following statement evaluates the NULL values using the IFNULL function.

SELECT IFNULL(NULL, 0) as result;

+--------+
| result |
+--------+
| 0      |
+--------+

COALESCE as an NVL Alternative in BigQuery

Similar to the IFNULL function, COALESCE returns the non-null value if the input value is NULL. Following is the BigQuery COALESCE syntax.

COALESCE(expr[, ...])

You can provide multiple input expressions. The function returns first non-null values. Remaining expressions are not evaluated once the non-null expression is found.

For example, following statement evaluates the NULL values using the COALESCE function.

SELECT COALESCE(NULL, 0) as result;
+--------+
| result |
+--------+
| 0      |
+--------+

NVL2 Function in BigQuery

The NVL2 function lets you determine the value returned by a query based on whether a specified expression is null or not null. There is no NVL2 function in BigQuery. As an alternative you can use following built-in functions.

CASE as a NVL2 Alternative in BigQuery
IF as a NVL2 Alternative in BigQuery

CASE as a NVL2 Alternative in BigQuery

Similar to the IF-ELSE statement, the CASE statement goes through conditions and returns a value when the first condition is met.

For example, following CASE statement works as a NVL2 function.

SELECT
	CASE 
		WHEN col1 IS NULL THEN 1
		ELSE 0
	END AS RESULT
FROM
	(
	SELECT
		NULL AS col1) a ;
+--------+
| result |
+--------+
| 1      |
+--------+

IF as a NVL2 Alternative in BigQuery

Similar to the CASE statement, IF statement goes through conditions and returns a value when the first condition is met.

The syntax is,

IF(expr, true_result, else_result)

If expr is true, returns true_result, else returns else_result. else_result is not evaluated if expr is true. true_result is not evaluated if expr is false or NULL.

For example, following IF statement works as a NVL2 function.

SELECT  
	IF(col1 IS NULL, 1, 0) result
FROM
	(
	SELECT
		NULL AS col1) a ;
+--------+
| result |
+--------+
| 1      |
+--------+