Questions about SQL NULL ordering in Oracle database

In the case of query ordering in Oracle, if there are null values in the Sort field, the sort result may not be the result you want.

such as SELECT * from Tabletest ORDER BY VISITS DESC overwrites the original SQL statement to: SELECT * from Tabletest ORDER BY VISITS Desc nulls last, "nulls Last "control puts the null record in the back, of course, you can also use" nulls first "to put control records in front.
Oracle null-value processing, sorting filtering

Oracle considers NULL to be the largest.

in ascending order, by default, the null value is sorted back.

The descending sort, by default, is preceded by a null value.

There are several ways to change this situation:

(1) Convert NULL to a specific value using the NVL function or the Decode function

(2) Use case syntax to convert NULL to a specific value (Oracle9i later version support. Similar to SQL Server):
Order BY (case MyCol if NULL then ' Beijing bleach ' else MyCol end)

(3) Use Nulls first or nulls last syntax.

This is the syntax that Oracle specifically uses to sort null values.

Nulls first: null is the top of the line. Example: SELECT * from MYTB ORDER by MyCol nulls first

NULL Last: The null is queued at the bottom. Example: SELECT * from MYTB ORDER by MyCol nulls last

"SQL Server":

SQL Server considers null to be minimal.

In ascending order : Null values are ranked first in the default.

To arrange the back, then: the order by case if Col is null then 1 else 0 end, col

descending : null values default to the last.

To line up in front, then: the order by case if Col is null and then 0 else 1 end, col desc

Description
1, is equivalent to no value, is unknown.
2, null and 0, empty string, space are different.
3, add, subtract, multiply and divide the null value, and the result is still empty.
4, NULL processing uses the NVL function or NVL2.
5. Use the keyword "is null" and "is not NULL" when comparing.
6, the null value cannot be indexed, so the query when some eligible data may not be found out,
Count (*), after processing with NVL (column name, 0) and then check.
7, the sorting is larger than other data (index default is descending arrangement, small → Large),
So the null value is always at the end of the line.

A comparison of null values can only be an IS, was not, null the result of a comparison with any value (including NULL) is NULL
The handling of null values can be done by NVL (,)

SELECT * from dual where dummy is null;

Questions about SQL NULL ordering in Oracle database