利用CASE WHEN條件式及OR邏輯運算子合併查詢邏輯

一些查詢需求中，當輸入特定查詢條件時，則依此條件進行篩選；若無任何查詢條件，則傳回所有資料。常見作法會將此查詢SQL獨立撰寫，再以UNION ALL（切記勿用UNION，但建議自行測試）指令將兩段查詢語法組合。語法如下，煩請自行測執行。

--建立測試資料

SELECT * INTO #Store

FROM (VALUES ('DDR', 50,  '光華店')

           , ('DDR2', 10,  '公館店')

           , ('DDR4', 80,  '站前店')

           , ('DDR2', 10,  '公館店')          

     ) Store (Prod, Qty, Loc)

-----------

DECLARE @Prod varchar(10)            --測試1: 未輸入資料

--DECLARE @Prod varchar(10) = 'DDR2' --測試2: 輸入資料

--1. 未輸入Prod

SELECT Prod, Qty, Loc

FROM #Store

WHERE 1=1

     AND @Prod IS NULL

UNION ALL　--使用UNION將僅剩3筆(DDR2少1筆) 

--2. 輸入Prod

SELECT Prod, Qty, Loc

FROM #Store

WHERE 1=1

     AND Prod =@Prod

前述兩段不同條件以聯集運算合併之SQL，將利用CASE WHEN條件式及OR邏輯運算子即可合併。其中，條件式(1)採用CASE WHEN條件式判斷查詢條件是否已輸入，以OR邏輯運算子結合條件式(2)查詢產品之條件，SQL及結果如下:

	SQL	Result
測試1 未輸入	--測試1: 未輸入資料 DECLARE @Prod varchar(10) SELECT * FROM #Store WHERE 1= CASE WHEN @Prod IS NULL THEN 1 END --(1)成立       OR Prod = @Prod                       --(2)不成立
測試2 輸入	--測試2: 輸入資料 DECLARE @Prod varchar(10) = 'DDR4' SELECT * FROM #Store WHERE 1= CASE WHEN @Prod IS NULL THEN 1 END --(1)不成立       OR Prod = @Prod                       --(2)成立

測試1（未輸入）中，運算式(1)結果為1=1即成立，條件式(2)則不成立，因任何資料不等於NULL 包含NULL本身，由於兩運算式是使用OR邏輯運算子結合，雖不成立但可忽略，將傳回所有資料(4筆)；測試2（輸入）中，運算式(1)為1=NULL即不成立，條件式(2)則具有完全決定權，將回傳1筆DDR4之產品。

查詢若具有部門代碼時，則列出該部門員工，若無則為全部

當查詢條件中明確指出特定部門代碼，則列出該部門之員工資料，若無則查詢出所有員工資料，本範例重點在於CASE WHEN及OR邏輯判斷式之應用，將以僅以MSSQL為例，撰寫Stored Procedure以進行測試，如下。

部門	SQL	執行結果	說明
全部	EXEC ListEmp ''		部門代碼參數為空時，代表列出所有員工資料。
IT部門	EXEC ListEmp 'IT'		部門代碼為指定部門時(ex:IT)，則列出該員工資料。

ListEmp程式碼（MSSQL）

SQL	說明
CREATE PROCEDURE ListEmp ( @Dept_No varchar(10) ) AS BEGIN SELECT * FROM Emp WHERE 1=1       AND (1 = CASE WHEN @Dept_No='' THEN 1 ELSE 0 END --重點1, CASE WHEN            OR DEPT_NO =@Dept_No                          --重點2, OR           ) END	SP是應用SELECT敍述句進行查詢，並直接將結果傳回，為MSSQL中相當獨特且簡便之作法。 此語法開發上雖簡便，但應用上則有所限制，難以搭配其他查詢語法使用，2005以上版本建議改用FUNCTION，結果是以TABLE方式傳回。

將條件式整理如下表（重點1）

#	條件式	ALL	IT	說明
1	1 = CASE WHEN @Dept_No='' THEN 1 ELSE 0 END	1	0	當部門代碼為空時，CASE WHEN運算結果為1，1=1成立；指定部門時則1=0不成立
2	DEPT_NO =@Dept_No	0	1	當部門代碼為空時，條件不成立。 部門代碼不為空時，條件成立。
OR		1	1	OR運算特性，任何條件成立則此運算成立。 當#1（即1=1）成立，則不論#2結果，均成立。 當#1（即1=0）不成立，但#2成立，仍成立。

再將2個條件式以OR進行判斷，不管是全部或IT部門均有其中一個條件成立（重點2）。

對於此範例，大部分作法會將兩種情況撰寫成2段類似之SQL，以IF…ELSE進行判斷、或以UNION ALL方式將兩段結合，或者Dynamic SQL動態組合查詢語法並執行。前述常用方法，SQL將變得非常龐大，邏輯或查詢欄位異動時需同時變更兩段增加維護成本，可使用CASE WHEN判斷查詢類型，以促使（enable）或取消（disable）另一查詢條件是否使用。應用此方法可以大幅縮減SQL複雜度（行數），不管ORACLE或MSSQL均可使用。

測試資料（以MSSQL 2008之Values方法建立）

SELECT * INTO Emp

FROM (VALUES ('A01', '麥坤',  '200', 'IT')

           , ('A02', '拖線',  '201', 'IT')

           , ('A03', '邁爵士', '901', 'MA')

           , ('A04', '郝莉莉', '801', 'MK')

      ) AS EMP(EMP_NO, EMP_NAME, EXT, DEPT_NO)

查詢時一併列出相關系所

數年前資訊科技產業蓬勃發展，資訊各領域紛紛獨立切割成立新的系所，相關科系在查詢時能將一併顯示，如資工及資料系兩系，不論查詢其中任何一筆資料，兩者均同時顯示。以下將依相關系統定義是否定義於資料表中分別討論:

1.  簡單版本-SQL中

OR可額外增加判斷空間（條件），而條件判斷式中之資料則由CASE WHEN所創造，SQL及兩種測試之結果，如下：

#	SQL	結果
1	SELECT * FROM      (         SELECT '資工系' Dept_Id      UNION ALL      SELECT '資科系'      UNION ALL      SELECT '財金系'      UNION ALL      SELECT '械械系'      UNION ALL      SELECT '資管系'      UNION ALL      SELECT '企管系'      ) A WHERE 1=1 AND (Dept_Id = '資科系' -- 判斷式1    OR Dept_Id = CASE '資科系' -- 判斷式2--重點                        WHEN '資工系' THEN '資科系'                        WHEN '資科系' THEN '資工系'                   END   　 )	判斷式1，查詢資科系；判斷式2則利用以CASE WHEN轉換為查詢資工系。
2	WHERE 1=1 AND (Dept_Id = '械械系'    OR Dept_Id = CASE '械械系' --重點                                        WHEN '資工系' THEN '資科系'                  WHEN '資科系' THEN '資工系'                   END        )

判斷式1，是基本判斷式，不解釋。重點在於判斷式2的產生，將使用簡單式-CASE語法，查詢條件置於CASE判斷式中並以WHEN進行轉換成對應之相關系所，即可產生另一組查詢條件式，兩組判斷式使用OR進行邏輯組合，任何條件成立均可，以此即可同時查詢相關系所之資料。

2.  定義版本-資料表中

前述對應資料將建立成資料表，依關聯定義為一筆（單向）/兩筆（雙向）撰寫出以下2組SQL，如下：

#	SQL	說明
1	SELECT COALESCE(R.Dept_Id, D.Dept_Id) Dept_Id FROM  #Dept D      LEFT OUTER JOIN #Rel_Dept R          ON (D.Dept_Id = R.Dept_Id               OR D.Dept_Id = R.Rel_Dept_Id ) WHERE 1=1       AND D.Dept_Id    ='資工系'	ü  適用於兩者關聯之定義為兩筆（資工-資科、資科-資工）。 ü  COALESCE為ANSI語法，MSSQL可用ISNULL、ORACLE則為NVL。
2	SELECT * FROM #Dept WHERE 1=1      AND Dept_Id='資工系' UNION--重複值剔除 SELECT CASE WHEN R.Dept_Id = D.Dept_Id THEN 　　　　　　　　　R.Rel_Dept_Id 　　　　　　　ELSE 　　　　　　　　　R.Dept_Id 　　　　END FROM #Rel_Dept R, #Dept D WHERE 1=1 　　AND D.Dept_Id='資工系'  　　AND (D.Dept_Id = R.Dept_Id           　OR D.Dept_Id = R.Rel_Dept_Id          )	ü  兩者關聯之定義為一筆/兩筆（資工-資科、資科-資工）均適用。 ü  關聯之定義為兩筆時，資料會有重複現象，可使用UNION自動剔除。

資料產生 (以MSSQL為例，ORACLE改為CTAS方式即可)

--1.Dept

SELECT '資工系' Dept_Id

        INTO #Dept

UNION ALL

SELECT '資科系'

UNION ALL

SELECT '財金系'

UNION ALL

SELECT '械械系'

UNION ALL

SELECT '企管系'

--2.Rel_Dept

DROP TABLE #Rel_Dept

SELECT '資工系' Dept_Id, '資科系' Rel_Dept_Id

         INTO #Rel_Dept

UNION ALL

SELECT '資科系' Dept_Id, '資工系'

UNION ALL

SELECT '財金系', '企管系'--刻意建立單向對應關係，可嘗試測試