一、前言

• MySQL 子查詢是指嵌套在其他 SQL 語句（如 SELECT、WHERE、FROM 等）內部的查詢。
• 用于輔助主查詢完成復雜的數據篩選或計算。

---

二、子查詢分類

• 標量子查詢
  • 描述：返回 單行單列（一個值），常用于比較運算（如 =、>、< 等）。
  • 示例：SELECT name FROM employee WHERE salary (SELECT AVG(salary) FROM employee);
• 列子查詢
  • 描述：返回 多行單列，需搭配 IN、ANY、ALL 等操作符。
  • 示例： SELECT name FROM department WHERE id IN (SELECT dept_id FROM employee);
• 行子查詢
  • 描述：返回 單行多列，需用行表達式（如 (col1, col2)）匹配。
  • 示例： SELECT FROM employee WHERE (age, salary) = (SELECT MAX(age), MAX(salary) FROM employee);
• 表子查詢
  • 描述：返回 多行多列，通常作為臨時表（派生表）在 FROM 子句中使用。
  • 示例： SELECT deptname, empcount FROM (SELECT deptid, COUNT(*) AS empcount FROM employee GROUP BY dept_id) AS tmp;
• 相關子查詢
  • 描述：子查詢依賴外部查詢的字段值，逐行執行（效率較低）。
  • 示例： SELECT name FROM employee e1 WHERE salary (SELECT AVG(salary) FROM employee e2 WHERE e1.deptid = e2.deptid);

---

三、常用操作符

• IN / NOT IN：用于判斷值是否在子查詢的結果集中

-- 查詢有訂單的客戶
SELECT customer_name 
FROM customers 
WHERE id IN (SELECT customer_id FROM orders);

• ANY / SOME：比較值與子查詢結果中的任意一個是否滿足條件

-查詢薪資大于任意一個部門平均薪資的員工 
SELECT name  FROM employee  WHERE salary ANY (SELECT AVG(salary) FROM employee GROUP BY dept_id);

• ALL：比較值需滿足與子查詢結果中的所有值

-查詢薪資高于所有部門平均薪資的員工 
SELECT name  FROM employee  WHERE salary ALL (SELECT AVG(salary) FROM employee GROUP BY dept_id);

• XISTS / NOT EXISTS：檢查子查詢是否返回至少一行結果

-- 查詢有下屬部門的上級部門
SELECT dept_name 
FROM departments d 
WHERE EXISTS (SELECT 1 FROM departments WHERE parent_id = d.id);

---

四、典型應用場景

• 1、在 WHERE 子句中篩選數據

-- 查找薪資高于平均值的員工
SELECT name, salary 
FROM employee 
WHERE salary > (SELECT AVG(salary) FROM employee);

• 2、在 FROM 子句中作為臨時表

-- 統計每個部門的平均薪資，并篩選高于公司平均薪資的部門
SELECT dept_id, avg_salary
FROM (SELECT dept_id, AVG(salary) AS avg_salary FROM employee GROUP BY dept_id) AS dept_avg
WHERE avg_salary > (SELECT AVG(salary) FROM employee);

• 3、在 FROM 子句中作為臨時表

-- 顯示員工姓名及其部門平均薪資
SELECT name, salary, (SELECT AVG(salary) FROM employee e2 WHERE e1.dept_id = e2.dept_id) AS dept_avg
FROM employee e1;

---

五、注意事項

• 性能優化：相關子查詢需逐行執行，可能影響性能，可嘗試改寫為 JOIN 或臨時表。
• 處理 NULL 值：使用 IN 或 NOT IN 時，若子查詢結果包含 NULL，可能導致意外結果（如 NOT IN (NULL, …) 永遠返回 FALSE）。
• 多層嵌套：MySQL 支持多層子查詢嵌套，但需保持代碼可讀性。
• 別名使用：派生表必須指定別名（如 FROM (SELECT …) AS tmp）。

---

六、示例：復雜查詢

-- 查找每個部門薪資最高的員工
SELECT dept_id, name, salary
FROM employee e1
WHERE salary = (SELECT MAX(salary) FROM employee e2 WHERE e1.dept_id = e2.dept_id
);

• 通過靈活運用子查詢，可以解決大部分復雜的數據關聯和篩選需求。
• 實際開發中需結合執行計劃分析，確保查詢效率。