MySQL is flexible in the data model and allows you to have nullable columns. For example, you’re using NULL as the default date-time value until a given action happens. When displaying the data you may want to apply a specific sorting. But if the sorting column allows NULL values, you may want to fall back to another column that contains a value (possibly because of a NOT NULL assignment).

This tutorial shows you how to order values in MySQL by multiple columns that use the first non-null one.

MySQL Series Overview

Use the First Non-Null Value From a List of Fields

MySQL has the COALESCE function that returns the first non-NULL column from a given list, or NULL if all values are NULL.

Here’s a simple example showing the functionality of COALESCE:

SELECT COALESCE(NULL, 1);  
# 1

Let’s use the COALESCE function in another example. Let’s say you want to sort the users of your application based on when they confirmed their account. This sorting will show the newest users, that are allowed to interact with your app, at the beginning of the listing. If a user hasn’t confirmed their account, you want to use the registration date.

Here’s an example using COALESCE to retrieve the confirmation or creation date of a given user:

SELECT *  
FROM users  
ORDER BY COALESCE(confirmedon, createdon) DESC;  

You can also use COALESCE in the list of selected fields. The following example uses a named field confirmedonOrCreatedon that contains the first non-NULL value from the two fields:

SELECT  
  *,
  COALESCE(confirmedon, createdon) as confirmedonOrCreatedon
FROM users  
ORDER BY confirmedonOrCreatedon DESC;  

That’s it!

Mentioned Resources

• MySQL docs on the COALESCE function