Simplify Data Handling with BigQuery IFNULL and NULLIF

With BigQuery, Google makes it easy to access tons of data with analysis, optimized results, and better performance and availability. It is a serverless database, and there is no infrastructure to administer it. Therefore, there is no need for a Database Administrator.

Using basic SQL like BigQuery IFNULL() and BigQuery NULLIF() Functions, your company can focus on data analysis to find important insights. In this article, you will get a glance at Google BigQuery and BigQuery’s Conditional Expressions and Functions like BigQuery IF(), BigQuery IFNULL(), and BigQuery NULLIF() functions.

You will gain in-depth knowledge of Google BigQuery Case and Conditional Expressions with examples to help clarify. Read along to learn about BigQuery Case and Conditional Expressions.

Why Do We Need NULL Functions?

To conduct operations on the NULL values stored in our BigQuery, NULL functions are required. On NULL values, we can conduct functions that explicitly recognise if a value is NULL or not.

With this ability to recognise NULL data, one can perform operations on them similar to the aggregate methods in SQL. The following are some of the functions:

• ISNULL() is a function that allows us to replace NULL values with the value we want.
• IFNULL() allows to return the first value if the value is NULL; otherwise, the second value is returned.
• COALESCE() is a function that returns the first non-null value in a set of arguments.
• NVL() can be used to replace a NULL value with a value specified by the user.

Conditional Expressions in BigQuery SQL

BigQuery IF() Expression

BigQuery IF() Syntax

IF(expr, true_result, else_result)

BigQuery IF() Description

If expr is true, true_result is returned; otherwise, else_result is returned. If expr is true, else_result is not evaluated. If expr is false or NULL, true_result is not evaluated.

The expression expr must be a boolean expression. The supertypes true_result and else_result must be coercible.

Return Data Type

Supertype of true_result and else_result.

Example

Problem: In BigQuery, let’s suppose we have a Sales table with the item num column containing the values 1, -1, and 0. You want to count how many instances of each value you have.

Solution:

There are three ways with which you can solve this problem using BigQuery IF Expressions

• Solution 1

SELECT 
  SUM(IF(item_num > 0, 1, 0)) AS buysplus,
  SUM(IF(item_num < 0, 1, 0)) AS buysminus,
  SUM(IF(item_num = 0, 1, 0)) AS buyszero
FROM MyShop.Sales

• Solution 2

SELECT 
  SUM(item_num > 0) AS buysplus,
  SUM(item_num < 0) AS buysminus,
  SUM(item_num = 0) AS buyszero
FROM MyShop.Sales

This will give you result like the below:

buysplus    buysminus    buyszero     
4           2            3

• Solution 3

Another option would be a transposed version of it

SELECT 
  item_num AS buys,
  COUNT(1) AS volume
FROM MyShop.Sales
GROUP BY 1  

This produces the result as below

buys      volume  
 0          3    
 1          4    
-1          2   

Troubleshooting Common Errors: Could not Cast Literal to Type DATE

When the two separate results true_result and else_result are of different data types, such as when one of the results is a date and the other is a data type that cannot be converted to a date, an error occurs. If this happens, double-check your logic and column types, or use the CAST function.

For argument types, there is no matching signature for function IF… Signatures that are supported are: IF, IF, IF, IF, IF (BOOL, ANY, ANY).

When the expr does not evaluate to TRUE or FALSE, this error occurs (i.e. expr is not a boolean expression).

BigQuery IFNULL() Function

BigQuery IFNULL() Syntax

IFNULL(expr, null_result)

BigQuery IFNULL() Description

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

expr and null_result can be any type and must be implicitly coercible to a common supertype. Synonym for COALESCE(expr, null_result).

BigQuery IFNULL() Return Data Type

Supertype of expr or null_result.

BigQuery IFNULL() Example

Here we’ve taken an example of a demo database called dataflair. 

Let’s look at each employee’s experience in DataFlair and change NULL with 0 years of experience.

Syntax

SELECT col1,col2, IFNULL(col3, value_to_be_replaced) FROM tableName;

Query

SELECT emp_id,name, IFNULL(experience, 0) FROM dataflair;

Output

All values corresponding to NULL are immediately replaced by 0 in this case.

Some other simple examples of using the BigQuery IFNULL() function are:

SELECT IFNULL(NULL, 0) as result

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

Or

SELECT IFNULL(10, 0) as result

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

BigQuery NULLIF() Function

BigQuery NULLIF() Syntax

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.

BigQuery NULLIF() Return Data Type

Supertype of expr and expr_to_match.

BigQuery NULLIF() Example

SELECT NULLIF(0, 0) as result

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

OR

SELECT NULLIF(10, 0) as result

+--------+
| 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 1: BigQuery IFNULL() and BigQuery NULLIF() Function yielding the Same Result

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

Result

NULLIF(56, 45)         IFNULL(56, 45)
--------------------   ---------------------
56             	       56

Explanation

The BigQuery NULLIF() function returns its first argument if both arguments are different, and NULL if both arguments were the same. 

The BigQuery IFNULL() function returns the first non NULL argument.

Case 2: 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.

Conclusion

The article summarizes some essential concepts about Google BigQuery and particularly focuses on BigQuery SQL Conditional Expressions and Functions like BigQuery IFNULL() and BigQuery NULLIF() functions. We laid a foundation for these functions by including syntax, description and use examples to help you understand more intricate details on these functions. We also helped distinguish BigQuery IFNULL() and BigQuery NULLIF() functions with two cases where these functions can produce the same result and the other, where these functions can produce different results.

Being a Data Warehouse user, your Organisation might be dealing with megabytes or petabytes of data, and when it comes to analysis, these processes can get tough. Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations such as Google BigQuery, with a few clicks.

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.

Visit our Website to Explore Hevo

Sign Upfor a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing price, which will assist you in selecting the best plan for your requirements.

Share your experience of understanding the BigQuery Case and Conditional Expressions in the comment section below! We would love to hear your thoughts.