MySQL COALESCE function

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.

Post a comment

Personal information