五餅二魚工作室

在資料庫應用程式開發中，很常會使用到 Master-Detail 的資料結構，有些時候我們內部會習慣稱頭和身的資料，在這種架構當中，用 Master 的 Key 值去 Detail 內，應該都會找到對應的資料，但不會有 Master 資料已經刪除，但還有對應的 Deaail 資料還存在的狀況。

一般為了預防那樣的情況，最常見的是利用 Foreign Key 去設定關聯，或者是設定 Cascade Update & Cascade Delete 來解決。今天我們先不來討論如何解決這樣的狀況，我們先來看如果有發生這樣異常的時候，我們要如何找出有異常的資料。

在下面的範例中，我們的 Master 的資料表是 COPTG， PK 欄位是 TG001 ( 單據類別 ) 和 TG002 ( 單據號碼 )。Detail 的資料表是 COPTH，PK 的欄位是 TH001 ( 單據類別 )  , TH002 ( 單據號碼 ) , TH003 ( 序號 )，要找出 COPTH 中是否有資料沒有存在的對應 COPTG 的資料，因此我們可以有幾種寫法來做處理。

1. 利用 LEFT JOIN : 這個是很常使用的一種手法，利用 Detail 的資料表去關聯回 Master 的資料，因此要找出不存在的，因此要讓他去做 LEFT JOIN 之後，再用 where 去過濾沒有關連到 master 的資料，因此我們判斷 master 的 KEY 欄位是否為 null。

select TH001, TH002 
from COPTH
left join COPTG ON TG001=TH001 AND TG002=TH002
where TG001 IS NULL

2. 利用 RIGHT JOIN：跟使用 LEFT JOIN 的方式很類似，只是 LEFT JOIN 的時候，我們是從 Detail 去看 Master，而當採用 RIGHT JOIN 的時候，則是抓 Master 為主。雖然這兩種方式想法的概念有點不同，但仔細看一下他們的執行計畫，會發現這兩者都是一樣的。

select TH001,TH002 
from COPTG
right join COPTH ON TG001=TH001 AND TG002=TH002
where TG001 IS NULL

3. 利用 NOT IN 的作法：在這個做法中，最主要是要介紹當我們採用 IN 的時候，這個時候只能判斷一個欄位，但當我們的鍵值是複合欄位的時候，就要改變一下將兩個欄位合併起來成為一個欄位，這樣才可以來搭配使用，這個雖然是很直覺的作法，但當資料量大的時候，卻也是比較慢的一種方式。

select TH001, TH002 
from COPTH
where TH001 + TH002 not in ( select TG001 + TG002 from  COPTG )

4. 也是利用 NOT IN，但此時我們在 Subquery 內，把 Master Table 的 Key 值也加進來，雖然語法上看起來似乎有比較好一點，但從執行計畫看起來，差異並不大。

select TH001, TH002 
from COPTH
where TH001 + TH002 not in 
	( select TG001 + TG002 from  COPTG where TG001=TH001 AND TG002=TH002)

5. 利用 Not Exists : 此時我們在 SubQuery 裡面利用 select 1 來識別是有有找到資料，此方式跟之前的比較起來，可以避免中間欄位計算的處理，也可以避免資料關聯起來之後，再進行過濾的狀況，因此在大量資料的狀況下，這個所耗用的整體資源是最少的。

select TH001, TH002 
from COPTH
where not exists
	( select 1 from  COPTG where TG001=TH001 AND TG002=TH002)

6. 利用 EXCEPT : 這個是很簡潔的方式，在 SQL Server 內比較少用到差集，他是一個不錯的方式，只是如果使用 Oracle 習慣的人可能會有點相似，在 Oracle 就是採用 minus 的運算。

select TH001, TH002 from COPTH
except
select TG001, TG002 from COPTG 

7. 利用 Full Join : 這個比較算是硬擠出來一種方式，基本上跟 LEFT JOIN & RIGHT JOIN 的方式雷同。只是一般我們比較會用 FULL JOIN 去做比對兩個資料表內有差異的資料，但拿來找某一個資料表內有找的狀況，就比較大材小用。

select TH001, TH002 
from COPTG
full join COPTH ON TG001=TH001 AND TG002=TH002
where TG001 IS NULL

上述這些寫法差異的時間會隨著兩個資料表內資料的多少和差異筆數有點不同，只是利用這樣的一個範例，來說明同一個狀況下其實我們有很多種可以用的方式，因此可以多多搭配執行計畫來使用，看不同的語法下的變化如何，也可以用這樣的方式，來學習了解如何看懂你的執行計畫。