MySQL, such as null null value sorting

"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

"Oracle":

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

If you want the columns that contain null to be sorted as you wish, you can do so.

"MySQL":

MySQL with sqlserver,null default minimum, workaround with SQL Server


Note:
1, NULL column as a query condition, regardless of the use of >/</>=/<= are not eligible, can only use IsNull to judge. as follows:

The Age field with ID 4 is empty,

Executes Sql:select * FROM student where the age >= 30; The results are as follows:

Execute Sql:select * FROM student where age < 30; The results are as follows:

Only Execute Sql:select * FROM student where-is null; To query for data with an age of NULL.

2, the null do add, subtract, multiply, divide and other operations, the result is still empty

Just started student table data as follows:

Executes sql:update student set age = Age + 20;

Execute Sql:select * from student; The results are as follows:

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

The test database above is mysql,oracle as well. SQL Server has not been tested and should be the same.

Null value
Columns can accept or reject null values. In the database, NULL is a special value that represents the concept of an unknown value. NULL differs from the null character or 0. The null character is actually a valid character, and 0 is a valid number. And NULL simply represents the notion that the value is unknown. NULL is also different from 0-length strings. If the column definition contains a NOT NULL clause, the row containing the null value cannot be inserted for the row. If the column definition contains only the NULL keyword, the null value is accepted.

Allowing NULL values within a column increases the complexity of logical comparisons using that column. The SQL-92 standard stipulates that any comparison of NULL values is not a value of TRUE or FALSE, but rather a value of UNKNOWN. This rule introduces three-value logic in the comparison operator, which is difficult to apply correctly.

Sorting null empty values such as MySQL