最近更新: 2006-02-28

SQL~~CASE, NULLIF() and ISNULL()

這是關於 SQL 中的 CASE 敘述的另一篇應用文章。前一篇為「SQL::使用 CASE 修飾資料內容,以便進行 group 操作」。

重點為:一、處理無意義的 NULL ;二、NULLIF() or ISNULL()

處理無意義的 NULL

在使用 outer join 查詢的場合中,其結果一定會有包含 NULL 值的欄位。然而,我們通常希望處理的是有意義的 0 或空字串,而不是無意義的 NULL 。例如,我們希望查詢結果的其中一個欄位內容,儲存的是左表格和右表格中各一個欄位相加的結果,但在 outer join 的場合中,兩表格欲相加之欄位,可能其中之一或兩者皆為 NULL ,此時相加的結果也會是 NULL (試試 "SELECT 1 + NULL" 這個查詢,其結果會是 NULL 。但通常我們希望傳回 1 。)。此時,我們需要先將 outer join 後的結果,利用 CASE 敘述加以修飾,將 NULL 改為有意義的 0 或空字串,以便後續運算。

case when 欄位 is NULL then 設計人員指定的有意義值 else 欄位 end
select left_table.barcode, 
left_table.input_qty, 
case when right_table.output_qty is NULL 
    then 0 
    else right_table.output_qty end as output_qty, 
case when right_table.output_qty is NULL 
    then left_table.input_qty 
    else left_table.input_qty - right_table.output_qty end as stock_qty 
from left_table 
left outer join right_table on right_table.barcode = left_tale.barcode

NULLIF() or ISNULL()

前述對 NULL 作修飾,以便後續處理的動作在實務上相當常用,因此有些 SQL DBMS 會提供函數形式的簡便寫法,例如 PostgreSQL 提供 NULLIF() ,而 Microsoft SQL Server 提供 ISNULL() ,此二者的用法完全一樣,當欄位值 (第一個參數) 為 NULL 時,傳回指定的替代值 (第二個參數) ,否則傳回欄位值。

-- PostgreSQL:
nullif( 欄位, 設計人員指定的有意義值 )

-- MS SQL Server:
isnull( 欄位, 設計人員指定的有意義值 )

ISNULL 在 SQL Standard 中是一個保留字,但目前並沒有定義標準用法,因此 MS SQL Server 提供的 ISNULL() 函數及用法,不見得可用在其它的 SQL DBMS 中,至少 PostgreSQL 就不這麼用。再者, ISNULL() 在字面上,往往被當作 is null 敘述的函數形式,易生誤解。總而言之, NULLIF() 及 ISNULL() 皆不可視為通用敘述,考慮到轉換、變更 DBMS 的情況,以及程式的移植性,還是建議使用較長的 CASE 敘述,而不要用 NULLIF()/ISNULL() 。

樂多舊網址: http://blog.roodo.com/rocksaying/archives/1186702.html

樂多舊回應
symis@ms62.url.com.tw(symis) (#comment-22064763)
Wed, 26 Oct 2011 15:04:09 +0800
case 欄位 is NULL then 設計人員指定的有意義值 else 欄位 end
...
MS SQL的指令好像一定要有when吧?
未留名 (#comment-22067033)
Thu, 27 Oct 2011 15:55:31 +0800
你真內行,這麼久的文章的打字錯誤都被你抓出來。

看前一篇「使用 CASE 修飾資料內容,以便進行 group 操作」使用的語法,那是正確的。
我這篇確實漏掉了 WHEN 。
p9908gis@yahoo.com.tw(CharlsePH) (#comment-22983820)
Tue, 16 Jul 2013 09:11:40 +0800
板大你好~!
可以請教是否when 後面的判斷可否依(條件1) and (條件二)來撰寫
有時不只想判斷一個條件~有這方面的應用嗎?
未留名 (#comment-22983838)
Tue, 16 Jul 2013 09:30:23 +0800
未留名 (#comment-22988264)
Fri, 19 Jul 2013 14:13:11 +0800
-- PostgreSQL:
nullif(資料1 ,資料2 )
只有當資料1跟資料2相等 才回傳null 否則回傳資料1
-- MS SQL Server:
isnull(資料1 ,資料2 )
當資料1為null時就回傳資料2 否則回傳資料1

兩個用法是不同的吧?