MySQL 4.1 起完整支援子查詢
1. FROM 子查詢 (衍生表)
CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
INSERT INTO t1 VALUES (1,'1',1.0), (2,'2',2.0);
SELECT sb1, sb2, sb3
FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
WHERE sb1 > 1;
-- 結果: 2, '2', 4.0
-- 解決聚合函數嵌套限制：AVG(SUM(...))
SELECT AVG(sum_col)
FROM (SELECT SUM(column1) AS sum_col FROM t1 GROUP BY column1) AS t;
2. ROW 子查詢 (列比較)
SELECT * FROM t1
WHERE ROW(1,2) = (SELECT column1, column2 FROM t2);
-- 等價於 WHERE column1=1 AND column2=2
3. EXISTS / NOT EXISTS 相關子查詢
-- 範例 1：找出有分店的店型
SELECT DISTINCT store_type FROM Stores s
WHERE EXISTS (SELECT * FROM Cities_Stores cs WHERE cs.store_type = s.store_type);
-- 範例 2：找出無分店的店型
SELECT DISTINCT store_type FROM Stores s
WHERE NOT EXISTS (SELECT * FROM Cities_Stores cs WHERE cs.store_type = s.store_type);
-- 範例 3：雙重 NOT EXISTS (關聯除法) - 找出在所有城市都有分店的店型
SELECT DISTINCT store_type FROM Stores s1
WHERE NOT EXISTS (
SELECT * FROM Cities c
WHERE NOT EXISTS (
SELECT * FROM Cities_Stores cs
WHERE cs.city = c.city AND cs.store_type = s1.store_type
));
4. 關聯子查詢 (逐列評估)
SELECT column1 FROM t1 AS x
WHERE x.column1 = (SELECT column1 FROM t2 AS y
WHERE y.column1 = (SELECT column1 FROM t3
WHERE x.column2 = t3.column1));
5. 實務建議
- 子查詢可讀性強，但複雜場合 JOIN 效能通常更好 (優化器成熟度)
- 使用
EXPLAIN 檢查執行計畫，關注 DEPENDENT SUBQUERY (相關子查詢) 是否可改寫為 JOIN
- 衍生表 (FROM 子查詢) 會產生臨時表，大資料量注意
tmp_table_size/max_heap_table_size
