Oracle order by processing NULL values

1. Default Processing

Oracle considers null as the maximum value by default in Order by, so if it is ASC in ascending Order, it is placed at the end, and DESC in descending Order is placed at the top.

2. Use the nvl Function

The nvl function can convert an input parameter to a specific value if it is null, as shown in figure
Nvl (employee_name, 'zhangsan') indicates that when employee_name is empty, 'zhangsan' is returned. If not empty, 'employee_name is returned.
You can use this function to customize the null sorting position.

3. Use the decode Function

The decode function is more powerful than the nvl function. It can also convert an input parameter to a specific value when it is null, such
Decode (employee_name, null, 'zhangsan', employee_name) indicates that 'zhangsan' is returned when employee_name is null. If not empty, the return value is employee_name.
You can use this function to customize the null sorting position.

4. Use case syntax

The Case syntax is supported after Oracle 9i. It is a flexible syntax and can also be used in sorting.
For example:
Select *
From employee
Order by (case employee_name
When null then
'Zhang san'
Else
Employee_name
End)
It indicates that when the value of employee_name is null, 'zhang san' is returned. If the value is not null, the value of employee_name is returned.
The case syntax can also be used to customize the null sorting position.

5. Use nulls first or nulls last syntax

Nulls first and nulls last are syntaxes supported by Oracle Order.
If the expression Nulls first is specified in Order by, the null value of the record is ranked first (whether asc or desc)
If the expression Nulls last is specified in Order by, the records with null values will be placed at the end (whether asc or desc)
The syntax is as follows:
-- Always put nulls at the beginning
Select * from zl_cbqc order by cb_ld nulls first

-- Always put nulls at the end
Select * from zl_cbqc order by cb_ld desc nulls last