Common handling of null values in Oracle sorting

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 the input parameter to a specific value when it is null. For example, nvl (employee_name, 'zhang san') indicates that when employee_name is null, 'zhang san' is returned ', if it is not null, the return value is employee_name. This function can be used 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 as decode (employee_name, null, 'zhangsan', employee_name) it indicates that when the value of employee_name is null, 'zhang san' is returned. If the value is not null, the return value is "employee_name". This function can be used to customize the null sorting position.

 

4. Use Case syntax

The case syntax is supported after Oracle 9i and is flexible. It 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) indicates that when the value of employee_name is null, 'zhang san' is returned. If the value is not null, the return value is the position where the null sorting can be customized using the case syntax.

 

5. Use nulls first or nulls last syntax

nulls first and nulls last are syntaxes supported by Oracle order by. If the expression nulls first is specified in order by, the records with the null value are ranked first (whether ASC or DESC) if the expression nulls last is specified in order by, the records with null values (whether ASC or DESC) will end with the following syntax: -- always put nulls in the first select * From zl_cbqc order by cb_ld nulls first -- always put nulls in the last select * From zl_cbqc order by cb_ld DESC nulls last