連接類型總覽
| 類型 | 關鍵字 | 結果集特徵 |
|---|
| 交叉連接 | CROSS JOIN | 笛卡爾積 (M×N)，無 ON 條件 |
| 內連接 | [INNER] JOIN | 僅返回符合 ON 條件的匹配行 |
| 左外連接 | LEFT [OUTER] JOIN | 保留左表所有行，右表無匹配則 NULL |
| 右外連接 | RIGHT [OUTER] JOIN | 保留右表所有行，左表無匹配則 NULL |
| 全外連接 | FULL OUTER JOIN | 保留兩表所有行，無匹配處填 NULL |
| 自連接 | JOIN 同表別名 | 表關聯自身（如員工-主管、分類層級） |
實戰範例（Student 學生表、ClassInfo 班級表）
1. 交叉連接
-- 寫法 1
SELECT * FROM Student CROSS JOIN dbo.ClassInfo;
-- 寫法 2 (舊語法)
SELECT * FROM Student, ClassInfo;
2. 內連接
-- 基本內連接
SELECT *
FROM Student
JOIN dbo.ClassInfo ON dbo.Student.Class = dbo.ClassInfo.ID;
-- 多條件：ON 條件 + 額外篩選
SELECT *
FROM Student
INNER JOIN dbo.ClassInfo ON dbo.Student.Class = dbo.ClassInfo.ID
AND dbo.Student.Class IN (1,2); -- 僅查班級 1、2
-- 不等值連接（罕用）
SELECT *
FROM Student
INNER JOIN dbo.ClassInfo ON dbo.Student.Class <> dbo.ClassInfo.ID;
3. 自連接：查詢班級所屬系別
SELECT T1.*, T2.ClassName AS DeptName
FROM dbo.ClassInfo AS T1
INNER JOIN dbo.ClassInfo AS T2 ON T1.PID = T2.ID;
4. 外連接對比
-- Left Join：所有學生，無班級則 ClassInfo 欄位為 NULL
SELECT * FROM Student LEFT JOIN dbo.ClassInfo ON Student.Class = ClassInfo.ID;
-- Right Join：所有班級，無學生則 Student 欄位為 NULL
SELECT * FROM Student RIGHT JOIN dbo.ClassInfo ON Student.Class = ClassInfo.ID;
-- Full Outer Join：學生+班級全保留
SELECT * FROM Student FULL OUTER JOIN dbo.ClassInfo ON Student.Class = ClassInfo.ID;
5. 集合運算
-- UNION：縱向合併，去重
SELECT StuID, StuName FROM Student WHERE StuID <= 2
UNION
SELECT StuID, StuName FROM Student WHERE StuID > 2;
-- UNION ALL：合併不去重（效能較好）
SELECT StuSex FROM Student WHERE StuID <= 2
UNION ALL
SELECT StuSex FROM Student WHERE StuID > 2;
-- EXCEPT：差集（第一個結果集減去第二個）
SELECT * FROM Student
EXCEPT
SELECT * FROM Student WHERE StuID <= 2;
-- INTERSECT：交集
SELECT * FROM Student WHERE StuID >= 2
INTERSECT
SELECT * FROM Student WHERE StuID <= 3;
關鍵差異總結
- 內連接 vs 外連接：內連接過濾不匹配行；外連接以保留表為主，不匹配處顯示 NULL
- UNION vs UNION ALL：UNION 執行去重排序，UNION ALL 直接合併，效能優於 UNION
- ON vs WHERE：外連接中 ON 條件決定如何匹配，WHERE 條件在連接後過濾，影響保留行
效能建議
- 優先使用內連接，必要時才用外連接
- 連接欄位建立索引（特別是外鍵）
- 大表連接考慮分頁、限制欄位、避免
SELECT *
- EXCEPT/INTERSECT 需兩結果集欄位數、型態完全一致