MySQL 子查詢實戰指南：三大類型、EXISTS 語義與關聯查詢改寫規律

MySQL 子查詢核心模式與性能優化改寫

子查詢將一個 SELECT 嵌套在另一個查詢中，MySQL 支持三大基本用法。掌握改寫為 JOIN 的規律，能顯著提升執行計劃效率。

一、子查詢三大基本用法

1. 標量子查詢：生成參考值

內層返回單值，外層用於比較。內層必須只返回一行一列。

-- 某天測驗成績
SELECT * FROM score 
WHERE event_id = (SELECT event_id FROM event WHERE date='2002-03-21' AND type='Q');

-- 最早生日的總統（錯誤寫法）
SELECT * FROM president WHERE birth = MIN(birth);  -- 聚合函數不能直接用於 WHERE

-- 正確：標量子查詢
SELECT * FROM president WHERE birth = (SELECT MIN(birth) FROM president);

2. EXISTS / NOT EXISTS：外層驅動內層匹配

「由外向內」：外層每行傳入內層檢查是否存在匹配。內層 SELECT * 只關心行數，不關心具體列。

-- 兩表都存在的數據
SELECT i1 FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.i1 = t2.i2);

-- t1 有、t2 無的數據
SELECT i1 FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE t1.i1 = t2.i2);

3. IN / NOT IN：集合成員資格檢查

內層返回單列集合，外層檢查值是否在集合中。

-- 交集
SELECT i1 FROM t1 WHERE i1 IN (SELECT i2 FROM t2);

-- 差集
SELECT i1 FROM t1 WHERE i1 NOT IN (SELECT i2 FROM t2);

-- 多值列表簡寫
SELECT * FROM student WHERE state IN ('A','B');

二、子查詢改寫為關聯查詢的標準規律

1. 匹配型 → 內連接 (INNER JOIN)

-- 子查詢
SELECT * FROM score WHERE event_id IN (SELECT event_id FROM event WHERE type='T');

-- 改寫
SELECT score.* FROM score, event 
WHERE score.event_id = event.event_id AND event.type = 'T';

-- 通用模式
SELECT * FROM table1 WHERE col1 IN (SELECT col2a FROM table2 WHERE col2b = value);
-- ↓
SELECT table1.* FROM table1, table2 
WHERE table1.col1 = table2.col2a AND table2.col2b = value;

2. 非匹配型 → 左連接 + IS NULL

-- 子查詢：從未缺勤的學生
SELECT * FROM student WHERE student_id NOT IN (SELECT student_id FROM absence);

-- 改寫：LEFT JOIN
SELECT student.* FROM student 
LEFT JOIN absence ON student.student_id = absence.student_id 
WHERE absence.student_id IS NULL;

-- 通用模式
SELECT * FROM table1 WHERE col1 NOT IN (SELECT col2 FROM table2);
-- ↓
SELECT table1.* FROM table1 
LEFT JOIN table2 ON table1.col1 = table2.col2 
WHERE table2.col2 IS NULL;

-- ⚠️ 前提：table2.col2 必須聲明為 NOT NULL

三、性能與語義對比

四、避坑指南

1. NOT IN + NULL 陷阱：若子查詢結果含 NULL，NOT IN 永遠返回空。改用 NOT EXISTS 或 LEFT JOIN ... IS NULL
2. 標量子查詢多行報錯：確保內層唯一約束或加 LIMIT 1
3. 相關子查詢性能：外層大表時，每行執行內層，極慢。優先改寫為 JOIN 或物化表
4. MySQL 版本：5.6+ 引入子查詢物化、半連接優化，舊版本改寫收益更大