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 或空字串，以便後續運算。

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() 。