Andrew Fiebert, Programmer and DBA
The blogfolio of
developer Andrew Fiebert

Archive for the ‘Rants’ Category

How to convert NULL to zero in MySQL

Monday, January 21st, 2008

Today I was pulling a query from MySQL and some of my columns were NULL. Now this isn’t the first time I’ve gotten NULL as a result and usually my code has an if statement in there to swap out the NULL values with zero. The problem is this time that I don’t have the option to edit the code after the query has been returned, I need the actual query results to have NULL removed from them. This lead to my discovery of COALESCE.

I have a column SUM(`order-total`) which will return the total dollar amount a user has spent on the website only some users have yet to make any purchases.

COALESCE is used in the following way:

COALESCE(SUM(`order-total`),0.00)

This acts just like str_replace in PHP. You can use COALESCE in any part of your query, it doesn’t only have to be where you specify the columns you want returned.