December 2012 Archive

« April 2011 | Main | August 2014 »

December 20, 2012

MySQL COALESCE function

@ 7:21 AM
This morning I needed to pull out names and email addresses from a MySQL table to import into a mailing list system.

The table has two name fields though - one a username, which is always populated, one a 'real' name, which is optional for the user. Since we want to use the subscriber's name in the emails we'll be sending I wanted to select the real name is if was populated, otherwise fall back to the username.

The MySQL COALESCE function does just this - pass it a list of fields and it will return the first non-NULL value in that list:

SELECT COALESCE(real_name, user_name) AS name FROM table;

This was new to me, and a very useful addition to the arsenal - previously I'd have used an IF statement to detect whether real_name has a LENGTH greater than 0, or post-processed the query results.