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?
Add a comment
|
6 Answers
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.
3 Comments
OMG Ponies
+1:
COALESCE is ANSI, supported by SQL Server 2000+, Oracle 9i+, MySQL 4.1+, dunno the version of PostgreSQL or SQLite...
D'Arcy Rittich
MySQL is mentioned in the title. I added a tag for it.
Evan Carroll
I think every 3VL DB with a SQL interface supports
coalesce(). Case and point, it was in MySQL 3.23 which is about as close to the bottom of the barrel as you can get.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).
3 Comments
tedioustortoise
This only really selects and replaces, rather than truly updating the table
Xin Niu
can i do it with multiple columns?
Arif
@XinNiu yes you can.
SELECT IFNULL(column1, column2) FROM table. or SELECT IFNULL(column1, 0) AS alias1, IFNULL(column2, 0) AS alias2 FROM table. BUT if you want to use more than 2 columns in one expression then you will need to use COALESCE like this SELECT COALESCE(column1, column2, column3, column4, 0) FROM table. COALESCE will return first non-null value in the parameters.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
2 Comments
Barney Chambers
this is the best answer
Tom
I almost got tricked by the accepted answer. This is exactly what I wanted. Thank you!
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)
Comments
MySQL:
SELECT COALESCE(Mycolumn, 0);
Comments
you can put the 0 value into your insert input method by casting it:(int)0
