🔍 WHERE 子句中使用子查詢：深度解析與最佳實踐

在 WHERE 子句中使用子查詢是 SQL 的高階技巧，可實現動態條件過濾。以下是全面指南，涵蓋語法、類型、陷阱及優化策略：

---

📜 一、基礎語法結構

SELECT 列 
FROM 主表 
WHERE 列 操作符 (SELECT 子查詢);

---

🧩 二、三種核心類型

1. 標量子查詢（單行單列）

-- 查詢工資高于平均工資的員工
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees  -- 返回單個值
);-- 可搭配比較運算符：=, >, <, >=, <=, <>

2. 行子查詢（單行多列）

-- 查找與特定員工職位+部門相同的員工
SELECT name, job, dept
FROM employees
WHERE (job, dept) = (SELECT job, dept FROM employees WHERE id = 101  -- 返回單行多列
);

3. 集合子查詢（多行單列）

-- 查詢有訂單的客戶
SELECT name 
FROM customers
WHERE id IN (SELECT DISTINCT cust_id FROM orders  -- 返回多行單列
);-- 常用操作符：IN, NOT IN, ANY, ALL, EXISTS

---

?? 三、六大關鍵注意事項

1. NULL 值的致命陷阱

-- ? 危險：NOT IN 遇 NULL 返回空結果
SELECT name 
FROM products
WHERE id NOT IN (SELECT product_id FROM discontinued  -- 若子查詢含 NULL
);-- ? 解決方案：顯式過濾 NULL
SELECT name 
FROM products
WHERE id NOT IN (SELECT product_id FROM discontinued WHERE product_id IS NOT NULL  -- 關鍵！
);

2. 子查詢返回結果數量

-- ? 錯誤：標量子查詢返回多行
SELECT name 
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees GROUP BY dept  -- 多行！
);-- ? 修正：確保返回單行
WHERE salary IN (SELECT ...)  -- 改用 IN

3. 性能黑洞（關聯子查詢）

-- ? 低效：每行執行一次子查詢（O(n2)）
SELECT name, salary
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept = e.dept  -- 關聯子查詢
);-- ? 優化：先聚合再連接
WITH dept_avg AS (SELECT dept, AVG(salary) avg_salFROM employees GROUP BY dept
)
SELECT e.name, e.salary
FROM employees e
JOIN dept_avg d ON e.dept = d.dept
WHERE e.salary > d.avg_sal;

4. 索引失效場景

-- ? 子查詢中對列使用函數
WHERE id IN (SELECT UPPER(product_code) FROM products  -- 索引失效！
)-- ? 優化：主查詢預處理
WHERE UPPER(id) IN (SELECT product_code FROM products)

5. EXISTS vs IN 的選擇

場景	推薦	原因
子查詢結果集小	IN	解析更快
子查詢結果集大	EXISTS	短路執行，不加載全部結果
需要處理 NULL	EXISTS	天然避免 NOT IN NULL 陷阱
關聯子查詢	EXISTS	通常更高效

-- EXISTS 示例（檢查存在訂單）
SELECT name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o   -- 不返回數據，只檢查存在性WHERE o.cust_id = c.id   -- 關聯條件
);

6. 同名字段歧義

-- ? 錯誤：主查詢與子查詢同名沖突
SELECT id, name
FROM employees
WHERE dept_id IN (SELECT id FROM depts WHERE name = 'IT'  -- 哪個 id？
);-- ? 方案：顯式別名限定
SELECT e.id, e.name
FROM employees e
WHERE e.dept_id IN (SELECT d.id FROM depts d WHERE d.name = 'IT'
);

---

🚀 四、性能優化策略

1. 子查詢轉為連接

-- 原始子查詢
SELECT * 
FROM products p
WHERE p.category_id IN (SELECT id FROM categories WHERE type = 'ELECTRONIC'
);-- ? 優化為 JOIN
SELECT p.*
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.type = 'ELECTRONIC';

2. 限制子查詢返回列

-- ? 低效：返回所有列
WHERE id IN (SELECT * FROM ...)-- ? 高效：只返回必要列
WHERE id IN (SELECT id FROM ...)

3. 臨時表物化

-- 復雜子查詢先存為臨時表
CREATE TEMPORARY TABLE temp_ids AS
SELECT id FROM large_table WHERE condition;-- 主查詢使用臨時表
SELECT * 
FROM main_table 
WHERE id IN (SELECT id FROM temp_ids);

---

🔧 五、高級用法示例

1. 多層嵌套子查詢

-- 找出銷售額超過部門平均的產品
SELECT product_name
FROM sales s
WHERE amount > (SELECT AVG(amount)FROM sales WHERE dept_id = (SELECT dept_id FROM products WHERE id = s.product_id)
);

2. ANY/ALL 運算符

-- 工資高于IT部門任意員工的銷售
SELECT name 
FROM sales_emps
WHERE salary > ANY (SELECT salary FROM it_emps
);-- 工資高于IT部門所有員工
WHERE salary > ALL (SELECT ...)

3. 條件組合

-- 多條件子查詢
SELECT *
FROM orders
WHERE cust_id IN (SELECT id FROM vip_customers)AND product_id NOT IN (SELECT id FROM discontinued_products);

---

💎 終極使用指南

場景	推薦方案	替代方案
簡單值過濾	標量子查詢	變量/JOIN
檢查記錄是否存在	EXISTS	JOIN ... WHERE NULL
多值匹配	IN + 非關聯子查詢	JOIN
關聯條件過濾	關聯子查詢	先聚合再連接
復雜邏輯判斷	CASE + 子查詢	應用層處理

📌 黃金法則：

1. 優先用 EXISTS 替代 IN（尤其 NOT EXISTS vs NOT IN）
2. 子查詢中絕對避免 SELECT *
3. 超過 3 層嵌套考慮重構為 CTE 或臨時表
4. 用 EXPLAIN 分析執行計劃，關注 DEPENDENT SUBQUERY 警告

性能警示標志：

-- 執行計劃中出現 ↓ 表示性能風險
+----+--------------------+--------+------+...
| id | select_type        | table  | type |
+----+--------------------+--------+------+
| 1  | PRIMARY            | e      | ALL  |
| 2  | DEPENDENT SUBQUERY | dept   | ALL  |  -- 關聯子查詢全表掃！
+----+--------------------+--------+------+