Oracle: Empty string and NULL allowd column

0. 前言

最近的專案又回到 Oracle, 印象中, 在作空字串與 Null 的處理時, 有一些要注意的地方. 所以, 就整理了本篇及 下一篇 的文章.

茲區分以下幾個部份作說明.

• 1. SQL 指令 (NULL)
• 2. 輸出結果 (NULL)
• 3. 個別執行 SELECT 指令的截圖 (NULL)
• 4. 結論 (NULL)
• 5. 參考文件

1. SQL 指令 (NULL)

DROP TABLE USER1.TESTNULL;
/
CREATE TABLE USER1.TESTNULL (
       ID    NUMBER(3,0)     DEFAULT 0 NOT NULL,
    NAME CHAR(10)    NULL,
    ADDRESS    NVARCHAR2(20)    NULL,    
   CONSTRAINT PK_TESTNULL PRIMARY KEY ( ID )
);
/
INSERT INTO TESTNULL(ID, NAME, ADDRESS)  
VALUES (1, 'JASPER', 'TAIPEI');
-- NAME 及 ADDRESS 給預設值, 也就是 NULL
INSERT INTO TESTNULL(ID)
VALUES (2);
-- NAME 給預設值, ADDRESS 填入 5  個 bytes 的空白
INSERT INTO TESTNULL(ID, ADDRESS)
VALUES (3, LPAD(' ', 5, ' '));
-- NAME 及 ADDRESS 給空字串.
INSERT INTO TESTNULL(ID, NAME, ADDRESS)
VALUES (4, '', '');
/
-- 情境1 -------------------------
PROMPT -- [情境1] -------------------------;
SELECT ID
    , '[' || NAME || ']' AS NAME1
    , '[' || ADDRESS || ']' AS ADDRESS1
FROM TESTNULL;
-- 情境2 -------------------------
PROMPT -- [情境2] -------------------------;
SELECT ID
    , LENGTH(NAME) AS LEN1
    , LENGTH(ADDRESS) AS LEN2
-- CASE NAME WHEN NULL THEN -1 ELSE LENGTH(NAME) END AS LEN1 ,
-- CASE ADDRESS WHEN NULL THEN -1 ELSE LENGTH(ADDRESS) END AS LEN2
FROM TESTNULL;
-- 情境3.1 -------------------------
PROMPT -- [情境3.1] -------------------------;
-- 預期結果: 2, 3, 4
SELECT ID, NAME, ADDRESS
FROM TESTNULL
WHERE NAME IS NULL;
-- 情境3.2 -------------------------
PROMPT -- [情境3.2] -------------------------;
-- 預期結果 2, 3, 4  ? => 錯, 答案是查不到資料
SELECT ID, NAME, ADDRESS
FROM TESTNULL
WHERE NAME = '';
-- 情境4.1 -------------------------
PROMPT -- [情境4.1] -------------------------;
-- 預期結果: 2, 4
SELECT ID, NAME, ADDRESS
FROM TESTNULL
WHERE ADDRESS IS NULL;
-- 情境4.2 -------------------------
PROMPT -- [情境4.2] -------------------------;
-- 預期結果 2, 4 ? => 錯, 答案是查不到資料
SELECT ID, NAME, ADDRESS
FROM TESTNULL
WHERE ADDRESS = '';
/    
                

2. 輸出結果 (NULL)

Table USER1.TESTNULL 已刪除.
Table USER1.TESTNULL 已建立.

1 資料列 已插入.
1 資料列 已插入.
1 資料列 已插入.
1 資料列 已插入.

-- [情境1] -------------------------
        ID NAME1        ADDRESS1              
---------- ------------ ----------------------
         1 [JASPER    ] [TAIPEI]              
         2 []           []                    
         3 []           [     ]               
         4 []           []                    

-- [情境2] -------------------------
        ID       LEN1       LEN2
---------- ---------- ----------
         1         10          6
         2                      
         3                     5
         4                      

-- [情境3.1] -------------------------
        ID NAME       ADDRESS             
---------- ---------- --------------------
         2                                
         3                                
         4                                

-- [情境3.2] -------------------------
未選取資料列

-- [情境4.1] -------------------------
        ID NAME       ADDRESS             
---------- ---------- --------------------
         2                                
         4                                

-- [情境4.2] -------------------------
未選取資料列
                

3. 個別執行 SELECT 指令的截圖 (NULL)

[情境1]

[情境2]

[情境3.1]

[情境3.2]

[情境4.1]

[情境4.2]

4. 結論 (NULL)

不論是 CHAR or VARCHAR2 只要欄位是允許 NULL:
(1) 在 INSERT, UPDATE 時, 填入空字串會被轉為 NULL 值.
(2) 在 WHERE 條件裡, 依 參考文件3, 4, 5, 任何欄位內容值 與 NULL 作比較, 會得到 Unknown 的結果.

本例中的第2, 4筆的 ADDRESS 欄位內容值為 NULL, 與空字串作比較 (不確定是否有將空字串轉為 NULL), 其結果應該是 Unknown, 所以不會被顯示出來.

也就是只能用 IS NULL, 或者用反向的 IS NOT NULL, 而不能用 = '' 作為條件判斷.

5. 參考文件

• 01. 黑暗執行緒, KB-詭異的NOT IN查詢，原來是NULL搞鬼
• 02. 黑暗執行緒, ORACLE, 你把空字串怎麼了?
• 03. 黑暗執行緒, KB－當 WHERE AND/OR 條件遇上 NULL
• 04. Oracle Help Center, Database SQL Reference: Nulls
• 05. Oracle Help Center, SQL Language Reference: Nulls