BigQuery - NULL values

Question

I want to make calculation among columns, which contains null values

x1     x2
9      0.0
5      1.2
12     null
10     null

If calculation

x1 + (x1*x2)

is made, it results in

9, 6, null, null

Can you pls suggest, how null values can be handled, so the result will be

9, 6, 12, 10

I was trying ifelse, if value is null, then use 1

IF(x1 = null, 0, x1)

but the results is still with null values.

Thank you!

Felipe Hoffa · Accepted Answer · 2015-10-20 06:42:04Z

46

Use IFNULL(expr, 0) - this will come back as 0 if expr is null.

In general, instead of doing something=null do something IS null.

answered Oct 20, 2015 at 6:42

Felipe Hoffa

59.8k23 gold badges185 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeremy Condit Over a year ago

And the reason is that any expression involving null (aside from "IS NULL" and "IFNULL") will evaluate to null, even if that expression is something like "foo = null".

Whit Waldo · Accepted Answer · 2022-09-01 00:37:52Z

7

Use ifnull function in Google Big Query. (Link provided)

IFNULL(x1, 0)*IFNULL(x2,0) as new_col

This will plug in 0 in place of any null values in x1 and x2.

FYI: There is a ISNULL(column_name) in Google cloud Data Prep just like in MySQL that will return a boolean i.e either True or False depending upon the column is null or not.

edited Sep 1, 2022 at 0:37

Whit Waldo

5,2774 gold badges54 silver badges75 bronze badges

answered Mar 5, 2018 at 20:30

geekidharsh

3,6091 gold badge22 silver badges25 bronze badges

Comments

norman123123 · Accepted Answer · 2021-07-09 16:01:28Z

2

Another possible solution is using COALESCE(arg1, arg2, ...,argN) Which returns the first not null argument.

For example:

COALESCE(x1 , 0) * COALESCE(x2, 0) returns 0 if x1 (or x2) is null.

answered Jul 9, 2021 at 16:01

norman123123

1,0078 silver badges17 bronze badges

Collectives™ on Stack Overflow

BigQuery - NULL values

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related