How to Manage those Pesky NULLs when Sorting Data

A typical query scenario: you want to sort data in descending order, say students arranged by their GMAT scores. Given a table student (id, name, score), what can be simpler than adding an ‘ORDER BY score DESC’ to the query?

If that’s what you thought, here’s a complication. There are some students who did not take the GMAT at all. Their scores in the table are not zero, they are NULL. Oracle’s ORDER BY..DESC in this situation could give you a nasty surprise with the result.

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

SQL> select * from student
  2  order by score desc;

        ID NAME                 SCORE
---------- --------------- ----------
         6 Thomas
         2 Rose
         7 Ruth                   705
         3 William                650
         4 Caledon                620
         5 Fabrizio               600
         1 Jack                   540

7 rows selected.

The Problem

We want our Ivy League material Ruth to be ranked first, but the NULLs push her to third. 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
  2  order by score;

        ID NAME                 SCORE
---------- --------------- ----------
         1 Jack                   540
         5 Fabrizio               600
         4 Caledon                620
         3 William                650
         7 Ruth                   705
         2 Rose
         6 Thomas

7 rows selected.

The problem takes on mammoth proportions when you’re forced to paginate through thousands of rows with NULLs in the sort column.

What is the way out?
Hint: It isn’t this…

SQL> select id
  2       , name
  3       , decode(score_t,-1,'',score_t) score
  4  from
  5  (
  6    select id, name, nvl(score,-1) score_t
  7    from student
  8    order by score_t desc
  9  );

        ID NAME            SCORE
---------- --------------- --------------------
         7 Ruth            705
         3 William         650
         4 Caledon         620
         5 Fabrizio        600
         1 Jack            540
         6 Thomas
         2 Rose

7 rows selected.

[If I had a dollar for each time I saw a variation of the above done to manage NULL columns…]

The Solution: ORDER BY NULLS LAST

Here’s a handy tip to sort the data in descending order AND place nulls at the bottom of the result list.

Use the NULLS LAST clause with ORDER BY to place NULLs last in the result list. This is applicable by default to ascending sort, specify it explicitly for descending sort.

SQL> select * from student
  2  order by score desc nulls last;

        ID NAME                 SCORE
---------- --------------- ----------
         7 Ruth                   705
         3 William                650
         4 Caledon                620
         5 Fabrizio               600
         1 Jack                   540
         6 Thomas
         2 Rose

7 rows selected.

Use the NULLS FIRST clause with ORDER BY to place NULLs first in the result list. This is applicable by default to descending sort, specify it explicitly for ascending sort.

SQL> select * from student
  2  order by score nulls first;

        ID NAME                 SCORE
---------- --------------- ----------
         2 Rose
         6 Thomas
         1 Jack                   540
         5 Fabrizio               600
         4 Caledon                620
         3 William                650
         7 Ruth                   705

7 rows selected.

Summary

In Oracle, 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.

To override the default behavior of ORDER BY, use ORDER BY NULLS FIRST/LAST.