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.