SQL 核心操作全解析：UNION、JOIN 類型與集合運算實戰指南

SQL 集合運算與表連接完全指南

在關係型資料庫開發中，掌握集合運算（並集、交集、差集）與表連接（內連接、外連接、交叉連接）是編寫高效查詢的基石。本文系統整理 SQL 標準語法，並對比各操作的效能差異與適用場景，助你靈活應對複雜業務需求。

一、基礎集合運算

1. 並集 UNION / UNION ALL

合併兩個結果集的行，要求列數、順序、資料型別相容。

-- 去除重複行（預設行為）
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

-- 保留所有行（含重複），效能優於 UNION
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;

核心差異：UNION 會執行排序去重，UNION ALL 直接合併，大數據量時後者快數倍。

2. 交集 INTERSECT（標準 SQL）與 JOIN 實現

標準 SQL 提供 INTERSECT，但常用 INNER JOIN 替代：

-- 標準寫法（部分資料庫支援）
SELECT * FROM table1 INTERSECT SELECT * FROM table2;

-- 通用等值連接寫法
SELECT a.* 
FROM table1 AS a 
INNER JOIN table2 AS b ON a.name = b.name;

3. 差集 EXCEPT / NOT IN / NOT EXISTS

-- 標準寫法
SELECT * FROM table1 EXCEPT SELECT * FROM table2;

-- 子查詢寫法（注意 NULL 值陷阱）
SELECT * FROM table1 
WHERE name NOT IN (SELECT name FROM table2);

-- 推薦：NOT EXISTS 避免 NULL 問題且通常更快
SELECT * FROM table1 t1
WHERE NOT EXISTS (SELECT 1 FROM table2 t2 WHERE t1.name = t2.name);

4. 笛卡爾積 CROSS JOIN

-- 顯式語法
SELECT * FROM table1 CROSS JOIN table2;

-- 隱式語法（不建議）
SELECT * FROM table1, table2;

⚠️ 警告：無條件交叉連接會產生 M×N 行，極易導致資源耗盡，僅用於極小表或測試。

二、深度解析 JOIN 類型

1. 內連接 INNER JOIN

僅返回滿足連接條件的匹配行。

-- 等值連接（最常用）
SELECT * FROM table1 a INNER JOIN table2 b ON a.id = b.id;

-- 非等值連接
SELECT * FROM table1 a INNER JOIN table2 b ON a.score > b.threshold;

-- 自然連接（自動匹配同名列，去除重複列，慎用）
SELECT * FROM table1 NATURAL JOIN table2;

2. 外連接 OUTER JOIN

保留單側或雙側所有行，無匹配處填 NULL。

左外連接 LEFT [OUTER] JOIN

SELECT a.*, b.name AS b_name
FROM table1 a
LEFT JOIN table2 b ON a.id = b.a_id;
-- 結果：table1 全部行 + 匹配的 table2 行，無匹配則 b_name 為 NULL

右外連接 RIGHT [OUTER] JOIN

SELECT a.*, b.*
FROM table1 a
RIGHT JOIN table2 b ON a.id = b.a_id;
-- 結果：table2 全部行 + 匹配的 table1 行

全外連接 FULL [OUTER] JOIN

SELECT a.*, b.*
FROM table1 a
FULL JOIN table2 b ON a.id = b.a_id;
-- 結果：兩表所有行，雙側無匹配處均為 NULL

3. 交叉連接 CROSS JOIN（笛卡爾積）

同上，生產環境嚴禁無 WHERE 條件使用。

三、效能優化關鍵點

• 優先用 UNION ALL：除非業務必須去重，否則避免 UNION 的排序開銷。
• 連接條件加索引：ON 子句欄位務必建立索引，尤其大表連接。
• 小表驅動大表：內連接時，優先將小結果集放左側（或用 STRAIGHT_JOIN 提示）。
• 避免 SELECT *：僅查詢所需欄位，減少網路傳輸與記憶體壓力。
• 用 EXISTS 替代 IN：處理差集/子查詢時，NOT EXISTS 通常比 NOT IN 更穩定高效。

四、快速選型速查表

五、實戰建議

1. 開發階段用 EXPLAIN 分析執行計畫，關注 type、rows、Extra 欄位。
2. 大表連接前，先用 WHERE 過濾縮小結果集，再做 JOIN。
3. 避免在 ON 條件中使用函數或隱式轉型，導致索引失效。
4. 定期 ANALYZE TABLE 更新統計資訊，讓優化器生成最優計畫。

掌握這些核心操作與優化原則，能讓你在處理複雜報表、數據清洗、多表關聯業務時遊刃有餘。實戰中多對比執行計畫，才是提升 SQL 功力的最快捷徑。