NULL values and sorting order

SQL> DESCRIBE exmpl
Name Null? Type
----------------- -------- ----------------------------
ID NUMBER(20)
AMOUNT NUMBER

SQL> SELECT * FROM exmpl;
ID AMOUNT
---------- ----------
1 100
2
3 200

Let's see the table after ORDER BY clause.

SQL> SELECT * FROM exmpl ORDER BY amount;
ID AMOUNT
---------- ----------
1 100
3 200
2

We see that NULL value - last. Let's try to revert order.

SQL> SELECT * FROM exmpl ORDER BY amount DESC;
ID AMOUNT
---------- ----------
2
3 200
1 100


NULL value is shown first. How can we change the order? We need to apply NULLS FIRST or NULLS LAST.

SQL> SELECT * FROM exmpl ORDER BY amount DESC NULLS LAST;
ID AMOUNT
---------- ----------
3 200
1 100
2

SQL> SELECT * FROM exmpl ORDER BY amount;
ID AMOUNT
---------- ----------
1 100
3 200
2

SQL> SELECT * FROM exmpl ORDER BY amount NULLS LAST;
ID AMOUNT
---------- ----------
1 100
3 200
2

SQL> SELECT * FROM exmpl ORDER BY amount NULLS FIRST;
ID AMOUNT
---------- ----------
2
1 100
3 200


So, as we have ascending order by default in ORDER BY clause, we have also NULLS LAST by default in ascending order. Course, NULLS FIRST is default parameter with DESC in ORDER BY parameter.

Summarizing, we can say that NULL values are considered as the greatest by defult. In ascending order they are shown last by default, in descending order they are shown first by default.