Nulls First and Nulls Last

(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>