What is the best way to get sorted SQL query based on null values

Question

I have a table in my database which i want to extract data in a specific order.
Here is an example of the table:

CREATE TABLE `test` (
  `field1` int(11) NOT NULL,
  `field2` int(11) DEFAULT NULL,
  `field3` int(11) DEFAULT NULL,
  UNIQUE KEY `field1_2` (`field1`,`field2`),
  UNIQUE KEY `field1_3` (`field1`,`field3`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Valid entries in my table is:

field1 must be filled
field2 can be either null or an unique value based on field1 and field2
field3 can be either null or an uniqie value based on field1 and field3

The order i want to extract the data is:

all fields with field2 and field3 as null
all fields with field2 as null and field3 with values
all fields with field3 as null and field2 with values

So far i have used three queries connected with UNION

SELECT * FROM test WHERE field2 IS NULL 
    AND field3 IS NULL UNION
SELECT * FROM test WHERE field2 IS NULL 
    AND field3 IS NOT NULL UNION 
SELECT * FROM test WHERE field2 IS NOT NULL 
    AND field3 IS NULL;

I myself feel this is to much necessary code to get it in order and hoped there was a better solution.

So is there a better way?

Gordon Linoff Gordon Linoff · Accepted Answer · 2015-07-23T14:02:49

Just put your conditions into a single order by:

order by (field2 is null and field3 is null) desc,
         (field2 is null and field3 is not null) desc,
         (field2 is null and field3 is not null) desc

You might want to add a where as well:

where field2 is null or field3 is null

This works because MySQL treats booleans as numbers in a numeric context, with "true" as 1 and "false" as 0. This is why the desc is needed. You can express the same thing in standard SQL as:

order by (case when field2 is null and field3 is null then 1
               when field2 is null and field3 is not null then 2
               when field2 is not null and field3 is null then 3
               else 4
          end)

What is the best way to get sorted SQL query based on null values

1 Answers