Simplify Data Handling with BigQuery IFNULL and NULLIF

With Google’s Enterprise Data Warehouse Solution, BigQuery, Google makes it easy to access tons of data with analysis, optimized results, and better performance and availability. Google BigQuery is a Serverless Database. There is no infrastructure to administer. 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.

Table of Contents

• An Overview of BigQuery
  • What are NULL Values in SQL?
  • Why do we need NULL Functions?
• Conditional Expressions in BigQuery SQL
  • BigQuery IF() Expression
    • Troubleshooting Common Errors: Could not Cast Literal to Type DATE
  • BigQuery IFNULL() Function
  • BigQuery NULLIF() Function
  • Difference between BigQuery IFNULL() and BigQuery NULLIF() Functions
    • Case 1: BigQuery IFNULL() and BigQuery NULLIF() Function yielding the Same Result
    • Case 2: BigQuery IFNULL() and BigQuery NULLIF() Function yielding Different Result
• Conclusion

An Overview of BigQuery

BigQuery is a fully managed Enterprise Data Warehouse that comes with built-in tools like Machine Learning, Geospatial Analysis, and Business Intelligence to help you manage and analyse your data. The Serverless Architecture of BigQuery allows you to use SQL queries to solve your Organization’s most pressing problems while requiring no infrastructure management. The scalable, distributed Analytical Engine in BigQuery allows you to query terabytes of data in seconds and petabytes of data in minutes.

By separating the Computational Engine that analyses your data from your storage options, BigQuery enhances flexibility. You can use BigQuery to store and analyse your data, or you can use it to review your data wherever it is stored. Federated Queries allow you to read data from external sources, whereas Data Streaming allows you to update data in real-time. BigQuery ML and BI Engine are powerful tools for analysing and understanding your data.

The Google Cloud Console Interface and the BigQuery Command-Line Tool are two BigQuery Interfaces. To change and manage data, Developers and Data Scientists can use Client Libraries written in familiar programming languages like Python, Java, JavaScript, and Go, as well as BigQuery’s REST API and RPC API. Existing applications, including third-party tools and utilities, can be interacted with using ODBC and JDBC drivers.

The BigQuery ML Documentation assists you in discovering, implementing, and managing data tools to support crucial business choices as a Data Analyst, Data Engineer, Data Warehouse Administrator, or Data Scientist.

What are NULL Values in SQL?

When we have missing data or the required data is not available, we use NULL values as placeholders in the Database.

A NULL value is a flexible data type that can be used in any column of any Data Type, including text, int, blob, and CLOB Data Types. NULL values are handy when cleansing data and conducting exploratory Data Analysis. 

NULL values also assist in removing ambiguity from data. NULL values are useful for maintaining a consistent Data Type across the column.

Consider this scenario: If a user accidentally enters their date of birth in the mobile number column, misunderstanding may develop when establishing contact.

To avoid this, we perform a data check before insertion and replace any data that is not of date datatype with a NULL value.

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 Data with its strong integration with 100+ sources (including 30+ free sources) allows you to not only export data from your desired data sources & load it to the destination of your choice, but also transform & enrich your data to make it analysis-ready so that you can focus on your key business needs and perform insightful analysis using BI tools.

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.