SQL中的null，SQLnull

SQL中的null，SQLnull

對Oracle SQL中的null小結如下：

1.1       null

Ø null 值計算的結果仍是null

Ø null 是一個未分配的、未知的，或不適用的值

Ø null不是0，也不是空格

 

1.1.1       null值的運算

null參數的算術運算式的結果仍然為null

SQL>  select '*'||1/null||'*' res1,'*'||1*null||'*' res2, '*'||(1-null)||'*' res3 from dual;

RE RE RE

-- -- --

** ** **

SQL>select null/0 from dual;

    NULL/0

----------

SQL>select 1/0 from dual;

select1/0 from dual

        *

ERROR atline 1:

ORA-01476:divisor is equal to zero

模數

SQL>select  '*'||mod(9,null)||'*' res fromdual;

RE

--

**

 從NULL/0沒有報錯的情況，我們可以推測出，Oracle發現算術運算式中有null時，並沒有真正地計算該運算式，而是直接返回了null。

SQL>select 1 from dual where null!=null;

no rowsselected

SQL>select 1 from dual where null = null;

no rowsselected

SQL>select 1 from dual;

                 1

----------

                 1

不管是null!=null 還是null=null計算的結果都為false(實際上是返回了null, null在邏輯中表現為false)，可見，不管是算術運算還是邏輯運算，只要null參與，結果就是null

1.1.2       null值與字串串連

null與字元型欄位串連時，表現為什麼都沒有""，相當於零個字元

 SQL>select '*'||null||'*' from dual;

'*

--

**

注意： 如果在實驗時出現中文亂碼，請確認資料庫服務nls_database_parameters，伺服器echo $NLS_LANG/ set NLS_LANG,以及用戶端v$nls_parameters的編碼設定是否一致。

1.1.3       null的查詢方式

SQL> select * from emp where comm is null;

其他的查詢方式會先過濾過列的null值所在行的記錄，即null不參與is null /is not null之外的查詢

 

SQL>select count(*) from emp;

  COUNT(*)

----------

                14

SQL>select count(*) from emp where comm<=500;

  COUNT(*)

----------

                 3

SQL>select count(*) from emp where comm>500;

  COUNT(*)

----------

                 1

 

1.1.4       null所在列的排序

在order by 排序中，null值最大

SQL>select ename, comm from emp order by comm;

 

TURNER                                    0

ALLEN                      300

WARD                      500

MARTIN                                1400

SCOTT

KING

ADAMS

JAMES

FORD

MILLER

BLAKE

JONES

SMITH

 

CLARK

14 rowsselected.

1.1.5       is null不走索引

在SQL的最佳化中有時會遇到SQL語句有索引也應該走索引，但最終卻沒有走索引的情況，這裡的is null就是其中的一種情況。

create table n1(sid integer,sname varchar2(120));

插入1W條記錄，第1W條的sname值為null

begin

  for i in 1..9999 loop

    insert into n1 values(i,'name'||i);

    if mod(i,100)=0 then

      commit;

    end if;

  end loop;

  insert into n1(sid) values(10000);

  commit;

end;

查詢sname列值走的是索引範圍掃描

SQL> explain plan for select * from n1 where sname = 'name1';Explained.SQL> select * from table(dbms_xplan.display);Plan hash value: 3644017351--------------------------------------------------------------------------------| Id  | Operation | Name | Rows| Bytes | Cost (%CPU)| Time|--------------------------------------------------------------------------------|   0 | SELECT STATEMENT ||     1 |    75 |     2   (0)| 00:00:01||*  1 |  INDEX RANGE SCAN| N1_SNAME_IND |     1 |    75 |     2   (0)| 00:00:01|--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("SNAME"='name1')Note-----   - dynamic sampling used for this statement (level=2)

is null方式查詢，雖然sname中為null的記錄1W行中只有一行，但還是沒有走索引，也就是說is null不走索引。

SQL> explain plan for select * from n1 where sname is null;Explained.SQL> select * from table(dbms_xplan.display);Plan hash value: 2416923229--------------------------------------------------------------------------| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  | |     1 |    75 |     9   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| N1 |     1 |    75 |     9   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("SNAME" IS NULL)Note-----   - dynamic sampling used for this statement (level=2)17 rows selected.

如果實際情況確實需要is null查詢走索引呢？可通過建立聯合索引的方式來實現。

drop index n1_sname ;

create index n1_sname_ind on n1(sname,sid);

 

SQL> explain plan for select * from n1 where  sid is not null and sname is null ;

SQL> select * from table(dbms_xplan.display);Plan hash value: 3644017351-------------------------------------------------------------------------------| Id  | Operation | Name | Rows| Bytes | Cost (%CPU)| Time|-------------------------------------------------------------------------------|   0 | SELECT STATEMENT ||     1 |    75 |     3   (0)| 00:00:01||*  1 |  INDEX RANGE SCAN| N1_SNAME_IND |     1 |    75 |     3   (0)| 00:00:01|-------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("SNAME" IS NULL)       filter("SID" IS NOT NULL)Note-----   - dynamic sampling used for this statement (level=2)18 rows selected.

可以看到建立聯合索引後，SQL查詢所耗費的資源明顯降低。

需要注意的是我們查詢最頻繁最經常使用列，比如sname要放在聯合索引的第一列；同時要走聯合索引，需要where後面的條件出現聯合索引包含的所有的欄位，這也是為什麼加了sid這個欄位的原因。

 

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。