🔍 SQL 子查詢全位置解析：可編寫子查詢的 7 大子句
子查詢可以出現在 SQL 語句的多個關鍵位置，不同位置的子查詢具有獨特的行為和限制。以下是系統化總結：
📌 1. WHERE 子句（最常用）
SELECT 列
FROM 表
WHERE 列 操作符 (SELECT ...);
類型：
- 標量子查詢（單值）
- 行子查詢（單行多列）
- 集合子查詢（多行單列）
示例：
-- 標量子查詢
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);-- 集合子查詢
SELECT * FROM customers
WHERE id IN (SELECT cust_id FROM orders);
📊 2. FROM 子句（派生表）
SELECT 列
FROM (SELECT ...) AS 別名
WHERE 條件;
特點：
- 必須指定別名
- 可包含任意復雜查詢
- 實質是創建臨時視圖
示例：
SELECT dept, avg_sal
FROM (SELECT dept_id, AVG(salary) AS avg_salFROM employeesGROUP BY dept_id
) AS dept_avg;
🎯 3. SELECT 子句（標量子查詢）
SELECT 列,(SELECT ...) AS 別名
FROM 表;
限制：
- 必須返回單行單列
- 通常為關聯子查詢（引用外部列）
- 每行都會執行一次
示例：
SELECT name,salary,(SELECT AVG(salary) FROM employees) AS avg_sal,salary - (SELECT AVG(salary) FROM employees) AS diff
FROM employees;
🔍 4. HAVING 子句
SELECT 聚合列
FROM 表
GROUP BY 列
HAVING 聚合函數(列) 操作符 (SELECT ...);
特點：
- 在分組后執行
- 可訪問聚合函數結果
示例：
SELECT dept_id, AVG(salary)
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);
🔄 5. JOIN 子句
SELECT 列
FROM 表1
JOIN (SELECT ...) AS 別名 ON 連接條件;
優勢：
- 預先過濾/聚合連接表
- 減少連接數據量
示例：
SELECT c.name, o.order_count
FROM customers c
JOIN (SELECT cust_id, COUNT(*) AS order_countFROM ordersGROUP BY cust_id
) o ON c.id = o.cust_id;
?? 6. INSERT 語句
插入數據來源
INSERT INTO 目標表 (列)
SELECT ... FROM 源表;
示例：
INSERT INTO premium_users (id, name)
SELECT id, name
FROM users
WHERE total_spend > 10000;
插入值計算
INSERT INTO 表 (列1, 列2)
VALUES ((SELECT ...), (SELECT ...)
);
限制：
- 每個子查詢必須返回單值
示例：
INSERT INTO stats (total_users, avg_salary)
VALUES ((SELECT COUNT(*) FROM users),(SELECT AVG(salary) FROM employees)
);
?? 7. UPDATE 語句
UPDATE 表
SET 列 = (SELECT ...)
WHERE 條件;
關鍵點：
- SET 子句的子查詢必須返回單值
- WHERE 子句可嵌套子查詢
示例：
-- 更新員工薪資為部門平均
UPDATE employees e
SET salary = (SELECT AVG(salary)FROM employees WHERE dept_id = e.dept_id
);
?? 8. 特殊位置注意事項
ORDER BY 子句（少用）
SELECT 列
FROM 表
ORDER BY (SELECT ...);
限制：
- 子查詢必須返回單值
- 每行執行一次，性能差
示例：
SELECT name, salary
FROM employees
ORDER BY (SELECT AVG(salary) FROM employees);
CREATE VIEW 語句
CREATE VIEW 視圖名 AS
SELECT ... FROM (SELECT ...);
?? 子查詢通用注意事項
-
NULL 處理
NOT IN遇 NULL 返回空集 → 用NOT EXISTS替代
-- 危險 WHERE id NOT IN (SELECT ...) -- 安全 WHERE NOT EXISTS (SELECT 1 FROM ...) -
性能陷阱
- 關聯子查詢（Correlated Subquery）導致 O(n2) 復雜度
- 解決方案：
-- 低效 SELECT * FROM t1 WHERE col = (SELECT ... FROM t2 WHERE t2.id = t1.id)-- 高效：轉為 JOIN SELECT t1.* FROM t1 JOIN (SELECT ... FROM t2) sub ON t1.id = sub.id
-
返回結果限制
位置 允許的行/列 是否需別名 WHERE 單行或多行（取決操作符） 否 FROM 任意 是 SELECT 單行單列 可選 HAVING 單行或多行 否 SET (UPDATE) 單行單列 否 -
可讀性優化
- 超過 2 層嵌套時改用 CTE（公共表表達式）：
-- 嵌套子查詢（難維護） SELECT ... FROM (SELECT ... FROM (SELECT ...)) -- CTE 優化版 WITH step1 AS (SELECT ...),step2 AS (SELECT ... FROM step1) SELECT ... FROM step2;
💎 子查詢位置決策指南
| 使用場景 | 首選位置 | 替代方案 |
|---|---|---|
| 行級條件過濾 | WHERE | JOIN |
| 創建臨時數據集 | FROM | CTE/臨時表 |
| 動態計算列值 | SELECT | 應用層計算 |
| 分組后過濾 | HAVING | 子查詢 + WHERE |
| 批量插入數據 | INSERT SELECT | ETL工具 |
| 基于查詢結果更新 | UPDATE SET | 多語句事務 |
| 復雜數據集連接前預處理 | JOIN | 物化視圖 |
📌 黃金法則：
- 能用
JOIN解決不用子查詢（優化器更易優化）- 標量子查詢優先放
SELECT，集合查詢優先放WHERE- 關聯子查詢是 性能最后選項 - 必須用
EXPLAIN分析- MySQL 中避免在
SELECT子查詢更新相同表（錯誤 1093）
)
