GCP-Bigquery- IFNULL() 和 NULLIF() 的区别和例子

NULLIF(expr, expr_to_match)

BigQuery NULLIF() Description

NULL is returned if expr = expr_to_match is true, otherwise, expr is returned. expr and expr_to_match must be implicitly coercible to a common supertype and comparable.

如果2个结果相同，返回null值，否则返回expr值。

BigQuery NULLIF() Return Data Type

Supertype of expr and expr_to_match.

BigQuery NULLIF() Example

SELECT NULLIF(0, 0) as result。 0 和0 相同，所以返回NULL值。

+--------+
| result |
+--------+
| NULL   |
+--------+

OR

SELECT NULLIF(10, 0) as result。10 和0不同，所以返回10.

+--------+
| result |
+--------+
| 10     |

Difference between BigQuery IFNULL() and BigQuery NULLIF() Functions

The BigQuery IFNULL() and BigQuery NULLIF() functions work exactly opposite to each other:

• BigQuery IFNULL() allows you to replace NULL values with another value. You can think of it as “if NULL, then …”.
• BigQuery NULLIF() allows you to treat certain values as NULL. You can think of it as “return NULL if …”.

Sometimes BigQuery IFNULL() and BigQuery NULLIF() functions can return the same output or different output, and this can be explained using the examples mentioned below:

Case : BigQuery IFNULL() and BigQuery NULLIF() Function yielding Different Result

SELECT
    NULLIF(NULL, 45),
    IFNULL(NULL, 45);

Result

NULLIF(NULL, 45)           IFNULL(NULL, 45)
------------------------   ------------------------
NULL             	   45     

Explanation

The BigQuery NULLIF() function, in this case, returns its first argument that is NULL, since both arguments are different.

The BigQuery IFNULL() function returns the first non NULL argument, which is 45.

Ref：BigQuery IFNULL and NULLIF Commands: Explained In 4 Simple Steps - Learn | Hevo (hevodata.com)