SQL Server JOIN 類型完整圖解：CROSS、INNER、OUTER、SELF、FULL 與 HASH/MERGE 提示

JOIN 分類總覽

關鍵語法範例

LEFT JOIN 條件前置寫法 (效能較佳)

-- 寫法 A：ON 中混合聯結與篩選
SELECT a.cust_id, b.order_date, b.tot_amt
FROM customer a LEFT JOIN sales b
  ON a.cust_id = b.cust_id AND b.order_date > '1996/10/15';

-- 寫法 B：衍生表預先篩選 (推薦，減少外部聯結後再篩選)
SELECT a.cust_id, b.order_date, b.tot_amt
FROM customer a
LEFT JOIN (SELECT * FROM sales WHERE order_date > '1996/10/15') b
  ON a.cust_id = b.cust_id;

SELF JOIN 避免重複行

SELECT a.emp_no, a.emp_name, b.emp_no, b.emp_name, a.date_hired
FROM employee a JOIN employee b
  ON a.emp_no <> b.emp_no AND a.date_hired = b.date_hired
WHERE a.emp_name > b.emp_name;  -- 只取一邊，消除鏡像重複

FULL JOIN 實現雙欄位並排顯示 (面試題)

-- 表：id, ic, name, amount (ic='i' 收入, 'c' 支出)
-- 目標：收入與支出按序號並排
SELECT aaa.*, bbb.*
FROM (
    SELECT (SELECT COUNT(id) FROM aa b WHERE b.id < a.id AND ic='i') AS seq, *
    FROM aa a WHERE ic='i'
) aaa
FULL JOIN (
    SELECT (SELECT COUNT(id) FROM aa b WHERE b.id < a.id AND ic='c') AS seq, *
    FROM aa a WHERE ic='c'
) bbb ON aaa.seq = bbb.seq
ORDER BY aaa.name;

JOIN HINT：強制 HASH / MERGE 聯結

USE pubs;
SELECT SUBSTRING(RTRIM(a.au_fname)+' '+LTRIM(a.au_lname),1,25) AS Name,
       SUBSTRING(t.title,1,20) AS Title
FROM authors a 
INNER MERGE JOIN titleauthor ta ON a.au_id = ta.au_id
INNER HASH JOIN titles t ON t.title_id = ta.title_id
ORDER BY au_lname, au_fname;

• MERGE JOIN：雙邊已排序 (或有索引順序)，適合大表等值聯結，低記憶體
• HASH JOIN：建立雜湊表再探測，適合大表無序、非等值或大小表差異大
• 使用 Hint 後 INNER 關鍵字成為必填
• 慎用：最佳化器通常比人更聰明，Hint 會隨統計資料變更而失效

實務建議：
1. 習慣用 LEFT JOIN 統一風格，避免 RIGHT JOIN 造成閱讀困擾。
2. 外部聯結的篩選條件**盡量放在 ON 子句**，而非 WHERE，否則隱性轉為內部聯結。
3. 大表聯結前確認聯結欄位有索引，觀察 Execution Plan 確認運算子 (Nested Loops / Merge / Hash)。