MySQL, SQL Server, Oracle Database sorting null Problem and Solution

 

[Sqlserver ]:

Sqlserver considers null to be the smallest.

Sort in ascending order: The null value is ranked first by default.

Order by case when col is null then 1 else 0 end, Col

Sort in descending order: The null value is ranked last by default.

Order by case when col is null then 0 else 1 end, col DESC

[Oracle ]:

Oracle considers null to be the largest.

Sort in ascending order,By default, the null value is placed behind it.

Sort in descending order,By default, the null value is first.

There are several ways to change this situation:

(1) Use the nvl function or decode function to convert null to a specific value.

(2) Use Case syntax to convert null to a specific value (later than Oracle9i. Similar to sqlserver ):
Order by (Case mycol when null then 'Beijing drioker' else mycol end)

(3) Use nulls first or nulls last syntax.

This is Oracle's syntax for sorting null values.

Nulls first: Place null at the top. For example, select * From mytb order by mycol nulls first

Null last: the last line of null. For example, select * From mytb order by mycol nulls last

If you want to sort the columns containing null according to your own wishes, you can perform the preceding operations.

[MySQL ]:

MySQL is the same as sqlserver, and null is the minimum by default. The solution is the same as that of sqlserver.

Note:
1. When a null column is used as a query condition, no matter whether you use >/</> =/<=, the condition is not met. You can only use isnull to judge the condition. As follows:

The age field with ID 4 is empty,

Run SQL: Select * from student where age> = 30; the result is as follows:

Run SQL: Select * from student where age <30; the result is as follows:

Only when SQL: Select * from student where age is null can the data whose age is null be queried.

2. add, subtract, multiply, and divide null. The result is still null.

The data in the student table is as follows:

Execute SQL: Update student set age = age + 20;

Execute SQL: Select * from student; the result is as follows:

The non-null age field is updated to + 20, while the null field is still null.

The above test database is MySQL, and Oracle is the same. SQL Server has not been tested. The same should be true.

Null Value
The column can accept or reject null values. Null is a special value in the database, representing the concept of an unknown value. Null is different from null or 0. Null is actually a valid character, and 0 is a valid number. Null indicates that the value is unknown. Null is also different from a zero-length string. If the column definition contains a not null clause, a row containing null values cannot be inserted for this row. If the column definition only contains the null keyword, the null value is accepted.

Adding a null value to a column increases the complexity of logical comparison using the column. SQL-92 standards stipulate that any comparison of null values is not set to true or false, but to unknown. This rule introduces the three-value logic in the comparison operator, but it is very difficult to use this logic correctly. [From Microsoft sqlserver books online]

Article on optimization of null index in SQL:

【Abstract】 SQL optimization: Use of null values and Indexes