[SQL] JOIN - Loop Join, Merge Join, Hash Join

• Query Optimizer 會採用最佳執行計畫查詢，但會被限制在即有的條件，選擇最佳的執行計畫，需熟知每種 "Join" 使用時機，才能了解當下不良情況來進行改進，達成提升查詢效率
• 因此觀察相同查詢語法與相同資料表，下列不同所影響查詢方式
• 資料量的大與小
• 資料表之間關聯 "索引" 建立
• 資料庫使用 Adventure Works for SQL Server 2012 的資料表 Sales.SalesOrderHeader 與 Sales.SalesOrderDetail 創建 2 個 table 如下

• CASE 1：
• 查詢

select *
from tbSalesOrderHeader o
join tbSalesOrderDetail od ON o.SalesOrderID = od.SalesOrderID
where o.SalesOrderID = 46666

• 查詢結果
• 使用 "堆積" 資料表，造成 "遺漏索引" 
• 因 SalesOrderID = 46666 ，表示少量資料查詢，採用 "Loop Join"

• CASE 2：
• 查詢

select *
from tbSalesOrderHeader o
join tbSalesOrderDetail od ON o.SalesOrderID = od.SalesOrderID
where o.SalesOrderID > 10000

• 查詢結果
• 使用 "堆積" 資料表，造成 "遺漏索引"，因 "堆積" 此兩資料表並無排序處理
• tbSalesOrderHeader 資料量較少，當成 "Build Input" 
• tbSalesOrderDetail 資料量較多，當成 "Probe Input"
• 因 SalesOrderID > 10000 ，表示少量資料查詢，採用 "Hash Join"

• CASE 3：
• 加入 "索引"

• 查詢

select *
from tbSalesOrderHeader o
join tbSalesOrderDetail od ON o.SalesOrderID = od.SalesOrderID
where o.SalesOrderID > 10000

• 查詢結果
• 因兩資料表都建立 "索引"，也會事先進行 "排序"
• 因此 Query Optimizer 採用 "Merge Join"

• Result：
1. 少量資料查詢，採用 "Loop Join" (巢狀迴圈聯結)
2. 大量資料查詢，資料表中所對應資料行並未排序，採用 "Hash Join" (雜湊比對聯結)
3. 大量資料查詢，資料表中所對應資料行已經排序，採用 "Merge Join" (合併聯結)
• REF：
• SQL Server 效能調校