SQL 外部連接與條件搭配使用完整教學

連接類型總覽

範例資料準備

DECLARE @tbl1 TABLE (id1 CHAR(10), name1 CHAR(10));
INSERT INTO @tbl1 VALUES ('1','a'), ('2','b'), ('33','d'), ('44','e');

DECLARE @tbl2 TABLE (id2 CHAR(10), name2 CHAR(10));
INSERT INTO @tbl2 VALUES ('1','a'), ('2','b'), ('55','d'), ('66','e');

各類連接實戰示範

1. 內連接

SELECT * FROM @tbl1 AS a INNER JOIN @tbl2 AS b ON a.id1 = b.id2;

結果：僅回傳 id=1、id=2 兩筆匹配資料。

2. 左外部連接

SELECT * FROM @tbl1 AS a LEFT JOIN @tbl2 AS b ON a.id1 = b.id2;

結果：@tbl1 4 筆全保留；id=33,44 在 @tbl2 無對應，name2 顯示 NULL。

3. 右外部連接

SELECT * FROM @tbl1 AS a RIGHT JOIN @tbl2 AS b ON a.id1 = b.id2;

結果：@tbl2 4 筆全保留；id=55,66 在 @tbl1 無對應，name1 顯示 NULL。

4. 全外部連接

SELECT * FROM @tbl1 AS a FULL JOIN @tbl2 AS b ON a.id1 = b.id2;

結果：雙方 4+4=8 筆邏輯列，合併後 6 筆（1、2 合併；33、44、55、66 各自保留）。

5. 交叉連接

SELECT * FROM @tbl1 AS a CROSS JOIN @tbl2 AS b;

結果：4 × 4 = 16 筆，無任何匹配邏輯。

WHERE 條件與 ON 條件的關鍵差異（外部連接專屬陷阱）

-- 錯誤：將右表條件放在 WHERE，會把 LEFT JOIN 變成 INNER JOIN
SELECT * FROM @tbl1 a LEFT JOIN @tbl2 b ON a.id1 = b.id2 WHERE b.name2 = 'a';

-- 正確：右表過濾條件放在 ON 子句
SELECT * FROM @tbl1 a LEFT JOIN @tbl2 b ON a.id1 = b.id2 AND b.name2 = 'a';

• ON：決定如何連接，外部連接時不匹配仍保留主表列（右表欄位變 NULL）。
• WHERE：決定最終保留哪些列，會過濾掉 NULL 列，導致外部連接失效。

進階：多表連接與條件組合

SELECT a.*, b.*, c.*
FROM TableA a
LEFT JOIN TableB b ON a.id = b.a_id AND b.status = 1  -- 連接時就過濾
LEFT JOIN TableC c ON a.id = c.a_id
WHERE a.category = 'VIP';  -- 主表過濾放 WHERE

小結

• 內連接：ON/WHERE 效果相同，建議用 ON 表達連接關係。
• 外部連接：右表/從表的過濾條件必須寫在 ON 子句，否則退化為內連接。
• 閱讀習慣：FROM 主表 LEFT JOIN 從表 ON 關聯條件 AND 從表過濾條件 WHERE 主表過濾條件。
• 全外部連接較少用，通常可用 UNION ALL + 子查詢替代以提升相容性。