Thursday, June 02, 2011

Nulls First and Nulls Last

Get great prices on parts from Andy's Auto Sport by clicking here!

(Tested on an Oracle 9 database.)

When Oracle sorts data, nulls come out top in the sort order. So if you sort in ascending order, they appear last:

SQL> create table employees
  2  (name1 varchar2(10))
  3  /

Table created.

SQL> insert into employees values ('Andrew')
  2  /

1 row created.

SQL> insert into employees values ('Fred')
  2  /

1 row created.

SQL> insert into employees values (null)
  2  /

1 row created.

SQL> select nvl(name1,'Null') first_name
  2  from employees
  3  order by name1 asc
  4  /

FIRST_NAME
----------
Andrew
Fred
Null

SQL>

And if you sort in descending order, they are shown first:

SQL> select nvl(name1,'Null') first_name
  2  from employees
  3  order by name1 desc
  4  /

FIRST_NAME
----------
Null
Fred
Andrew

SQL>

If you specify nulls first in the order by statement, the null values will appear before the rest of the data. This applies for both ascending and descending sorts:

SQL> select nvl(name1,'Null') first_name
  2  from employees
  3  order by name1 asc nulls first
  4  /

FIRST_NAME
----------
Null
Andrew
Fred

SQL> select nvl(name1,'Null') first_name
  2  from employees
  3  order by name1 desc nulls first
  4  /

FIRST_NAME
----------
Null
Fred
Andrew

SQL>

Conversely, nulls last makes the null values appear after the other data. Again, this applies for both ascending and descending sorts:

SQL> select nvl(name1,'Null') first_name
  2  from employees
  3  order by name1 asc nulls last
  4  /

FIRST_NAME
----------
Andrew
Fred
Null

SQL> select nvl(name1,'Null') first_name
  2  from employees
  3  order by name1 desc nulls last
  4  /

FIRST_NAME
----------
Fred
Andrew
Null

SQL>

No comments: