MySQL: Typecasting NULL to 0

Question

Let us suppose the following table (e.g. a result of several inner join statements):

id | column_1 | column_2 ------------------------  1 |  1       |   2 |  2       | 2  3 |          | 3 

Which you could for example get from the following statement:

select a.id, t1.column_1, t2.column_2 from a left join t1 on a.id = t1.id left join t2 on a.id = t2.id 

Now, if i'd like to sum up t1.column_1 and t2.column_2 as follows

select      a.id,      t1.column_1,      t2.column_2,     (t1.column_1 + t2.column_2) as cumulated from a left join t1 on a.id = t1.id left join t2 on a.id = t2.id 

The reslut will look as follows:

id | column_1 | column_2 | cumulated ------------------------------------  1 |  1       | NULL     | NULL  2 |  2       | 2        | 4  3 |  NULL    | 3        | NULL 

My question basically is: is there a way to typecast NULL into 0 in order to do some math?

I have tried CONVERT(t1.column_1, SIGNED) and CAST(t1.column_1 as SIGNED), but a NULL stays a NULL.

Answer 1

Use IFNULL(column, 0) to convert the column value to zero.

Alternatively, the COALESCE function will do the same thing: COALESCE(column, 0), except

1. COALESCE is ANSI-compliant, IFNULL is not
2. COALESCE takes an arbitrary number of columns/values and will return the first non-null value passed to it.