CASE運算式

與一般程式語言一樣，SQL也提供條件判斷運算式，CASE運算式可應用於SQL指令中，根據特定條件（或組合）進行分岐處理，大幅提升SQL指令整體的運算能力。

運算概念為WHEN條件式為真時，則傳回THEN之後的值，在WHEN的組合中，其判斷優先順序為由前至後，並將傳回最先符合條件的值；若在WHEN組合中均未符合，則將傳回ELSE的預設值，因CASE運算式中，並不一定需要有ELSE子句，若均未符合任何運算式，系統傳回NULL值。CASE指令可分成簡單型CASE子句（Simple Case Statement）及搜尋式CASE子句（Searched CASE Form）兩種格式，以下整理出其語法及比較，如下表所示：

引數	簡單型CASE	搜尋型CASE
語法	CASE <expr>     WHEN <val 1> THEN <result 1>     WHEN <val 2> THEN <result 2>       ...     WHEN <val N> THEN <result N>     [ELSE <default>] END	CASE     WHEN <bool expr 1> THEN <result 1>     WHEN <bool expr 2> THEN <result 2>       ...     WHEN <bool expr N> THEN <result N>      [ELSE <default>] END
邏輯運算	較簡單	複雜
運算式（expr）	一組	多組
WHEN判斷	單一值	可多組邏輯運算組合
NULL值	於運算式（expr）中處理	於判斷式（WHEN）中處理

1.    簡單型CASE子句（Simple Case Statement）

簡單型CASE語法有個特點，CASE後面直接連運算式（expr），判斷式（WHEN）中只能為單一值，範例如下：

SELECT  CASE Cust_Name                WHEN '張先生' THEN 1                WHEN '曹先生' THEN 2                ELSE 3         END UserNo 　　　 , CASE SUBSTRING(Cust_Name2, 2)               WHEN '先生'' THEN '男'               WHEN '小姐HEN '女'               ELSE 'N.A'         END Sex FROM Orders

2.    搜尋式CASE子句（Searched CASE Form）

搜尋式CASE語法有個特點，CASE後面直接連判斷式（WHEN），各組判斷式（WHEN）有其運算式（expr）或組合。另外，在搜尋式CASE中的各組運算式，其運算邏輯可進行較複雜的運算，支援大小比較（如：=、>、>=、<、<=及BETWEEN...AND）、NULL值判斷（如：IS NULL、IS NOT NULL）、IN /NOT IN、模糊比對（LIKE），以及邏輯組合（如：AND、OR）等常用的搜尋運算子功能。

以下將以性別判斷、折扣、VIP客戶等幾種範例，說明如何應用搜尋運算子於搜尋式CASE子句，SQL語法如下：

SELECT         CASE               WHEN Cust_Name = '張先生' THEN 1               WHEN Cust_Name = '曹先生' THEN 2               ELSE 3          END UserNo         , CASE               WHEN Cust_Name LIKE '%先生' THEN '男'               ELSE '女'         END Sex      ,  CASE              WHEN QTY BETWEEN  0 AND 150 THEN 0.10              WHEN QTY BETWEEN 150 AND 300 THEN 0.15              WHEN QTY >=300                THEN 0.20         END Discount , CASE           WHEN Cust_Name IN ('張先生', '曹先生')  THEN 1           ELSE 0       END VIP FROM Orders

NULL值處理

同樣的，在CASE運算式中，若將與NULL進行比較運算時，仍需注意NULL的特性。下列將以一組SQL以及產生的結果，分析兩種型式的CASE運算式的差異。

SELECT VAL      , CASE VAL WHEN NULL    THEN 1 ELSE 0 END  AS "Simple (Is Null)"      , CASE WHEN VAL IS NULL THEN 1 ELSE 0 END AS "Search (Is Null)" FROM (       SELECT 1 VAL -- FROM DUAL       UNION ALL       SELECT 2 VAL -- FROM DUAL       UNION ALL       SELECT NULL VAL -- FROM DUAL       ) D

VAL	Simple (Is Null)	Search (Is Null)
1	0	0
2	0	0
NULL	0	1

由上得知，當值為NULL時，以簡單型CASE運算式所得到的結果為錯誤，使用搜尋式CASE答案才正確，對於NULL值的運算，必須是IS NULL或IS NOT NULL兩種型式。

但在MSSQ可以變更ANSI_NULLS資料庫選項為OFF(SET ANSI_NULLS OFF)，則可視= NULL為IS NULL而<> NULL為IS NOT NULL，但因這違反資料庫對NULL處理一般規則，故不建議使用。

CASE運算式可提供SQL命令在條件處理的能力，使SQL命令更具彈性，不少資料庫使用者誤認CASE運算式僅可應於用SELECT運算式中，後續範例中將以下列不同的應用型態，如篩選條件（WHERE）、排序（ORDER BY）、更新（UPDATE）或其他情境下使用。

ORACLE

ORACLE尚提供了另一種函數為DECODE，其語法如下：

DECODE(<expr>, <val 1>, <result 1>, <Val 2>, <Result 2>, …, <default>)

DECODE相當類似前述所提的簡單型CASE子句，以下將DECODE方式重新改寫，其結果與前述相同，SQL語法如下：

SELECT DECODE(Cust_Name,                     '張先生', 1,                     '曹先生', 2,                               3                 ) UserNo FROM Orders

DECODE與簡單型CASE子句的差異是在於NULL值的處理，若NULL為DECOCE的判斷條件之一，用NULL即可代表，而不是使用IS NULL的方式，使用方法如下所示：

DECODE(欄位名稱, NULL, 1 , 0)

MSSQL

MSSQL2012提供類似EXCEL的IIF指令，語法如下，但對於NULL值處理仍是要以IS NULL / IS NOT NULL判斷。

IIF ( boolean_expression, true_value, false_value )

    

另外，尚有功能近似之CHOICE函數由從清單傳回索引(Index)指定之項目。

CHOOSE ( index, val_1, val_2 [, val_n ] )