Oracle order NULL LAST by default

Question

Is there a way to set the default order used by Oracle to be NULL LAST (or NULL FIRST), without have to put it in each query?

Answer 1

No, there is no way to change the default behavior of NULLS FIRST and NULLS LAST:

NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.

---

I can't prove it's impossible to change, but I cannot find such a feature in the places it is most likely to be implemented.

SQL Option The manual does not mention anything.

Parameter None of the nls parameters in V$PARAMETER control it: select * from v$parameter where name like '%nls%';

Hidden Parameter There's no hidden parameter. I tried searching for anything like %null% or %sort%, none of them appear relevant.

Locale Builder Oracle allows you to create your own custom sorting. It has a lot of options, but none of them allow you to define how NULLs are sorted. Setting the Major Sort and Minor Sort numbers really high or low does not change it (I was hoping that a NULL was implemented as a hard-coded small or large value). You can set the sort order for 0x0000, which is "NULL", but that's a different type of NULL.

Answer 2

No, there is no way to enable default ordering without using order by clause, which allows you put NULLs last or first. Here is an example:

1. Ascending ordering

   SQL> with t1(col) as(
     2    select 1    from dual union all
     3    select 2    from dual union all
     4    select null from dual union all
     5    select 3    from dual
     6  )
     7  select *
     8    from t1
     9  order by col asc nulls last
     10  ;
   

   Result:

     COL
     ------
      1
      2
      3
      null
   

2. Descending ordering

     SQL> with t1(col) as(
      2    select 1    from dual union all
      3    select 2    from dual union all
      4    select null from dual union all
      5    select 3    from dual
      6  )
      7  select *
      8    from t1
      9  order by col desc nulls last
     10  ;
   

   Result:

     COL
     ----------
      3
      2
      1
      null
   

Answer 3

ORDER BY EMPLOYER_NAME DESC NULLS LAST

The above worked for me on Oracle 11g

Answer 4

In ascending order, NULL values will always be sorted last