本文 AI 產出，尚未審核

MySQL 子查詢：EXISTS 與 NOT EXISTS

簡介

在資料庫查詢中，我們常常需要根據另一張表的資料來決定是否要取得某筆記錄。子查詢（Subquery）提供了這樣的彈性，而 EXISTS 與 NOT EXISTS 則是子查詢中最常用、也是效能最優的兩個關鍵字。

  • EXISTS 用來判斷子查詢是否至少回傳 一筆 符合條件的資料，若有則回傳 TRUE。
  • NOT EXISTS 則相反，當子查詢 沒有 回傳任何資料時才回傳 TRUE。

這兩個語法在處理「有無關聯」的問題時，往往比 IN、JOIN 更直觀且效能更佳，尤其在資料量大、索引完善的情況下，MySQL 會利用半連接（semi‑join）優化查詢。本文將深入說明 EXISTS / NOT EXISTS 的運作原理、常見寫法與實務應用，幫助您在日常開發中寫出更清晰、更高效的 SQL。

核心概念

1. 基本語法

-- EXISTS 範例
SELECT *
FROM orders o
WHERE EXISTS (
    SELECT 1
    FROM order_items i
    WHERE i.order_id = o.id
      AND i.product_id = 1001
);

-- NOT EXISTS 範例
SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
);
  • 子查詢 內部通常寫 SELECT 1（或 SELECT *），因為 MySQL 只關心是否有資料返回，而不在乎實際欄位值。
  • EXISTS 只要子查詢回傳 任意一筆 結果，就會立即停止搜尋，提升效能。
  • NOT EXISTS 則在子查詢找不到任何符合條件的列時才成立。

2. 為什麼 EXISTS 常比 IN 更快？

情境 IN 的執行方式 EXISTS 的執行方式
子查詢結果很大 需要把子查詢結果全部 materialize（產生暫存表），再與外層比對 只要找到第一筆符合條件的資料即停止，使用半連接
子查詢有 NULL 可能因 NULL 的存在導致結果不符合預期 不受 NULL 影響，只要有匹配即回傳 TRUE

3. 搭配聚合函數的 EXISTS

有時我們需要根據聚合結果（如總金額、筆數）來決定是否選取資料，這時可以把聚合放在子查詢裡，再以 EXISTS 判斷。

SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
    GROUP BY o.customer_id
    HAVING SUM(o.total_amount) > 5000
);
  • 子查詢先依 customer_id 分組，計算每位客戶的訂單總額；
  • HAVING 篩選出總額大於 5000 的客戶；
  • EXISTS 判斷外層客戶是否在這個集合中。

4. 多層子查詢的 EXISTS

EXISTS 可以嵌套使用，形成多層條件檢查。例如：找出「至少有一筆訂單，且該訂單內至少包含一件庫存不足的商品」的客戶。

SELECT DISTINCT c.id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
      AND EXISTS (
          SELECT 1
          FROM order_items i
          JOIN products p ON p.id = i.product_id
          WHERE i.order_id = o.id
            AND p.stock < i.quantity
      )
);
  • 內層 EXISTS 檢查每筆訂單的商品庫存是否不足；
  • 外層 EXISTS 再判斷該客戶是否至少有一筆符合條件的訂單。

5. NOT EXISTS 的典型應用：找出「孤兒」資料

「孤兒」資料指的是在關聯表中找不到對應關聯的列，例如已刪除的產品仍保留在訂單明細中。

SELECT i.*
FROM order_items i
WHERE NOT EXISTS (
    SELECT 1
    FROM products p
    WHERE p.id = i.product_id
);
  • 若 products 表找不到對應的 product_id，則此筆訂單明細被視為孤兒。
  • 使用 NOT EXISTS 可以快速定位此類資料，便於後續清理或補救。

常見陷阱與最佳實踐

陷阱 說明 建議的解決方式
子查詢忘記加索引 EXISTS 內的條件若涉及大量資料，缺乏適當索引會導致全表掃描。 為子查詢中使用的欄位（如 order_id、customer_id）建立索引。
使用 SELECT * 而非 SELECT 1 雖然功能相同，但 SELECT * 會額外讀取欄位資料，增加 I/O。 永遠寫 SELECT 1（或 SELECT NULL），讓 optimizer 更易判斷為半連接。
子查詢返回 NULL 產生意外結果 在 IN 中 NULL 會使結果變成未知（UNKNOWN），但 EXISTS 不受此影響。 若必須使用 IN，請先過濾 NULL：WHERE col IN (SELECT col FROM tbl WHERE col IS NOT NULL)。
忘記使用 DISTINCT 或 GROUP BY 多筆匹配會導致外層重複列。 若需要唯一結果，使用 DISTINCT 或在子查詢中加入 GROUP BY。
過度嵌套子查詢 複雜的多層 EXISTS 可能讓 optimizer 難以最佳化。 考慮將部分邏輯抽成視圖（VIEW）或臨時表（CTE），提升可讀性與效能。

最佳實踐

  1. 僅在需要「存在」判斷時使用 EXISTS/NOT EXISTS，避免把它當成一般的 JOIN。
  2. 確保子查詢的條件欄位都有索引，尤其是外鍵欄位。
  3. 盡量使用 SELECT 1，讓 MySQL 明確知道只關心行的存在與否。
  4. 測試執行計畫（EXPLAIN），確認 optimizer 真的採用了半連接或索引查詢。

實際應用場景

1. 會員篩選：找出已完成購買的活躍會員

SELECT u.id, u.email
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
      AND o.status = 'completed'
      AND o.created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
);
  • 只要在過去 30 天內有 已完成 訂單，即視為活躍會員。
  • 這種篩選常用於行銷郵件名單的生成。

2. 商品庫存警示：找出「有訂單但庫存不足」的商品

SELECT p.id, p.name, p.stock
FROM products p
WHERE EXISTS (
    SELECT 1
    FROM order_items i
    WHERE i.product_id = p.id
      AND i.quantity > p.stock
);
  • 可直接將結果匯入後端系統，觸發自動補貨或通知負責人。

3. 數據清理：刪除沒有任何訂單的客戶資料

DELETE FROM customers
WHERE NOT EXISTS (
    SELECT 1
    FROM orders
    WHERE orders.customer_id = customers.id
);
  • 使用 NOT EXISTS 確保只刪除「孤兒」客戶，避免誤刪仍有交易紀錄的資料。

4. 交叉報表：列出每位銷售員在特定區域是否有業績

SELECT s.id, s.name,
       EXISTS (
           SELECT 1
           FROM sales_detail sd
           WHERE sd.salesperson_id = s.id
             AND sd.region = 'North'
             AND sd.amount > 0
       ) AS has_north_sales
FROM salespersons s;
  • has_north_sales 會回傳 1（TRUE）或 0（FALSE），方便在前端直接顯示圖示或顏色。

總結

EXISTS 與 NOT EXISTS 是 MySQL 中處理「是否存在」條件的利器。相較於 IN、JOIN，它們在大資料集與有明確索引的情況下能夠利用半連接優化，提供更佳的效能與更直觀的語意。掌握以下要點，就能在日常開發中發揮最大效益：

  1. 只要需要判斷資料是否存在，就使用 EXISTS / NOT EXISTS。
  2. 子查詢內寫 SELECT 1，並確保關鍵欄位已建索引。
  3. 利用 EXPLAIN 觀察執行計畫，確保 optimizer 正確使用半連接。
  4. 避免在子查詢中返回 NULL，或使用 IN 時先過濾 NULL。
  5. 適時使用聚合、HAVING 或多層 EXISTS 來完成複雜的業務邏輯。

透過本文的範例與最佳實踐，您應該已能熟練運用 EXISTS / NOT EXISTS 來撰寫高效、易讀的 MySQL 查詢，無論是資料篩選、報表產生、還是資料清理，都能快速解決「有無」的判斷需求。祝您寫出更佳的 SQL，讓資料庫效能與開發效率同步提升！