MySQL 子查詢完全指南：類型、優化與實戰技巧

深入理解子查詢機制與效能調優

子查詢（內部查詢）嵌套於外部查詢的 `WHERE`、`HAVING`、`FROM` 等子句中，可返回標量、行、列或表。善用子查詢能簡化複雜邏輯，但不當使用會導致效能災難。

分類與語法

常見操作符詳解

• 比較運算符 (= > < ...)： 僅限標量子查詢，否則報錯 `Subquery returns more than 1 row`。
• IN / NOT IN： `IN` 等價於 `= ANY`；`NOT IN` 等價於 `<> ALL`。注意 `NOT IN` 遇 `NULL` 會導致結果為空（三值邏輯）。
• ANY / SOME： 滿足任一值即真。
• ALL： 滿足所有值才真。
• EXISTS / NOT EXISTS： 僅判斷是否存在行，不返回具體值，通常比 `IN` 效能更穩定，且不受 `NULL` 影響。

相關子查詢 vs 獨立子查詢

• 獨立子查詢： 不依賴外部查詢，可單獨執行。優化器可將其轉為連接或物化，複雜度 O(M+N)。
• 相關子查詢： 引用外部查詢欄位，對外部每行重新執行。最易造成效能問題，複雜度可能達 O(M×N)。
  範例：`SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.id = t2.id);`

派生表

子查詢置於 `FROM` 子句中形成虛擬表：`SELECT * FROM (SELECT id FROM t1) AS derived_tbl;`。MySQL 5.6+ 支援物化優化。

優化建議

1. 優先考慮 `JOIN` 替代子查詢（特別是 `IN`/`EXISTS` 轉 `INNER JOIN`）。
2. 使用 `EXPLAIN` 分析 `select_type`：`DEPENDENT SUBQUERY` 為相關子查詢警訊。
3. 避免在 `WHERE` 中對大表使用相關子查詢，改用連接或臨時表。
4. 適當增加索引覆蓋子查詢條件欄位。
5. `NOT IN` 含 `NULL` 時改用 `NOT EXISTS` 或 `LEFT JOIN ... IS NULL`。

實戰範例

-- 查詢杭州用戶所有訂單 (獨立子查詢)
SELECT order_id FROM orders WHERE customer_id IN (SELECT customer_id FROM users WHERE city='hangzhou');

-- 查詢訂單數多於 tx 用戶的客戶 (標量子查詢 + HAVING)
SELECT u.customer_id, u.city, COUNT(o.order_id) AS cnt
FROM users u JOIN orders o ON u.customer_id=o.customer_id
WHERE u.customer_id <> 'tx'
GROUP BY u.customer_id
HAVING cnt > (SELECT COUNT(*) FROM orders WHERE customer_id='tx');

-- 查詢訂單數多於 tx 或 9you 任一用戶的客戶 (ANY)
HAVING cnt > ANY (SELECT COUNT(*) FROM orders WHERE customer_id IN ('tx','9you') GROUP BY customer_id);