MySQL: Ignore null values when ordering results

Fast answer: use IFNULL() in your select statement.

Lets say you have some products that have a length attribute which is recorded in two different ways: inches and feet. In your CMS, the client enters EITHER inches OR feet. When generating a list of products sorted by length, you’d want the ‘inch’ products up top and the ‘feet’ products under them. At first, you’d probably try this:

SELECT id, title, lenIN, lenFT
FROM products

But that would give you the ‘feet’ products first since the inch values would be null. A second attempt:

SELECT id, title, lenIN, lenFT
FROM products

That of course puts the products in the wrong ordering. Here is the fix:

SELECT id, title, IFNULL(lenIN, 1000) as tempIN, lenIN, lenFT
FROM products
ORDER BY tempIN, lenIN, lenFT

It comes from the magic of:


If expr1 is not NULL, IFNULL() returns expr1, else it returns expr2. So when inches is null, a value of 1000 is used. I picked ‘1000’ because I’m sure no product will be 1000 inches long (feet would surely be used at that point). The result is the product which have inches filled will be put at the top of the list, ordered correctly, followed by the products where inches were blank (and are now 1000 inches).