[Nulls] oracle "Special Care" for the position of null values after SQL sorting"

Reprinted from: http://space.itpub.net/519536/viewspace-626464

Oralce pairNullThere is a special "care" in the position after sorting the values, which is "nulls
With the "first" and "nulls last" options, you can use this option to forcibly specify the position where the null value appears in SQL sorting (whether it is displayed at the beginning or at the end ). Demonstration and summary here for your reference.

1. Create a demo table t and initialize 7 data records
SEC @ ora10g> Create Table T (x INT );
SEC @ ora10g> insert into T values (1 );
SEC @ ora10g> insert into T values (2 );
SEC @ ora10g> insert into T values (3 );
SEC @ ora10g> insert into T values (4 );
SEC @ ora10g> insert into T values (null );
SEC @ ora10g> insert into T values (null );
SEC @ ora10g> insert into T values (null );
SEC @ ora10g> commit;

2. sort order by without adding "Take care" -- the value of null is after.
SEC @ ora10g> select * from t order by X;

X
----------
1
2
3
4




7 rows selected.

3. The order by descending order effect without "Take care" -- the null value is in front.
SEC @ ora10g> select * from t order by x desc;

X
----------



4
3
2
1

7 rows selected.

4. sort order by in ascending order of special "Take care" -- the null value is in front.
SEC @ ora10g> select * from t order by X nulls first;

X
----------



1
2
3
4

7 rows selected.

5. Order by descending order effect for special "Take care" -- the value of null is after.
SEC @ ora10g> select * from t order by x desc nulls last;

X
----------
4
3
2
1




7 rows selected.

6. Rule Summary
1) without "Taking Care", we can assume that the null values in all content are the largest. Therefore, after sorting in ascending order, the null value is at the end, the value of null after inverted sorting is at the beginning!
2) When "nulls first" is specified, the null values are listed at the beginning, whether in ascending or descending order; when "nulls last" is specified, the null values are arranged at the end of the ascending or descending order.

7. reference content for "nulls first | nulls last" in Oracle official documents
Http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2171079
The excerpt is as follows:
Nulls first | nulls last
Specify whether returned rows containing null values shoshould appear first or last in the ordering sequence.
Nulls last is the default for ascending order, and nulls first is the default for descending order.

8. Summary
Through this introduction, we can see that Oracle is indeed meticulous. With the Forced method of the null value position, the flexibility and controllability of SQL writing can be further enhanced.

The end!