SQL query, treat NULL as zero
I am learning SQL (using MySQL) and have a very simple question. I have a table with salary and employee bonus information and I would like to summarize them, but MySQL returns zero when at least one of the terms is NULL
returned instead 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 |
+----------------+
source to share
You can use COALESCE
. It takes multiple arguments and returns the first one, which is not null.
You can use IFNULL
too (not to be confused with ISNULL
). In this case, it behaves the same, but COALESCE
more portable; it accepts multiple arguments and returns the first non-null one. In addition, other databases support it, so it makes it easier to switch to another database if you want 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 a very convenient way of writing:
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;
source to share