MySQL子查詢全解析：IN/EXISTS/比較運算符/ANY/ALL/UNION 六大類型與性能優化實戰

子查詢本質

將一個SELECT嵌套在另一個查詢的WHERE/FROM/SELECT列表中。內層結果供外層作條件/數據源。

參考表

• employee(num, d_id, name, age, sex, homeaddr)
• department(d_id, d_name, function, address)

六大子查詢類型實戰

1. IN / NOT IN 子查詢

SELECT * FROM employee 
WHERE d_id IN (SELECT d_id FROM department);

結果：員工部門ID在部門表中存在的行。NOT IN相反。**注意**：子查詢結果含NULL導致NOT IN永為假，慎用。

2. 比較運算符子查詢 (=, !=, >, <, >=, <=)

SELECT d_id, d_name FROM department
WHERE d_id != (SELECT d_id FROM employee WHERE age=24);

前提：子查詢**必須返回單行單列**（標量子查詢），否則報錯。適用於聚合函數結果比較。

3. EXISTS / NOT EXISTS 子查詢（性能首選）

SELECT * FROM employee
WHERE EXISTS (SELECT d_name FROM department WHERE d_id=1004);

• 內層不返回數據，僅返回TRUE/FALSE
• 有滿足行→TRUE，外層執行；無→FALSE，外層跳過
• 可利用索引、正確處理NULL、半連接優化（MySQL 8.0+）

組合條件：

SELECT * FROM employee
WHERE age > 24 AND EXISTS (SELECT d_name FROM department WHERE d_id=1003);

4. ANY / SOME 子查詢

SELECT * FROM employee
WHERE d_id != ANY (SELECT d_id FROM department);

語義：**滿足子查詢結果集中任一值**即可。!= ANY 等價於 NOT IN（無NULL時）。

5. ALL 子查詢

SELECT * FROM employee
WHERE d_id >= ALL (SELECT d_id FROM department);

語義：**滿足子查詢結果集中所有值**。示例：員工部門ID ≥ 所有部門ID → 僅最大部門ID的員工。

6. 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; -- 保留重複

非子查詢但常配合使用。UNION ALL性能遠優於UNION（無排序去重）。

性能優化鐵律

1. 優先 EXISTS 替代 IN：大數據量、外層表大時，EXISTS半連接優化顯著
2. 避免標量子查詢在SELECT列表：每行執行一次，改用JOIN或APPLY(MySQL不支持，用衍生表)
3. 子查詢轉 JOIN：WHERE col IN (SELECT ...) → INNER JOIN ... ON ...
4. 衍生表物化：MySQL 5.6+ 自動物化FROM (SELECT ...)，加速複用
5. LIMIT 在子查詢限制：MySQL 8.0.21+ 支持 IN (SELECT ... LIMIT n)，早期版本用視圖/連接繞過

版本演進

• 5.6：子查詢物化、半連接優化
• 8.0：CTE (WITH)、側向衍生表 LATERAL、IN/EXISTS 進一步優化

子查詢是SQL表達複雜邏輯的強大工具，但濫用是性能殺手。理解六大類型語義、優化器行為、版本特性，配合EXPLAIN驗證，寫出優雅高效的查詢。