BigQuery - NULL values

null google-bigquery

user3577904 · Oct 20, 2015 · Viewed 43.9k times · Source

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

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.