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!

Answer 1

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.

Answer 2

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.