MySQL 查詢進階筆記（四）：子查詢分類、EXISTS/IN/比較/行子查詢實戰與 UNION/Handler 進階用法

子查詢分類體系

IN / NOT IN 子查詢

-- 選修 206 號課程的學生
SELECT name, stu_id FROM student
WHERE stu_id IN (SELECT stu_id FROM score WHERE course_id = '206');

-- 未選修「離散數學」的學生（嵌套 IN）
SELECT name, stu_id, major FROM student
WHERE stu_id NOT IN (
  SELECT stu_id FROM score
  WHERE course_id IN (
    SELECT course_id FROM course WHERE course_name = '離散數學'
  )
);

⚠️ NOT IN 陷阱：子查詢結果含 NULL 會導致整體為 UNKNOWN（空結果）。改用 NOT EXISTS 或 LEFT JOIN ... IS NULL。

比較子查詢 (ANY / SOME / ALL)

-- 年齡大於所有計算機系學生的學生（出生日期更早）
SELECT stu_id, name, major, birth_date
FROM student
WHERE birth_date < ALL (
  SELECT birth_date FROM student WHERE major = '計算機'
);

-- 等價邏輯：< ALL = 小於最小值；> ALL = 大於最大值
-- = SOME / = ANY 等價於 IN
-- <> SOME / <> ANY 含義特殊：只要有一個不等即真

EXISTS / NOT EXISTS 相關子查詢

-- 選修 206 號課程的學生（EXISTS 寫法）
SELECT name FROM student s
WHERE EXISTS (
  SELECT 1 FROM score sc
  WHERE sc.stu_id = s.stu_id AND sc.course_id = '206'
);

執行機制差異

• IN：子查詢**先執行一次**產生結果集，外查詢逐行查找（適合子查詢結果集小）
• EXISTS：外查詢**逐行驅動**子查詢（適合外查詢結果集小、有索引支持）

SELECT 列表中的標量/行子查詢

-- 每位女生與 081101 號學生的年齡差
SELECT stu_id, name,
  YEAR(birth_date) - YEAR(
    SELECT birth_date FROM student WHERE stu_id = '081101'
  ) AS age_diff
FROM student
WHERE gender = 0;

-- 性別、學分均相同的學生（行子查詢元組比較）
SELECT stu_id, name
FROM student
WHERE (gender, credits) = (
  SELECT gender, credits FROM student WHERE stu_id = '081101'
);

FROM 子句中的表子查詢 (衍生表)

-- 先算平均分再過濾
SELECT * FROM (
  SELECT stu_id, AVG(score) AS avg_score
  FROM score
  GROUP BY stu_id
) AS t_avg
WHERE avg_score >= 85;

⚠️ MySQL 5.7+ 自動合併衍生表優化；5.6 需加 SQL_SMALL_RESULT 或改 JOIN。

UNION 合併結果集

SELECT stu_id, name, major, gender, birth_date, credits
FROM student WHERE stu_id = '081101'
UNION
SELECT stu_id, name, major, gender, birth_date, credits
FROM student WHERE stu_id = '081210';
-- UNION ALL 保留重複行，性能更好

規則

• 各 SELECT 列數、類型、順序**完全一致**
• 第一個 SELECT 的列名成為結果集列名
• ORDER BY / LIMIT 只能放末尾，單獨 SELECT 需加括號
• 默認去重 (DISTINCT)，指定 ALL 保留重複

HANDLER 低級逐行讀取 (MyISAM/InnoDB 專用)

-- 打開表
HANDLER tbl_name OPEN [AS alias];

-- 讀取第一行 / 下一行
HANDLER tbl_name READ FIRST [WHERE cond] [LIMIT n];
HANDLER tbl_name READ NEXT [WHERE cond] [LIMIT n];

-- 按索引定位讀取
HANDLER tbl_name READ idx_name {=|>=|<=|<} (val1,val2...) [WHERE cond] [LIMIT n];
HANDLER tbl_name READ idx_name {FIRST|NEXT|PREV|LAST} [WHERE cond] [LIMIT n];

-- 關閉
HANDLER tbl_name CLOSE;

適用場景

• 極高併發下避開 SQL 解析/優化器開銷
• 存儲過程/觸發器中逐行處理
• 直接讀取索引順序數據（如游標替代）

⚠️ 非標準 SQL，僅 MyISAM/InnoDB 支持，應用層極少直接使用。