Replace null with 0 in MySQL
MysqlSqlNullMysql Problem Overview
I am getting NULL values in the results of an operation in MySQL.
Is there a way to convert the NULL values into the value 0?
Mysql Solutions
Solution 1 - Mysql
Yes, by using COALESCE.
SELECT COALESCE(null_column, 0) AS null_column FROM whatever;
COALESCE goes through the list of values you give it, and returns the first non-null value.
Solution 2 - Mysql
I am adding this answer because no one mentioned IFNULL function
You can use IFNULL
SELECT IFNULL(column_name, 0) FROM table_name;
IFNULL will return column's value (if it has something other than NULL) otherwise second parameter passed (in this case 0).
Solution 3 - Mysql
If you messed up and have NULLs in existing table layout and want zeros, here is solution:
UPDATE `table` SET `somefield`=0 WHERE `somefield` is null
Solution 4 - Mysql
There is the COALESCE method which return the first non-null parameter, in your case :
COALESCE(field, 0)
But you can use this if you want more :
COALESCE(field1, field2, 0)
Solution 5 - Mysql
MySQL:
SELECT COALESCE(Mycolumn, 0);
Solution 6 - Mysql
you can put the 0 value into your insert input method by casting it:(int)0