子查詢是 SQL 編寫中構建複雜邏輯的核心工具。本文系統梳理子查詢分類、適用場景、操作符選擇,並通過實戰案例演示關聯子查詢、EXISTS 優化等進階技巧。
| 分類 | 定義 | 適用操作符 | 典型子句 |
|---|---|---|---|
| 單行子查詢 | 保證返回 ≤1 行 | =, >, >=, <, <=, <> | WHERE, HAVING |
| 多行子查詢 | 可能返回多行 | IN, ANY, ALL, SOME | WHERE, HAVING |
| 單列子查詢 | 返回單一欄位 | 同上 | SELECT, WHERE, HAVING, CASE/DECODE |
| 多列子查詢 | 返回多欄位,需成對匹配 | IN (col1, col2)... | WHERE |
| 關聯子查詢 | 內層引用外層欄位,逐行執行 | 同上 | WHERE, SELECT |
test_t)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) 兩個欄位。
SELECT no, s_name FROM test_t
WHERE score IN (SELECT MAX(score) FROM test_t);
-- 插入測試數據
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(內層表對外不可見)。
SELECT id, no, s_name, score
FROM test_t
WHERE (no, score) IN (SELECT no, MAX(score) FROM test_t GROUP BY no);
內層分組聚合後返回 (班級, 最高分) 元組,外層直接匹配,可讀性更強。
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,避免無意義列投影
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 EXISTSEXPLAIN 觀察 DEPENDENT SUBQUERY 標記,評估關聯子查詢開銷