1 null值的介紹
NULL 是數據庫中特有的數據類型，當一條記錄的某個列為 NULL ，則表示這個列的值是未知的、是不確定的。既然是未知的，就有無數種的可能性。因此， NULL 並不是一個確定的值。 這是 NULL 的由來、也是 NULL 的基礎，所有和 NULL 相關的操作的結果都可以從 NULL 的概念推導出來。
2 oracle中的null值介紹
在不知道具體有什麼數據的時候，即未知，可以用NULL， 稱它為空，ORACLE中，含有空值的表列長度為零。允許任何一種數據類型的字段為空，除了以下兩種情況：
a、主鍵字段(primary key);
b、定義時已經加了NOT NULL限制條件的字段
3 Oracle中null值說明：
a、等價於沒有任何值、是未知數。
b、NULL與0、空字符串、空格都不同。
c、對空值做加、減、乘、除等運算操作，結果 仍為空。
d、NULL的處理使用NVL函數。
e、比較時使用關鍵字用“is null”和“is not null”。
f、空值不能被索引，所以查詢時有些符合條件的數據可能查不出來， count(expr)中，用nvl(列名,0)處理後再查。
g、排序時比其他數據都大(索引默認是降序排列，小→大)， 所以NULL值總是排在最後。
IS NULL 和IS NOT NULL 是不可分割的整體，改為IS 或IS NOT都是錯誤的，從上面我們看到了NULL 和空字符串的區別。
任何和NULL 的比較操作，如<>、=、<=等都返回UNKNOWN(這裡的unknown就是null，它單獨使用和布爾值false類似).判斷和比較規則總結如下：
判斷和比較規則總結表
例如：使用方法：
SQL> select 1 from dual where null=null;
沒有查到記錄
SQL> select 1 from dual where null="';
沒有查到記錄
SQL> select 1 from dual where ''='';
沒有查到記錄
SQL> select 1 from dual where null is null;
1
---------
1
SQL> select 1 from dual where nvl(null,0)=nvl(null,0);
1
---------
1
4、null做一些算術運算，比如+，-，*，/等，結果 還是null，但是對於連接操作符||，null忽略,concat函數也忽略null
SQL> select null || 'abc' from dual;
NUL
---
abc
SQL> select concat(null,'abc') from dual;
CON
---
abc
SQL> select null+10 from dual;
NULL+10
----------
5、null相關函數規則
Oracle有nvl、nvl2、nullif、coalesce等函數專門處理null
5.1 nvl(expr1,expr2)
描述：如果expr1是null，那麼用expr2作為返回值，不是null則返回expr1.expr1與expr2一般是類型相同的，如果類型不同則會采用自動轉換，轉換失敗則報錯。
SQL*PLUS中數值類型右對齊，字符左對齊;通過第三條語句可以看出null和‘’還是有區別的。
SQL> select nvl(null,0) from dual;
NVL(NULL,0)
-----------
0
SQL> select nvl(to_char(null),0) from dual;
N
-
0
SQL> select nvl('',0) from dual;
N
-
0
5.2 nvl2函數
語法：nvl2(expr1,expr2,expr3)
描述：expr1如果是null，則返回expr3,否則返回expr2
expr2和expr3類型不同，expr3類型轉換為expr2類型
SQL> select nvl2(null,'1',2) from dual;
N
-
2
expr2為null，返回值類型和expr3一致
SQL> select nvl2(null,null,2) from dual;
NVL2(NULL,NULL,2)
-----------------
2
SQL> select nvl2(null,null,'2') from dual;
N
-
2
不同類型之間的轉換，自動轉換不成功，則報錯
SQL> select nvl2(null,1,'b') from dual;
select nvl2(null,1,'b') from dual
*
ERROR at line 1:
ORA-01722: invalid number(無效數字)
5.3 nullif函數
語法：nullif(expr1,expr2)
描述：判斷expr1和expr2是否相等，若相等則返回null，否則返回expr1.要求expr1與expr2類型必須相同
SQL> select nullif(1,3) from dual;
NULLIF(1,3)
-----------
1
SQL> select nullif(1,1) from dual;
NULLIF(1,1)
-----------
SQL> select nullif('1',1) from dual;
select nullif('1',1) from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
SQL> select nullif('ab','ab ') from dual;
NU
--
SQL> select nullif(null,1) from dual;
select nullif(null,1) from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CHAR
SQL> select nullif(to_char(null),'1') from dual;
N
-
5.4 coalesce函數
語法：coalesce(expr1,expr2,…,exprn)
描述：從左到右返回第一個為非null的值，若所有的列表元素都為null，則返回null。要求所有都必須為同一類型。
SQL> select coalesce(null,null,null) from dual;
C
-
SQL> select coalesce(null,1,2) from dual;
COALESCE(NULL,1,2)
------------------
1
SQL> select coalesce(1,'1',1) from dual;
select coalesce(1,'1',1) from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
6 null與索引
Oracle中的B*Tree索引，並不存儲全為null的列，
雖然在表中建立了符合UNIQUE 索引，但是全為null的行還是可以插入的，而不是全為null的重復行則不可以插入。因為在UNIQUE約束中，(null，null)和(null，null)是不同的，當然在其他一些情況，比如說分組、集合操作中都認為全是null是相等的
SQL> create table t(a number,b number);
Table created.
SQL> create unique index idx_t on t(a,b);
Index created.
SQL> insert into t values(null,null);
1 row created.
SQL> insert into t values(1,null);
1 row created.
SQL> insert into t values(null,1);
1 row created.
SQL> commit;
SQL> insert into t values(1,null);
insert into t values(1,null)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.IDX_T) violated
7 null的排序
order by默認升序(asc),這時候null是排在最後的，如果指定降序那麼null是排在最前面的，認為null最大。
但是可以用nulls first和nulls last進行調整。
SQL> select * from emp
2 order by comm asc;
SQL> select * from emp
2 order by comm desc;
SQL> select *from emp
2 order by comm asc nulls first;
SQL> select *from emp
2 order by comm desc nulls last;
8 null與性能的關系
Not null約束，定義約束是要付出消耗性能的代價的，由下面的測試可以看出雖然約束檢查的很快，但是有時候還是很消耗資源的，至少在這個例子上是這樣的，不需要not null約束，除非必要，不要亂定義約束。
SQL> set serveroutput on
SQL> declare
2 v_value number not null :=0;
3 start_time number;
4 end_time number;
5 begin
6 start_time :=DBMS_UTILITY.GET_TIME;
7 FOR i in 0..100000000 LOOP
8 v_value :=i;
9 end LOOP;
10 end_time :=DBMS_UTILITY.GET_TIME;
11 DBMS_OUTPUT.PUT_LINE(end_time-start_time);
12 END;
13 /
1043
PL/SQL procedure successfully completed.
SQL> declare
2 v_value number;
3 start_time number;
4 end_time number;
5 begin
6 start_time :=DBMS_UTILITY.GET_TIME;
7 FOR i IN 0..100000000 LOOP
8 v_value :=i;
9 end LOOP;
10 end_time :=DBMS_UTILITY.GET_TIME;
11 DBMS_OUTPUT.PUT_LINE(end_time-start_time);
12 END;
13 /
767
9 動態語句中的綁定變量與null
在PL/SQL中動態SQL和動態PL/SQL經常使用綁定變量，這個綁定變量有個要求，就是不能直接傳入字面量null值，因為PL/SQL中動態語句要求傳入的綁定變量必須是SQL類型，而字面量null是無類型的，null字面量傳入是不可以的。當然可以采用多種方法，如果一定要傳入null，則可以將null改為空字符串、TO_NUMBER,TO_CHAR,TO_DATE等函數進行轉換，或定義一個未初始化的變量、直接傳入變量等。
SQL> create table test(id number,name varchar2(10),birth date);
Table created.
SQL> insert into test values(1,'aa',SYSDATE);
1 row created.
SQL> insert into test values(null,'aa',SYSDATE);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> declare
2 v_sql varchar2(4000);
3 begin
4 v_sql :='update test set birth=:vbirth where id is null';
5 execute immediate v_sql using null;
6 commit;
7 end;
8 /
execute immediate v_sql using null;
*
ERROR at line 5:
ORA-06550: line 5, column 31:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored
SQL> declare
2 v_sql varchar2(4000);
3 begin
4 v_sql :='update test set birth=:vbirth where id is null';
5 execute immediate v_sql using '';
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
10 sqlserver 中null值介紹
創建示例表， Script 如下：
create table dbo. cassaba_null
(
column1 nvarchar ( 50) not null,
column2 nvarchar ( 50) null
)
go
insert into dbo. cassaba_null values ( '1' , null)
insert into dbo. cassaba_null values ( '2' , 'string' )
insert into dbo. cassaba_null values ( '3' , '' )
go
a. 使用 =null / <>null 默認情況下的確不能使用 =null / <> null 來判斷 null 值如此。實際上 SQL Server 可以 使用 SET ANSI_NULLS { ON | OFF } 設定來控制 =null / <>null 的行為。
當 SET ANSI_NULLS 為 ON 時，即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT 語句仍返回零行。
即使 column_name 中包含非空值，使用 WHERE column_name <> NULL 的 SELECT 語句仍會返回零行
但是當 SET ANSI_NULLS 為 OFF 時，等於 (=) 和不等於 (<>) 比較運算符不遵守 ISO 標准。
使用 WHERE column_name = NULL 的 SELECT 語句返回 column_name 中包含空值的行。
使用 WHERE column_name <> NULL 的 SELECT 語句返回列中包含非空值的行。
此外，使用 WHERE column_name <> XYZ_value 的 SELECT 語句返回所有不為 XYZ_value 也不為 NULL 的行。
b. 改變 null 值的連接行為
SQL Server 提供 SET CONCAT_NULL_YIELDS_NULL
{ ON | OFF } 來控制 null 與其它字符串連接的行為。
當 SET CONCAT_NULL_YIELDS_NULL 為 ON 時，串聯空值與字符串將產生 NULL 結果。例如， SELECT 'abc' + NULL 將生成 NULL 。
當 SET CONCAT_NULL_YIELDS_NULL 為 OFF 時，串聯空值與字符串將產生字符串本身(空值作為空字符串處理)。例如， SELECT 'abc' + NULL 將生成 abc 。
如果未指定 SET CONCAT_NULL_YIELDS ，則應用 CONCAT_NULL_YIELDS_NULL 數據庫選項的設置。
注：在 SQL Server 的未來版本中， CONCAT_NULL_YIELDS_NULL 將始終為 ON ，而且將該選項顯式設置為 OFF 的任何應用程序都將產生一個錯誤。
c. 變量的默認值與 null 值
命名一個變量後，如果沒有給它賦初始值，它的值就是 null 。有時候需要注意初始 null 值和通過 select 語句給變量後期賦 null 的區別。因為此 ‘null’ 非彼 ‘null’ 。
d. 子查詢中的 null
子查詢中出現的 null 值經常會被我們忽視，先查看下面的例子。
e. Case 語句中的 null
Case 中的 when 語句注意不要寫成 when null, 否則得不到想要的結果。
下面的第 1 條 sql 錯誤， 2 ， 3 正確。
f. 與 null 相關的函數 ISNULL ISNULL 檢測表達式是否為 NULL ，如果是的話替換 NULL 值為另外一個值 COALESCE COALESCE 函數返回指定表達式列表的第一個非 NULL 值 NULLIF 當指定的兩個表達式有相同值的時候 NULLIF 返回 NULL 值，否則返回第一個表達式的值
示例 1 ： set ansi_nulls on
declare @test1 nvarchar ( 10)
if ( @test1 = null)
select 1
else
select 2
---------------------------------------------------------------------
結果返回 2
示例 2 ： set ansi_nulls off
declare @test1 nvarchar ( 10)
if ( @test1 = null)
select 1
else
select 2
---------------------------------------------------------------------
結果返回 1
示例 3 ：
set ansi_nulls on
select * from dbo. cassaba_null where column2 != null
---------------------------------------------------------------------
無記錄返回
示例 4 ：
set ansi_nulls off
select * from dbo. cassaba_null where column2 != null
---------------------------------------------------------------------
返回第 2 ， 3 條記錄
如果不希望每次都判斷 null, 可以使用 isnull 函數來達到每次把 null 自動替換為空字符串進行連接的效果。
示例 5 ：
declare @test nvarchar ( 50)
select isnull ( @test, '' ) + 'extend'
go
select column1 + isnull ( column2, '' ) as column3 from dbo. cassaba_null
go
---------------------------------------------------------------------
示例 6 ：
declare @test nvarchar ( 50)
-- 無符合條件的語句 , 保持默認值 null
select @test= column2 from dbo. cassaba_null where column1 = '4'
-- 有符合條件的語句，返回的欄位值為 null ，並且賦給 @test
select @test= column2 from dbo. cassaba_null where column1 = '1'
如果後面的代碼使用 @test 的值是否為 null 來判斷有沒有匹配的記錄，則可能發生錯誤。碰到這種狀況，我們同樣可以使用 isnull 函數來避免這個問題。
select @test= isnull ( column2, '' ) from dbo. cassaba_null where column1 = '1'
如上面的語句，即使有匹配的記錄返回 null, 也會變成空字符串賦給 @test 了。這樣就把兩種情況區分開了。
示例 7 ： set ansi_nulls off
select * from cassaba_null a where a. column2 = ( select b. column2 from dbo. cassaba_null b where b. column1 = 1)
---------------------------------------------------------------------
不管上面 ansi_nulls 設置為 on 還是 off ，始終沒有記錄返回。我們修改一下查詢語句：
select * from cassaba_null a where a. column2 in ( select b. column2 from dbo. cassaba_null b where b. column1 = 1)
這樣，如果 ansi_nulls 設置為 on ， 則沒有記錄返回。 如果設置為 off ，則會返回一條記錄。
對於這種狀況，如果我們確定不需要返回 null 值記錄，則使用下面的 sql 語句：
select * from cassaba_null a where a. column2 in( select b. column2 from dbo. cassaba_null b where b. column1 = 1
and b. column2 is not null)
反之，使用下面的語句：
select * from cassaba_null a where a. column2 in( select b. column2 from dbo. cassaba_null b where b. column1 = 1
and b. column2 is not null) or a. column2 is null
10 Sql與oracle中null值的不同
a.在SQL Server中與oracle正相反，NULL值會被認為是一個無窮小的值，所以如果按照升序排列的話，則會被排在最前面
b. SQL Server和Oracle中對插入數據值包含空的處理有所差異，在SQL Server中，我們可以把表字段設計為非空，但我們仍然可以通過下面語句執行插入操作：
INSERT INTO Table (TestCol) VALUES(‘’)
其中的TestCol字段，在設計的時候，已經被設計為NOT NULL在sql server中，null和空格是不同的，也就是說，上面的語句插入的是一個空，但並不是NULL，只有當我們的插入語句中沒有該字段的時候，才會被認為違反非空的條件約束，如果把NULL翻譯成“空”的話，可能就會很容易搞混了。此外，如果我們的字段是INT類型的話，如果我們插入空的話，會得到一個0，也就是說，Sql server會自動幫我們處理對空格的轉化。
但是在Oracle中，這個便利便不存在了，必須嚴格按照規則來進行插入，也就是說，我們再想視圖通過插入空來滿足NOT NULL的設計約束，已經不能成功了，必須插入實實在在的內容才能符合NOT NULL的約束。
所以相對來說oracle要比sqlserver限制要多，也就相對嚴謹
