mysql 0 null_MySQL：类型为NULL到0

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.

解决方案

Use IFNULL(column, 0) to convert the column value to zero. Alternatively, the COALESCE function will do the same thing, except (1) COALESCE is ANSI-compliant, IFNULL is not, and (2) COALESCE takes an arbitrary number of columns/values and will return the first non-null value passed to it.