MYSQL、SQL Server、Oracle資料庫排序空值null問題及其解決辦法

 

【sqlserver】：

sqlserver 認為 null 最小。

升序排列：null 值預設排在最前。

要想排後面，則：order by case when col is null then 1 else 0 end ,col

降序排列：null 值預設排在最後。

要想排在前面，則：order   by case when col is null then 0 else 1 end , col desc

【oracle】：

oracle認為 null 最大。

升序排列，預設情況下，null值排後面。

降序排序，預設情況下，null值排前面。

有幾種辦法改變這種情況：

（1）用 nvl 函數或decode 函數 將null轉換為一特定值

（2）用case文法將null轉換為一特定值（oracle9i以後版本支援。和sqlserver類似）：
order by (case mycol when null then '北京漂客'     else   mycol   end)

（3）使用nulls first 或者nulls last 文法。

這是oracle專門用來null值排序的文法。

nulls first ：將null排在最前面。如：select * from mytb order by mycol nulls first

null last ：將null排在最後面。如：select * from mytb order by mycol nulls last

如果要想讓含有null的列按照自己的意願進行排序，可做如上處理。

【mysql】：

MySQL同sqlserver，null預設最小，解決辦法同sqlserver

注意：
1、null的列作為查詢條件時，無論使用>/</>=/<=都是不合格，只能使用isNull來判斷。如下：

id為4的age欄位為空白，

執行SQL：select * from student where age >= 30; 結果如下：

執行SQL：select * from student where age < 30; 結果如下：

只有執行SQL：select * from student where age is null; 才能查詢出age為null的資料。

2、對null做加、減、乘、除等運算操作，結果仍為空白

剛開始student表資料如下：

執行SQL：update student set age = age + 20;

執行SQL：select * from student; 結果如下：

非null的age欄位都更新為+20，而null欄位依然為空白。

上面的測試資料庫為MySQL，Oracle也一樣。SQL Server沒測試過，應該也是一樣的。

空值
列可以接受或拒絕空值。在資料庫內 NULL 是特殊值，代表未知值的概念。NULL 不同於Null 字元或 0。Null 字元實際上是有效字元，0 是有效數字。而 NULL 只是表示該值未知這一概念。NULL 也不同於零長度字串。如果列定義中包含 NOT NULL 子句，則不能為該行插入含有 NULL 值的行。如果列定義中僅包含 NULL 關鍵字，則接受 NULL 值。

在列內允許 NULL 值會增加使用該列的邏輯比較的複雜性。SQL-92 標準規定：對 NULL 值的任何比較都不取值為 TRUE 或 FALSE，而是取值為 UNKNOWN。此規定在比較子中引入了三值邏輯，而要正確運用該邏輯很困難。[摘自Microsoft SQLServer聯機叢書]

SQL中null值索引最佳化的文章：

【摘】SQL最佳化:NULL值與索引的使用