[SQL] JOIN ON 條件中使用 CASE 來判斷

此論壇問題是要利用 @TempA 的 A2 欄位 JOIN @TempB 的 B1 欄位，假如 A2 欄位資料為 AA+ JOIN 不到資料，就必須改為 AA- 去JOIN，直到找不到資料。

DECLARE @TempA TABLE(A1 char(4),A2 char(3))
DECLARE @TempB TABLE(B1 char(3),B2 char(10))
INSERT INTO @TempA VALUES('0001','AA+')
INSERT INTO @TempA VALUES('0002','BB+')
INSERT INTO @TempB VALUES('AA-','值1')
INSERT INTO @TempB VALUES('BB+','值2')
INSERT INTO @TempB VALUES('BB-','值3')

-- 會產生無法解釋的非預期結果
SELECT A.A1,A.A2,B.B1,B.B2 
FROM @TempA AS A
  LEFT JOIN @TempB AS B ON
    (
      CASE
        WHEN (A.A2 = B.B1) THEN A.A2 
        ELSE SUBSTRING(A.A2,1,2) + '-' END
    ) = B.B1

-- 建議用法
SELECT 
  A.A1 , 
  A.A2 , 
  ISNULL(B1.B1,B2.B1) AS B1, 
  ISNULL(B1.B2,B2.B2) AS B2
FROM @TempA AS A 
  LEFT JOIN @TempB AS B1 ON A.A2 = B1.B1
  LEFT JOIN @TempB AS B2 ON SUBSTRING(A.A2,1,2) + '-' = B2.B1

在 JOIN ON 使用 CASE WHEN 來判斷連結條件或是多個條件比較，會產生無法解釋的非預期結果，要盡量避免此用法；在此利用多個 LEFT JOIN 搭配 ISNULL() OR COALESCE() 來達到目的，確保結果產生。

MSDN ON 說明

ON 為聯結所根據的條件，指定聯結所根據的條件。條件可以指定任何述詞 (雖然通常都是使用資料行和比較運算子)

當條件指定資料行時，這些資料行不必有相同的名稱或相同的資料類型；不過，如果資料類型不同，它們必須相容的類型或 SQL Server 可以利用隱含方式轉換的類型。 如果資料類型無法利用隱含方式轉換，條件必須藉由 CONVERT 函數，利用明確方式轉換資料類型。

ON 子句中可以有僅涉及其中一個聯結資料表的述詞。 這類述詞也可以在查詢的 WHERE 子句中。 雖然這類述詞的放置不會影響 INNER 聯結，不過，如果涉及 OUTER 聯結，就可能會造成不同的結果。 這是因為 ON 子句中的述詞會套用至聯結之前的資料表，但在語意上，WHERE 子句則套用至聯結的結果。

參考資料
• 論壇問題出處
• Conditional Joins in SQL Server
• MSDN FROM