MySQL 子查詢操作符全解析：IN、EXISTS、ANY、ALL、SOME、UNION

MySQL 子查詢操作符全解析：IN、EXISTS、ANY、ALL、SOME、UNION

掌握子查詢操作符的語義差異與適用場景，是編寫高效 SQL 的關鍵。本文系統講解六大操作符的邏輯等價關係、執行機制及最佳實踐。

1. ANY / SOME：滿足任一條件即可

ANY 和 SOME 完全等價，表示「大於子查詢返回集合中的任意一個值」：

SELECT * FROM t1 WHERE col > ANY (SELECT col FROM t2);
-- 等價於
SELECT * FROM t1 WHERE col > val1 OR col > val2 OR col > val3 ...

適用場景：需滿足「只要超過最小值」等鬆散條件時。

2. ALL：必須滿足所有條件

ALL 要求主查詢值同時滿足子查詢結果集中所有值的比較：

SELECT * FROM t1 WHERE col > ALL (SELECT col FROM t2);
-- 等價於
SELECT * FROM t1 WHERE col > val1 AND col > val2 AND col > val3 ...

典型用法：查找「大於最大值」的記錄，即 > ALL 等價於 > (SELECT MAX(col) FROM t2)。

3. IN / NOT IN：集合成員資格判斷

IN 是 = ANY 的別名，用於檢查值是否存在於列表或子查詢結果中：

SELECT id, name FROM a WHERE id IN (SELECT aid FROM b);
SELECT id, name FROM a WHERE id NOT IN (SELECT aid FROM b);

注意：子查詢返回 NULL 會導致 NOT IN 整體判定為 FALSE，建議改用 NOT EXISTS。

4. EXISTS / NOT EXISTS：關聯存在性檢查

核心機制：將主查詢每行數據代入子查詢作為條件驗證，根據 TRUE/FALSE 決定是否保留。

SELECT * FROM employee 
WHERE EXISTS (SELECT 1 FROM department WHERE d_id = 1003 AND dept_name = employee.dept);

關鍵特性：

• 子查詢 SELECT 列表可寫 *、1 或任意常量，優化器會忽略
• 常與 AND/OR 組合使用：WHERE age > 24 AND EXISTS (...)
• 性能取決於子查詢是否能利用索引，建議實測對比 JOIN 方案

5. UNION / UNION ALL：結果集合併

SELECT d_id FROM employee UNION SELECT d_id FROM department;  -- 去重
SELECT d_id FROM employee UNION ALL SELECT d_id FROM department; -- 保留所有

前提條件：所有 SELECT 列數、類型、順序必須一致；第一條查詢的列名將作為結果集列名。

操作符選型決策樹

1. 需判斷「存在即可」→ EXISTS
2. 需匹配離散值列表 → IN
3. 需比較聚合極值 → ALL/ANY
4. 需合併同構結果集 → UNION ALL（優先）或 UNION