Replace null with 0 in 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.
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
).
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)
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