SQL query, treat NULL as zero [duplicate]

Question

I'm learning SQL (using MySQL) and have a very simple question. I have a table with salary and bonus information on employees, and I'd like to sum the two, but have MySQL return the value zero when at least one of the summands is NULL, instead of returning NULL. What's the easiest way to do this?

mysql> SELECT SALARY, BONUS FROM 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 FROM EMPLOYEE_PAY_TBL;
+----------------+
| SALARY + BONUS |
+----------------+
|       32000.00 |
|           NULL |
|           NULL |
|       21000.00 |
|           NULL |
|           NULL |
+----------------+

Answer 1

You can use COALESCE. It accepts a number of arguments and returns the first one that is not null.

You can use IFNULL too (not to be confused with ISNULL). It behaves the same in this scenario, but COALESCE is more portable; it allows multiple arguments and returns the first not-null one. Also, other databases support it too, so that makes it slightly easier to migrate to another database if you would like to in the future.

SELECT COALESCE(SALARY, 0) + COALESCE(BONUS, 0)
FROM EMPLOYEE_PAY_TBL;

or

SELECT IFNULL(SALARY, 0) + IFNULL(BONUS, 0)
FROM EMPLOYEE_PAY_TBL;

Both of them are just a very convenient way to write:

SELECT 
  CASE WHEN SALARY IS NULL THEN 0 ELSE SALARY END +
  CASE WHEN BONUS IS NULL THEN 0 ELSE BONUS END
FROM EMPLOYEE_PAY_TBL;