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.

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>