MySQL 子查詢（Subquery）── WHERE 子查詢完整教學

簡介

在日常的資料庫開發中，我們常常需要根據多層條件來篩選資料。單純的 WHERE 條件只能處理同一層的比較，當條件需要參考另一張表或是同一張表的聚合結果時，子查詢（Subquery） 就成為不可或缺的工具。
WHERE 子查詢允許我們在 WHERE 子句裡嵌入另一個 SELECT，讓查詢的結果可以直接作為過濾條件，極大提升 資料抽取的彈性 與 可讀性。

本篇文章將從概念說明、實作範例、常見陷阱與最佳實踐，逐步帶領讀者掌握 WHERE 子查詢的使用方式，並提供實務上的應用場景，幫助初學者到中級開發者在日常開發中快速上手。

核心概念

1. 子查詢的基本結構

子查詢就是在一個 SELECT、INSERT、UPDATE、DELETE 或 WHERE 子句中再寫一個 SELECT，其結果會被外層查詢使用。
在 WHERE 子查詢中，常見的運算子有：

小技巧：若子查詢回傳多筆資料卻使用 =，MySQL 會拋出「Subquery returns more than 1 row」的錯誤，務必根據需求選擇正確的運算子。

2. 單值子查詢（Scalar Subquery）

單值子查詢只會回傳一筆結果，常用於 比較 或 計算。
範例 1：找出薪資高於全公司平均薪資的員工

SELECT emp_id, emp_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)          -- 計算全公司的平均薪資
    FROM employees
);

說明：子查詢先算出平均薪資（單一數值），外層查詢再挑出薪資大於該平均值的員工。

3. 多值子查詢（IN / NOT IN）

當子查詢會回傳多筆資料時，我們通常使用 IN 或 NOT IN 來判斷「是否在」或「不在」該集合內。

範例 2：找出曾在 2023 年下單且金額超過 5000 的客戶

SELECT DISTINCT customer_id, customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE YEAR(order_date) = 2023
      AND total_amount > 5000
);

說明：子查詢挑出符合條件的 customer_id，外層查詢再根據這些 ID 取出客戶資訊。

4. EXISTS / NOT EXISTS

EXISTS 用於檢查子查詢是否至少返回一筆資料，通常搭配 相關子查詢（correlated subquery），即子查詢內會參考外層的欄位。

範例 3：找出有未付款訂單的會員

SELECT m.member_id, m.member_name
FROM members AS m
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.member_id = m.member_id   -- 相關條件
      AND o.payment_status = 'unpaid'
);

說明：只要子查詢對某位會員找到一筆未付款的訂單，EXISTS 就會回傳 TRUE，此會員即會被列出。

5. ANY / ALL 結合比較運算子

ANY 與 ALL 讓我們可以把子查詢的結果集合與單一值比較，分別代表「任意」或「全部」符合條件。

範例 4：找出薪資高於 所有 部門平均薪資的員工

SELECT emp_id, emp_name, salary, dept_id
FROM employees AS e
WHERE salary > ALL (
    SELECT AVG(salary)          -- 每個部門的平均薪資
    FROM employees
    GROUP BY dept_id
);

說明：ALL 會比較 salary 是否大於子查詢回傳的 每一個 部門平均值，若符合則代表該員工的薪資高於所有部門的平均薪資。

6. 相關子查詢（Correlated Subquery） vs 非相關子查詢

• 非相關子查詢：子查詢不參考外層的欄位，執行時只會跑一次。例如範例 1、2。
• 相關子查詢：子查詢內部會使用外層的欄位，對每一筆外層記錄都會重新執行一次。例如範例 3、4。

效能提醒：相關子查詢若資料量大，可能造成大量重複計算，建議改寫為 JOIN 或使用 WITH（CTE）先把子查詢結果暫存。

程式碼範例（實務應用）

以下提供 5 個常見且實用的 WHERE 子查詢範例，每個範例皆附上註解與說明，讓讀者能直接套用於自己的專案。

sql\n-- 先算出每個部門的平均薪資，再挑出高於自部門平均的員工\nSELECT e.emp_id, e.emp_name, e.salary, e.dept_id\nFROM employees e\nWHERE e.salary > (\n    SELECT AVG(salary)\n    FROM employees\n    WHERE dept_id = e.dept_id   -- 相關子查詢：根據外層的 dept_id 計算\n);

sql\nSELECT p.product_id, p.product_name, p.price\nFROM products p\nWHERE p.product_id IN (\n    SELECT DISTINCT product_id\n    FROM order_items oi\n    JOIN orders o ON oi.order_id = o.order_id\n    WHERE o.order_date >= CURDATE() - INTERVAL 30 DAY\n);

sql\nSELECT e.emp_id, e.emp_name\nFROM employees e\nWHERE EXISTS (\n    SELECT 1\n    FROM tasks t\n    WHERE t.assignee_id = e.emp_id\n      AND t.status = 'pending'\n);

sql\nSELECT c.customer_id, c.customer_name\nFROM customers c\nWHERE c.customer_id NOT IN (\n    SELECT DISTINCT customer_id\n    FROM orders\n);

sql\nSELECT p.product_id, p.product_name, p.price, p.category_id\nFROM products p\nWHERE p.price > ALL (\n    SELECT AVG(price)\n    FROM products\n    WHERE category_id = p.category_id\n    GROUP BY category_id\n);

小結：這些範例涵蓋了 單值、集合、存在性、排除 以及 集合比較 五大類型，足以應對大多數的業務需求。

常見陷阱與最佳實踐

最佳實踐小結：

1. 先思考是否能改寫為 JOIN：JOIN 通常比子查詢執行效率高，且更易於閱讀。
2. 使用 EXISTS 替代 IN：當子查詢回傳大量資料時，EXISTS 的效能往往較佳，因為它只要找到第一筆符合條件就會停止搜尋。
3. 避免 NOT IN 與 NULL 同時出現：改用 NOT EXISTS 或在子查詢中排除 NULL。
4. 適時使用 CTE（WITH）：讓子查詢結果可重複使用，提升可讀性與維護性。
5. 檢查執行計畫：使用 EXPLAIN 觀察 MySQL 如何執行子查詢，必要時加入索引或重寫查詢。

實際應用場景

案例示範：假設電商平台想要發送優惠券給 過去 90 天內 沒有任何訂單 的使用者。可使用 NOT EXISTS：

SELECT u.user_id, u.email
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.user_id
      AND o.created_at >= CURDATE() - INTERVAL 90 DAY
);

此查詢一次完成所有條件判斷，避免先把所有使用者拉出再在程式碼裡過濾，減少資料傳輸與程式負擔。

總結

• WHERE 子查詢是 資料過濾的強大工具，能讓單一 SQL 語句完成多層次的條件判斷。
• 依照需求選擇正確的運算子（=、IN、EXISTS、ANY、ALL），避免常見的 多筆回傳錯誤 與 NULL 造成的三值邏輯問題。
• 效能 是實務開發的關鍵：盡可能使用 JOIN、EXISTS、CTE，並為關聯欄位加上索引。
• 透過本篇提供的 5 大範例、常見陷阱 與 最佳實踐，讀者可以快速在自己的專案中導入 WHERE 子查詢，提升查詢的彈性與可讀性。

掌握了 WHERE 子查詢之後，你將能在 資料分析、報表產生、業務規則驗證 等多種情境中，寫出更簡潔、更高效的 MySQL 查詢語句。祝你在 MySQL 的世界裡玩得開心，寫出乾淨、易維護的程式碼！ 🚀