[DB] JOIN 帶有 NULL 值欄位

有時因為某些因素，在表格做關聯時會有 JOIN 值為 NULL 的欄位。以下這個例子為例：

表格 Timeable: 列出每個時間點發車的列車會停靠的站點：

Time  Stop1    Stop2    Stop3      Stop4    Stop5
----  -------  -------  ---------  -------  ----------
0800  Taipei   Taoyuan  Taichung   Tainan   Kaohsiung
0820  NULL     Taoyuan  Taichung   NULL     Kaohsiung
0830  Taipei   NULL     Taichung   NULL     Kaohsiung
0840  Taipei   NULL     Taichung   Tainan   NULL
0855  NULL     Taoyuan  Taichung   NULL     Kaohsiung
0910  Taipei   NULL     Taichung   NULL     Kaohsiung

表格 CarType: 列出不同列車類型會停靠的站點：

CarType  Stop1    Stop2     Stop3     Stop4    Stop5
-------  -------  --------  --------  -------  ----------
      A  Taipei   Taoyuan   Taichung  Tainan   Kaohsiung
      B  Taipei   Taoyuan   Taichung  Tainan   Kaohsiung
      A  NULL     Taoyuan   Taichung  NULL     Kaohsiung
      C  Taipei   NULL      Taichung  NULL     Kaohsiung
      B  Taipei   NULL      Taichung  Tainan   NULL
      B  NULL     Taoyuan   Taichung  NULL     Kaohsiung
      A  Taipei   NULL      Taichung  NULL     Kaohsiung

依照最直覺的想法，應該會這麼下 SQL：

select t.Time, c.CarType, t.Stop1, t.Stop1, t.Stop3, t.Stop4, t.Stop5 from Timeable t
left join CarType c on
t.Stop1 = c.Stop1 and t.Stop2 = c.Stop2 and t.Stop3 = c.Stop3 and t.Stop4 = c.Stop4 and t.Stop5 = c.Stop5

產生的結果，不覺得怪怪的嗎?

Time  CarType  Stop1   Stop2   Stop3     Stop4   Stop5
----  -------  ------  ------  --------  ------  ---------
0800        A  Taipei  Taipei  Taichung  Tainan  Kaohsiung
0800        B  Taipei  Taipei  Taichung  Tainan  Kaohsiung
0820     NULL  NULL    NULL    Taichung  NULL    Kaohsiung
0830     NULL  Taipei  Taipei  Taichung  NULL    Kaohsiung
0840     NULL  Taipei  Taipei  Taichung  Tainan  NULL
0855     NULL  NULL    NULL    Taichung  NULL    Kaohsiung
0910     NULL  Taipei  Taipei  Taichung  NULL    Kaohsiung

問題就在於關聯條件設定時，有些欄位是 NULL

在 SQL Server 中，有兩種解法：
解法一：土法煉鋼法，條件寫多一點吧！

select t.Time, c.CarType, t.Stop1, t.Stop1, t.Stop3, t.Stop4, t.Stop5 from Timeable t left join CarType c on
(t.Stop1 = c.Stop1 or t.Stop1 is NULL and c.Stop1 is NULL) and
(t.Stop2 = c.Stop2 or t.Stop2 is NULL and c.Stop2 is NULL) and
(t.Stop3 = c.Stop3 or t.Stop3 is NULL and c.Stop3 is NULL) and
(t.Stop4 = c.Stop4 or t.Stop4 is NULL and c.Stop4 is NULL) and
(t.Stop5 = c.Stop5 or t.Stop5 is NULL and c.Stop5 is NULL)

解法二：利用 2008 版本提供的 coalesce 方法：

select t.Time, c.CarType, t.Stop1, t.Stop1, t.Stop3, t.Stop4, t.Stop5 from Timeable t left join CarType c on
(coalesce(t.Stop1, '') = coalesce(c.Stop1, '')) and
(coalesce(t.Stop2, '') = coalesce(c.Stop2, '')) and
(coalesce(t.Stop3, '') = coalesce(c.Stop3, '')) and
(coalesce(t.Stop4, '') = coalesce(c.Stop4, '')) and
(coalesce(t.Stop5, '') = coalesce(c.Stop5, ''))

coalesce 中，會將 NULL 值以一個指定的值替代，所以最好塞入獨特且不重複的字元進去

所得到的答案：

Time  CarType  Stop1   Stop2   Stop3     Stop4   Stop5
----  -------  ------  ------  --------  ------  ---------
0800        A  Taipei  Taipei  Taichung  Tainan  Kaohsiung
0800        B  Taipei  Taipei  Taichung  Tainan  Kaohsiung
0820        A  NULL    NULL    Taichung  NULL    Kaohsiung
0820        B  NULL    NULL    Taichung  NULL    Kaohsiung
0830        C  Taipei  Taipei  Taichung  NULL    Kaohsiung
0830        A  Taipei  Taipei  Taichung  NULL    Kaohsiung
0840        B  Taipei  Taipei  Taichung  Tainan  NULL
0855        A  NULL    NULL    Taichung  NULL    Kaohsiung
0855        B  NULL    NULL    Taichung  NULL    Kaohsiung
0910        C  Taipei  Taipei  Taichung  NULL    Kaohsiung
0910        A  Taipei  Taipei  Taichung  NULL    Kaohsiung