How to use alternative column value instead of null in BigQuery

How can I use another value when selected column value was NULL in BigQuery?

BigQuery(BQ) is very useful for data analysis or processing.

It is good at handling huge data. It returns summary result in short time.

But data is not always clean.

Sometimes it contains NULL in specific column.

In that case we should use another value.

Then how can we use alternative value ?

So today I introduce about "How to use alternative column value instead of null in BigQuery".

TOC

Prepare data

First, we have to prepare data.

Load this CSV file into table null_sample.

col1,col2,col3
a,b,c
,b,c
,,c
a,b,
a,,

Then we can see table like below.

Rowcol1col2col3
1nullbc
2nullnullc
3abc
4abnull
5anullnull
Table has null cells.

How to use alternative column value instead of null in BigQuery

There are 2 methods to use another value instaed of NULL.

Using alternative value
  • IFNULL
  • COALESCE

I will introduce each of them.

IFNULL

First you may imagine IF function.

There is similar function IFNULL that is for NULL values.

Conditional expressions in Standard SQL  |  BigQuery  |  Google Cloud

In order to use IFNULL(), you can set column that you want to check NULL as first parameter.
And set alternative value as second parameter.

SELECT 
col1,
col2,
col3,
IFNULL(col1,col2) as f
FROM test.null_sample

Result is like below.

Rowcol1col2col3f
1nullbcb
2nullnullcnull
3abca
4abnulla
5anullnulla
IFNULL

As you see, in row 1 and 2, function returned col2 value.

Then there is a problem.

If we want to use col3 value when col1 and col2 are NULL, what should we do ?

In that case we use nested IFNULL.

SELECT 
col1,
col2,
col3,
IFNULL(col1,IFNULL(col2,col3)) as f
FROM test.null_sample
Rowcol1col2col3f
1nullbcb
2nullnullcc
3abca
4abnulla
5anullnulla
NESTED IFNULL

Nested structure is hard to read.

So there is another solution.

COALESCE

In order to use another value instead of NULL, we can also use COALESCE.

In COALESCE, it accepts multiple parameters.

And it returns first not-null-value.

Conditional expressions in Standard SQL  |  BigQuery  |  Google Cloud

So it doesn't require nested structure.

SELECT 
col1,
col2,
col3,
COALESCE(col1,col2,col3) as f
FROM test.null_sample
Rowcol1col2col3f
1nullbcb
2nullnullcc
3abca
4abnulla
5anullnulla
COALESCE

This SQL is better to read.

Conclusion

Today I explained about "How to use alternative column value instead of null in BigQuery".

There are 2 methods to use another value instead of NULL.

Using alternative value
  • IFNULL
  • COALESCE

We can use IFNULL like IF function. But it can check only 1 expression.

COALESCE accepts 2 or more parameters and returns first non-null-value.

So it is useful to check multiple columns.

If you like this article, please
Like or Follow !

If you like this article, please share !

Author

karasanのアバター karasan System engineer

Mid-career engineer (AI, Data science, Salesforce, etc.).
Good at Python and SQL.

TOC