SQL Server – Dealing with NULL values when sorting

mssql-logo

When working with NULL values, it’s important how you deal with NULL records in your database. This example walks through and explains how to sort and separate NULL and non-NULL values. This example should work with all database types including MySQL, MS SQL, and postgreSQL.

Let’s start with an example.

Consider an employee table.

eid         ename                salary      commission
----------- -------------------- ----------- -----------
100         jon                  2000        NULL
101         tim                  2200        NULL
102         mark                 2500        NULL
103         steve                3500        NULL
104         king                 5500        0
104         ward                 1500        300
105         adam                 5500        800

 

Now, you want to sort the results from employee table by the commission column. However, this column is nullable so we will need to specify whether NULL values sort last or first.

The result could contain NULLs sorted either first or sorted last.

select * from employee order by commission;

eid         ename                salary      commission
----------- -------------------- ----------- -----------
100         jon                  2000        NULL
101         tim                  2200        NULL
102         mark                 2500        NULL
103         steve                3500        NULL
104         king                 5500        0
104         ward                 1500        300
105         adam                 5500        800

(7 row(s) affected)
select * from employee order by commission desc;

eid         ename                salary      commission
----------- -------------------- ----------- -----------
105         adam                 5500        800
104         ward                 1500        300
104         king                 5500        0
100         jon                  2000        NULL
101         tim                  2200        NULL
102         mark                 2500        NULL
103         steve                3500        NULL

(7 row(s) affected)

The above queries sort the results for nullable column (commission). If you would like to sort non-NULL values in ascending and descending order and place the NULL values either first or last, you can use sub-queries with case expression.

Using a case expression, you can flag NULL values, and non-NULL values. Once accomplished, you can add a flag column in order by clause. Then you can easily sort non-NULLs values in ascending/descending order or the NULL values in first or last position.

Sorting the non-NULLs values in ascending or descending order.

select eid, ename, salary, commission
from
(select *,
case when commission is null then 1
else 0
end as is_null
from employee) x
order by is_null, commission;

eid         ename                salary      commission
----------- -------------------- ----------- -----------
104         king                 5500        0
104         ward                 1500        300
105         adam                 5500        800
100         jon                  2000        NULL
101         tim                  2200        NULL
102         mark                 2500        NULL
103         steve                3500        NULL

(7 row(s) affected)

In the above query, we have sorted non-NULLs in ascending order with NULLs at last.

Sorting NULLs first and non-NULLs in descending order.

select eid, ename, salary, commission
from
(select *,
case when commission is null then 1
else 0
end as is_null
from employee) x
order by is_null desc, commission desc;

eid         ename                salary      commission
----------- -------------------- ----------- -----------
100         jon                  2000        NULL
101         tim                  2200        NULL
102         mark                 2500        NULL
103         steve                3500        NULL
105         adam                 5500        800
104         ward                 1500        300
104         king                 5500        0

(7 row(s) affected)

Now you can see the results NULL values sorted first followed by non-NULLs in descending order.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

  • Marc thomsan

    Really good article thanks for info.

    • Vishwanath Dalvi

      You are welcome Marc.