How to Order in MySQL, Merging Two Columns Containing NULL or Empty Values

After explaining How to Order in MySQL, putting Zero or NULL at the end today we’ll be looking at how to order based on two columns, one of which may have either NULL or empty values in.

I came across this problem on a client’s site where we have an “alternative name” field for each product. This is so the client can, where necessary, display a different name on the front end of the site from what they see in admin. The problem here is that not all products have an alternative name so if we want to order by the product name in MySQL, we have to take both the “name” and “alternative name” fields into account.

We acheive this like so:

SELECT COALESCE(NULLIF(alternative_name, ''), name) AS name ORDER BY name

This replaces any empty values with NULL then the COALESCE function picks the first non-NULL value.

If your database has NULLs instead of empty values you can leave the first step out and simply have:

SELECT COALESCE(alternative_name, name) AS name ORDER BY name

This code is free to use at your own discretion. It comes without warranty. Please feel free to feedback any edits.