mysql - SQL query, treat NULL as zero -

this question has answer here:

• mysql: typecasting null 0 1 answer

i'm learning sql (using mysql) , have simple question. have table salary , bonus information on employees, , i'd sum two, have mysql return value 0 when @ least 1 of summands null, instead of returning null. what's easiest way this?

mysql> select salary, bonus employee_pay_tbl; +----------+---------+ | salary   | bonus   | +----------+---------+ | 30000.00 | 2000.00 | |     null |    null | | 40000.00 |    null | | 20000.00 | 1000.00 | |     null |    null | |     null |    null | +----------+---------+  mysql> select salary + bonus employee_pay_tbl; +----------------+ | salary + bonus | +----------------+ |       32000.00 | |           null | |           null | |       21000.00 | |           null | |           null | +----------------+ 

you can use coalesce. accepts number of arguments , returns first 1 not null.

you can use ifnull (not confused isnull). behaves same in scenario, coalesce more portable; allows multiple arguments , returns first not-null one. also, other databases support too, makes easier migrate database if in future.

select coalesce(salary, 0) + coalesce(bonus, 0) employee_pay_tbl; 

or

select ifnull(salary, 0) + ifnull(bonus, 0) employee_pay_tbl; 

both of them convenient way write:

select    case when salary null 0 else salary end +   case when bonus null 0 else bonus end employee_pay_tbl;