Looking for a MySQL equivalent for SQL Server's ISNULL function?

SQL Server has a well known and widely used ISNULL function that actually replaces a null column value or null subquery value with whatever you specify. For example, if you were to write

then you would have zeros returned in your result set for all InventoryItem table records that had a NULL value for the UnitPrice field. Ever tried to do that in MySQL? It doesn't work because MySQL's ISNULL function simply returns a value telling you whether or not the argument to the ISNULL function was NULL or not. Not as cool when compared to SQL Server's nifty replace type of functionality now is it? But wait, no need to talk smack about MySQL - for there is another great and easy to use command in MySQL that will give us the same result!

So to get you up to speed, I've been working on a cfchart today that uses a simple cfc query result to display demographic information in a bar graph. We want to graph demographics for a group of people by Ethnicity and Gender. This sounds easy enough until you include the requirement that I must show a demographic group even if the count of people who fall into said group is zero. So now instead of simply querying the table of people and grouping by the EthnicityCode and performing a COUNT(*) we have to instead query a subsidiary table that contains all possible EthnicityCode values and then subquery the table of people and COUNT(*) the matching records. To make matters worse, when our cfc method returns the query results with NULL values to the cfchartseries then our cfchartseries skips the "" values and leaves in the last good value it had prior to running into the NULLs. Bummer.

First off, I tried ISNULL() but in MySQL that's not going to allow me to essentially replace a NULL with a zero. And that's when I remembered the awesome good old COALESCE function which will return the first non NULL argument from a list! Perfect, exactly what we need. In this case I'm using it on the value returned by a subquery but you can just as easily use it on a column name in your SQL syntax.

So now our cfc is able to return a zero to the cfchartseries rather than an empty string "" which will allow us to properly graph zero values as shown below...