DEE
Database Engineering Essentials

繁體中文

English

繁體中文

English

Appearance

[DEE-204] 子查詢 vs JOIN ​

INFO

大多數情況下優先使用 JOIN。當子查詢能更清楚地表達意圖，或查詢規劃器證實其效能更佳時，再使用子查詢。務必用 EXPLAIN ANALYZE 驗證——不要假設其中一種在所有情況下都更快。

背景 ​

SQL 提供兩種主要方式來合併多張表的資料：JOIN 和子查詢。JOIN 根據條件將兩張表的資料列並排合併。子查詢則將一個查詢嵌套在另一個查詢中，通常位於 WHERE、FROM 或 SELECT 子句。

「總是把子查詢改寫為 JOIN」這個慣常建議過於簡化。現代查詢最佳化器（PostgreSQL 12+、MySQL 8.0+）經常在內部將子查詢轉換為 JOIN，使得在許多情況下效能差異可以忽略。然而，在特定場景中選擇確實會有重大影響：

  • **關聯子查詢（correlated subqueries）**會對外層查詢的每一列重新執行內層查詢，如果最佳化器無法展平（flatten），成本會很高。
  • EXISTS vs IN 有不同的 NULL 處理語意，影響的是正確性，不只是效能。
  • SELECT 子句中的純量子查詢有時是表達計算最清楚的方式，但如果未被最佳化，可能導致類似 N+1 的行為。

正確的做法是撰寫最能清楚表達意圖的查詢，然後檢查執行計畫。如果計畫相同，就選擇更易讀的形式。

原則 ​

  • 開發者SHOULD優先使用 JOIN 來合併多張表的資料，因為它們通常更易讀，且給予最佳化器更多彈性。
  • 當子查詢欄位可能包含 NULL 時，開發者SHOULD使用 EXISTS 而非 IN，以避免不正確的結果。
  • 開發者在為效能關鍵查詢選擇子查詢或 JOIN 時，MUST檢查執行計畫。
  • 開發者SHOULD NOT盲目地將每個子查詢改寫為 JOIN——有些子查詢更清楚，且最佳化後效能相同。

範例 ​

相同查詢：子查詢 vs JOIN ​

子查詢（IN）：

sql
-- 找出至少有一筆訂單的客戶
SELECT customer_id, name
FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM orders
);

JOIN（等價）：

sql
-- 使用 JOIN 的相同結果
SELECT DISTINCT c.customer_id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

EXISTS（等價，通常更佳）：

sql
-- 使用 EXISTS 的相同結果——不需要 DISTINCT
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
);

在現代 PostgreSQL 上，三者通常產生相同的執行計畫（semi-join）。用 EXPLAIN ANALYZE 驗證。

EXISTS vs IN：NULL 處理差異 ​

這是正確性問題，不是效能問題。

sql
-- 設定：orders 表有些資料列的 customer_id 為 NULL
INSERT INTO orders (order_id, customer_id, total)
VALUES (999, NULL, 0.00);

-- 找出沒有下過訂單的客戶

-- 錯誤：子查詢中有 NULL 時 NOT IN 不回傳任何列
SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders  -- 包含 NULL！
);
-- 回傳：空（0 列）——不正確！

-- 正確：NOT EXISTS 正確處理 NULL
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
);
-- 回傳：沒有訂單的客戶——正確

NOT IN 為何在有 NULL 時失敗： NOT IN (1, 2, NULL) 被評估為 x != 1 AND x != 2 AND x != NULL。由於 x != NULL 永遠是 UNKNOWN，整個表達式就是 UNKNOWN，該列被排除。這是正確的 SQL 行為，但幾乎從來不是預期的結果。

經驗法則： 當子查詢欄位可為 NULL 時，一律使用 NOT EXISTS 而非 NOT IN。

關聯 vs 非關聯子查詢 ​

sql
-- 非關聯子查詢：執行一次，結果被重用
-- 最佳化器可以將此執行為 hash semi-join
SELECT name, salary
FROM employees
WHERE dept_id IN (
    SELECT dept_id FROM departments WHERE budget > 1000000
);

-- 關聯子查詢：參照外層查詢，可能逐列重新執行
-- 找出薪資高於其部門平均的員工
SELECT e.name, e.salary, e.dept_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e.dept_id  -- 關聯：參照外層的 e.dept_id
);

關聯子查詢較難以 JOIN 表達。等價的 JOIN 需要衍生表或 CTE：

sql
-- 上述關聯子查詢的 JOIN 等價寫法
SELECT e.name, e.salary, e.dept_id
FROM employees e
JOIN (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
) dept_avg ON dept_avg.dept_id = e.dept_id
WHERE e.salary > dept_avg.avg_salary;

兩種形式都是有效的。檢查 EXPLAIN ANALYZE 看最佳化器對你的資料分佈處理哪個更好。

EXPLAIN 比較 ​

sql
-- PostgreSQL：使用 IN 的子查詢
EXPLAIN ANALYZE
SELECT customer_id, name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);
Hash Semi Join  (cost=25.00..75.00 rows=500 width=36)
                (actual time=0.15..0.80 rows=480 loops=1)
  Hash Cond: (customers.customer_id = orders.customer_id)
  ->  Seq Scan on customers  (...)
  ->  Hash  (...)
        ->  Seq Scan on orders  (...)
sql
-- PostgreSQL：等價的 JOIN
EXPLAIN ANALYZE
SELECT DISTINCT c.customer_id, c.name
FROM customers c JOIN orders o ON o.customer_id = c.customer_id;
HashAggregate  (cost=80.00..85.00 rows=500 width=36)
               (actual time=0.90..1.10 rows=480 loops=1)
  Group Key: c.customer_id, c.name
  ->  Hash Join  (cost=25.00..75.00 rows=2000 width=36)
                  (actual time=0.15..0.70 rows=2000 loops=1)
        ->  Seq Scan on customers c  (...)
        ->  Hash  (...)
              ->  Seq Scan on orders o  (...)

IN 子查詢被最佳化為 Semi Join（每位客戶找到第一筆匹配即停止），而 JOIN 版本必須產生所有匹配列，再用 HashAggregate 去重。對這個查詢而言，子查詢形式實際上更有效率。

子查詢明顯較佳的場景 ​

場景為何子查詢勝出
存在性檢查EXISTS 執行 semi-join——找到第一筆匹配即停止掃描。JOIN 必須處理所有匹配後再去重。
否定（反連接）NOT EXISTS 因 NULL 處理而比 NOT IN 安全，且通常被最佳化為與 LEFT JOIN ... IS NULL 相同的計畫。
純量計算SELECT 中計算單一值的子查詢（例如計數或平均值）可能比 lateral join 或 CTE 更清楚。
依聚合篩選WHERE dept_id IN (SELECT dept_id ... GROUP BY ... HAVING ...) 比 JOIN 衍生表更易讀。

JOIN 明顯較佳的場景 ​

場景為何 JOIN 勝出
需要兩張表的欄位JOIN 自然地產生兩側的欄位；WHERE 中的子查詢無法將欄位加入結果。
多重關聯跨 3 張以上的表串接 JOIN 比巢狀子查詢更易讀。
最佳化器彈性JOIN 給予最佳化器更多自由來選擇 JOIN 順序、演算法和平行化。
對 JOIN 後資料聚合對已 JOIN 的資料做 GROUP BY 很直覺；等價的子查詢需要笨拙的重構。

常見錯誤 ​

  1. 為了效能總是把子查詢改寫為 JOIN。 現代最佳化器經常在內部將子查詢轉換為 JOIN。盲目改寫可能降低可讀性而沒有任何效能增益。改寫前後務必檢查 EXPLAIN ANALYZE。

  2. 在可為 NULL 的欄位上使用 NOT IN。 如果子查詢回傳任何 NULL 值，NOT IN 會回傳零列。這在技術上是正確的 SQL 行為，但幾乎從來不是預期的結果。使用 NOT EXISTS 或在子查詢中加上 WHERE column IS NOT NULL。

  3. 忽視關聯子查詢的效能。 最佳化器無法展平的關聯子查詢會對外層的每一列執行一次。在大型外層結果集上，這會變成類似 N+1 的問題。在執行計畫中檢查 PostgreSQL 的「SubPlan」節點或 MySQL EXPLAIN 的「DEPENDENT SUBQUERY」——兩者都表示逐列執行。

  4. 在 SELECT 中使用子查詢而不檢查迴圈成本。 SELECT 子句中的純量子查詢（例如 (SELECT COUNT(*) FROM orders WHERE orders.customer_id = c.customer_id)）本質上是關聯子查詢。如果最佳化器無法將其轉換為 JOIN，它會對每一輸出列執行一次。對複雜的逐列計算使用 lateral join 或 CTE。

  5. 使用 JOIN 做存在性檢查時忘記 DISTINCT。 用 JOIN 取代 IN (subquery) 時，如果子查詢側有多筆匹配，JOIN 可能產生重複列。忘記 DISTINCT 會改變結果集的基數。

相關 DEE ​

  • DEE-200 查詢與效能總覽
  • DEE-201 解讀執行計畫——如何驗證子查詢 vs JOIN 的效能
  • DEE-203 JOIN 策略——理解 JOIN 類型與演算法
  • DEE-205 查詢最佳化模式——更廣泛的查詢撰寫技術

參考資料 ​