SQL COALESCE

I know it seems to be a recurring theme with me dealing with NULLs, probably because I come up with situations dealing with NULLs all the time at work.  You can't use equality symbols on NULLs, so if you have NULL, you need to figure out how to deal with them.

Here is another fancy way of dealing with NULLs if you have some equations you have to run.

Let's say that you work for an organization that deals with different salary types:

• Hourly
• Salary
• Commissions

If you work at a company that has a sales force, you more than likely have all three of these salary types.  Now, do you keep a different table for each salary type and store the employees in those different tables?  Sounds like a little too much work for such an easy concept.  That is where SQL COALESCE() comes into play.

SQL COALESCE

COALECE allows you to specify a comma delimited list of expressions and it will return the first non-NULL expression.  So, in our example above, we have columns that show an employee's: hourly wage, salary, commission amount and number of sales.  We can then specify each expression with the wage calculation, as seen below:


SELECT COALESCE(hourly_wage * 40 * 52, salary, commission * salesnum) AS 'Total Salary'
FROM wages

This will then return the wage for each employee, no matter if they are hourly, salary or commission based.