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.
