Category Archives: Development

SQL COALESCE function

Today I discovered the SQL function ‘COALESCE’.  The function returns the value with the highest precedence datatype from a comma separated list of expressions.  For example:

SELECT COALESCE (site_settings.VALUE, settings.VALUE) AS VALUE
FROM site_settings
LEFT JOIN settings ON settings.id = site_settings.setting_id

In the above example the first non NULL value passed to the COALESCE function is selected. if site_settings.value is NULL then settings.value will be selected. If settings.value is NULL, then the site_settings.value will be selected. If both values are NULL, then NULL will be returned.

Here is the MySQL reference page for the function: COALESCE

Worth keeping in mind. Came in handy for simplifying some SQL at work.