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".

Author

Mid-carieer engineer (AI, system). Good at Python and SQL.

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.

How to use alternative column value instead of null in BigQuery

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

I will introduce each of them.

IFNULL

First you may imagine IF function.

There is similar function IFNULL that is for NULL values.

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.

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

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.

So it does't require nested structure.

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

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 instaed of NULL.

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.

There are some other articles about BigQuey.

If you interested in them, please read them.