萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 子查詢深度解析:類型、用法與實戰範例

MySQL 子查詢完全指南:從單行到關聯子查詢的進階應用

子查詢是 SQL 編寫中構建複雜邏輯的核心工具。本文系統梳理子查詢分類、適用場景、操作符選擇,並通過實戰案例演示關聯子查詢、EXISTS 優化等進階技巧。

一、子查詢基礎分類

分類定義適用操作符典型子句
單行子查詢保證返回 ≤1 行=, >, >=, <, <=, <>WHERE, HAVING
多行子查詢可能返回多行IN, ANY, ALL, SOMEWHERE, HAVING
單列子查詢返回單一欄位同上SELECT, WHERE, HAVING, CASE/DECODE
多列子查詢返回多欄位,需成對匹配IN (col1, col2)...WHERE
關聯子查詢內層引用外層欄位,逐行執行同上WHERE, SELECT

二、實戰案例演示(基於測試表 test_t)

1. 多列子查詢:精確匹配多欄位

SELECT t1.id, t1.no, t1.s_name, t1.score
FROM test_t t1
WHERE (t1.id, t1.score) IN (SELECT t2.id, t2.score FROM test_t t2);

外層需同時匹配 (id, score) 兩個欄位。

2. 單列子查詢:查找最高分學生

SELECT no, s_name FROM test_t
WHERE score IN (SELECT MAX(score) FROM test_t);

3. 關聯子查詢:各班級最高分

-- 插入測試數據
INSERT INTO test_t (id, no, t_name, s_name, score) VALUES ('10', '2', 'Mary', 'AAA', '70');

-- 關聯子查詢寫法
SELECT t1.id, t1.no, t1.s_name, t1.score
FROM test_t t1
WHERE t1.score = (SELECT MAX(t2.score) FROM test_t t2 WHERE t1.no = t2.no);

執行邏輯:外層逐行讀取 t1,內層根據 t1.no = t2.no 過濾同班數據計算最高分,再與外層 t1.score 比較。

錯誤示範:將關聯條件移至外層會導致 ORA-00904(內層表對外不可見)。

4. GROUP BY 替代方案:多列 IN 子查詢

SELECT id, no, s_name, score
FROM test_t
WHERE (no, score) IN (SELECT no, MAX(score) FROM test_t GROUP BY no);

內層分組聚合後返回 (班級, 最高分) 元組,外層直接匹配,可讀性更強。

三、EXISTS / NOT EXISTS 高效判斷

EXISTS 僅判斷「是否存在滿足條件的行」,返回 Boolean,不回傳實際數據,適合大數據集存在性檢查。

語法規範

SELECT * FROM test_t t1
WHERE EXISTS (SELECT 1 FROM test_t t2 WHERE t2.score < 60 AND t1.id = t2.id);
-- 子查詢慣用 SELECT 1,避免無意義列投影

執行機制

  1. 外層取一行 → 內層檢查條件
  2. 內層命中第一行即返回 TRUE,停止掃描(短路機制)
  3. 外層輸出該行 → 繼續下一行

NOT EXISTS 反向邏輯

SELECT * FROM test_t t1
WHERE NOT EXISTS (SELECT 1 FROM test_t t2 WHERE t2.score < 90 AND t1.id = t2.id);
-- 僅返回「所有分數 ≥ 90」的學生

四、性能優化建議

  • 優先用 JOIN 替代 IN/EXISTS 子查詢(MySQL 5.6+ 優化器可自動轉換)
  • 關聯子查詢確保關聯欄位有索引
  • 避免在 WHERE 中對大表使用 NOT IN(NULL 值陷阱),改用 NOT EXISTS
  • EXPLAIN 觀察 DEPENDENT SUBQUERY 標記,評估關聯子查詢開銷
copyright © 萬盛學電腦網 all rights reserved