How to Manage NULLs when Sorting Data

 

 
Consider a scenario such that you want to sort data in descending order, for example Given a table student (gateId, name, score), now to arrange the students by their GATE scores the following query can be used.

Select * from Student 'ORDER BY score DESC'

 
Now consider a scenario such that some students who did not take the GATE at all, now their scores in the table are not zero, but they are null. In this case Oracle’s ORDER BY..DESC will give a surprise result.

Here’s how the result will look with the ORDER BY…DESC clause:
 

SQL> select * from student order by score desc;
 
    GATEID NAME                 SCORE
---------- --------------- ----------
         9 Blellik
         3 Michal
         7 Sara                   910
         4 Ibrahim                840
         1 Caledon                730
         6 Fabrizio               710
         2 Jaya Prakash           640
         5 Nilafar                580
         8 Lourde                 550

 
In this scenario actually Sara need to be ranked 1st, but the nulls push that student rank to 3rd as ORDER BY..DESC in Oracle places null values right at the top of the query results.
 

Likewise, ORDER BY (ascending order) places null values at the end of the query results.

SQL> select * from student order by score;
 
    GATEID NAME                 SCORE
---------- --------------- ----------
         8 Lourde                 550
         5 Nilafar                580
         2 Jaya Prakash           640
         6 Fabrizio               710
         1 Caledon                730
         4 Ibrahim                840
         7 Sara                   910
         3 Michal
         9 Blellik

Solution
 
Starting with Oracle 8i, there is a little known syntax available in the ORDER BY clause that resolve this issue. All you have to do is change the last line above to the following:

SQL> select * from student order by score desc nulls last;
 
    GATEID NAME                 SCORE
---------- --------------- ----------
         7 Sara                   910
         4 Ibrahim                840
         1 Caledon                730
         6 Fabrizio               710
         2 Jaya Prakash           640
         5 Nilafar                580
         8 Lourde                 550
         9 Blellik
         3 Michal

 
You can also use NULLS FIRST when you’re sorting in ascending order, and you want the NULL rows to appear at the top of the report.

SQL> select * from student order by score nulls first;
 
   GATEID NAME                 SCORE
---------- --------------- ----------
         3 Michal
         9 Blellik
         8 Lourde                 550
         5 Nilafar                580
         2 Jaya Prakash           640
         6 Fabrizio               710
         1 Caledon                730
         4 Ibrahim                840
         7 Sara                   910

 
If you’re still supporting Oracle 8.0 or 7.3 databases, you can achieve the same effect using the Null Values function (NVL).
 

ORDER BY NVL(score, -1);

 
This forces the NULL rows to be sorted as if they had the value (-1) in them, and they will appear at the bottom of the output.
 
Conclusion
 
In Oracle database, the default sort order in an ascending sort places null values at the bottom of the result list, and in a descending sort at the start of the result list. This is because, NULL is treated as a very large value by Oracle. To override this default behavior of ORDER BY, use the NULLS FIRST/LAST clause.