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)