2024年01月15日玄貓（BlackCat）

SQL 進階查詢技術：表格連線與比較運算子完整指南

深入探討 SQL 表格連線技術與比較運算子的實戰應用。涵蓋 INNER JOIN、LEFT JOIN、多表連線策略、子查詢設計與 CTE 應用，搭配完整的程式碼範例與效能最佳化技巧，協助開發者建構高效的資料庫查詢邏輯。

資料庫技術 SQL 程式設計

SQL JOIN 比較運算子子查詢 CTE 資料庫查詢最佳化關聯式資料庫查詢效能

當企業的業務邏輯變得複雜時，資料往往分散在多個資料表中。一個電子商務系統可能有使用者資料表、訂單資料表、產品資料表與庫存資料表，這些資料表透過外鍵關聯形成一個完整的資料模型。要從這些分散的資料中提取有意義的資訊，就需要使用 SQL 的表格連線（JOIN）技術將相關的資料組合在一起。同時資料的篩選與比較也是查詢的核心需求，SQL 提供了豐富的比較運算子來精確定位所需的資料。

表格連線不僅僅是將兩個資料表的欄位放在一起這麼簡單。不同的連線類型決定了當兩個資料表的資料不完全匹配時應該如何處理。內連線（INNER JOIN）只保留兩邊都有匹配的資料，左連線（LEFT JOIN）保留左邊資料表的所有資料即使右邊沒有匹配，右連線與外連線則提供了其他的資料組合方式。理解這些連線類型的差異對於正確提取資料至關重要。

比較運算子看似簡單，但在實務應用中有許多細節需要注意。等於與不等於的判斷、NULL 值的特殊處理、IN 與 BETWEEN 的效能差異、子查詢的適用場景等等，這些都會影響查詢的正確性與效能。一個設計良好的查詢不僅能正確回傳資料，還能充分利用索引減少資料庫的負擔。

本文將深入探討 SQL 表格連線與比較運算子的各種技巧。從基礎的雙表連線到複雜的多表連線，從簡單的等於判斷到進階的子查詢設計，我們將透過豐富的程式碼範例與詳細的註解，協助讀者掌握資料庫查詢的核心技術，並理解不同查詢方式的效能影響。

SQL JOIN 基礎與多表連線策略

關聯式資料庫的核心概念是將資料正規化，避免重複儲存相同的資訊。這意味著完整的業務資料必然分散在多個資料表中，透過外鍵關聯維持資料的一致性。當需要查詢完整資訊時，就必須使用 JOIN 將相關的資料表連接起來。以下透過一個實際的訂單系統範例來說明各種連線技術的應用。

-- 建立使用者資料表
-- 儲存系統中所有使用者的基本資訊
CREATE TABLE users (
    -- 使用者唯一識別碼
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- 使用者名稱
    username VARCHAR(50) NOT NULL,
    
    -- 電子郵件地址
    email VARCHAR(100) NOT NULL UNIQUE,
    
    -- 註冊日期
    registration_date DATE NOT NULL,
    
    -- 建立索引以加速查詢
    INDEX idx_username (username),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 建立訂單資料表
-- 儲存所有訂單的基本資訊
CREATE TABLE orders (
    -- 訂單唯一識別碼
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- 下單使用者的 ID（外鍵）
    user_id INT NOT NULL,
    
    -- 訂單日期
    order_date DATETIME NOT NULL,
    
    -- 訂單總金額
    total_amount DECIMAL(10, 2) NOT NULL,
    
    -- 訂單狀態（pending、processing、shipped、delivered、cancelled）
    status VARCHAR(20) NOT NULL,
    
    -- 外鍵約束，確保每個訂單都對應到一個有效的使用者
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    
    -- 建立索引以加速查詢
    INDEX idx_user_id (user_id),
    INDEX idx_order_date (order_date),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 建立產品資料表
-- 儲存所有產品的資訊
CREATE TABLE products (
    -- 產品唯一識別碼
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- 產品名稱
    product_name VARCHAR(100) NOT NULL,
    
    -- 產品分類
    category VARCHAR(50) NOT NULL,
    
    -- 產品單價
    unit_price DECIMAL(10, 2) NOT NULL,
    
    -- 庫存數量
    stock_quantity INT NOT NULL DEFAULT 0,
    
    -- 建立索引
    INDEX idx_category (category),
    INDEX idx_product_name (product_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 建立訂單明細資料表
-- 儲存每個訂單包含的產品項目
CREATE TABLE order_items (
    -- 訂單明細唯一識別碼
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- 訂單 ID（外鍵）
    order_id INT NOT NULL,
    
    -- 產品 ID（外鍵）
    product_id INT NOT NULL,
    
    -- 購買數量
    quantity INT NOT NULL,
    
    -- 該項目的價格（可能與產品表的單價不同，因為可能有折扣）
    item_price DECIMAL(10, 2) NOT NULL,
    
    -- 外鍵約束
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    
    -- 建立索引
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

有了這些資料表後，可以透過不同類型的 JOIN 來組合資料。最常用的是內連線，它只回傳兩個資料表中都有匹配的資料列。例如要查詢所有訂單及其對應的使用者資訊，可以使用內連線將訂單資料表與使用者資料表連接。

-- 內連線範例：查詢所有訂單及其對應的使用者資訊
-- INNER JOIN 只會回傳兩個資料表都有匹配資料的列
-- 如果某個訂單的 user_id 在 users 資料表中找不到，該訂單將不會出現在結果中
SELECT 
    -- 從 orders 資料表選取欄位
    o.order_id,
    o.order_date,
    o.total_amount,
    o.status,
    
    -- 從 users 資料表選取欄位
    u.username,
    u.email
FROM 
    orders o
INNER JOIN 
    users u ON o.user_id = u.user_id
WHERE 
    -- 只查詢最近 30 天的訂單
    o.order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
ORDER BY 
    -- 按訂單日期降序排列（最新的在前）
    o.order_date DESC;

左連線在某些場景下特別有用，例如要顯示所有使用者及其訂單資訊，即使某些使用者從未下過訂單。左連線會保留左邊資料表（在這個例子中是 users）的所有資料列，即使在右邊資料表（orders）中找不到匹配的資料。

-- 左連線範例：查詢所有使用者及其訂單統計
-- LEFT JOIN 會保留左邊資料表（users）的所有資料列
-- 即使某個使用者沒有任何訂單，該使用者仍會出現在結果中
SELECT 
    u.user_id,
    u.username,
    u.email,
    u.registration_date,
    
    -- 計算每個使用者的訂單數量
    -- COUNT(o.order_id) 會計算該使用者的訂單數
    -- 如果使用者沒有訂單，COUNT 會回傳 0
    COUNT(o.order_id) AS total_orders,
    
    -- 計算每個使用者的訂單總金額
    -- COALESCE 函數用於處理 NULL 值
    -- 如果 SUM 回傳 NULL（使用者沒有訂單），則回傳 0.00
    COALESCE(SUM(o.total_amount), 0.00) AS total_spent
FROM 
    users u
LEFT JOIN 
    orders o ON u.user_id = o.user_id
GROUP BY 
    -- 按使用者分組，計算每個使用者的統計資料
    u.user_id, u.username, u.email, u.registration_date
ORDER BY 
    -- 按總消費金額降序排列
    total_spent DESC;

當需要從三個或更多資料表中提取資料時，可以使用多個 JOIN 將它們連接起來。例如要查詢訂單的完整資訊，包括使用者、訂單、產品等，就需要連接多個資料表。

-- 多表連線範例：查詢訂單的完整資訊
-- 包括使用者資訊、訂單資訊、產品資訊
SELECT 
    -- 訂單基本資訊
    o.order_id,
    o.order_date,
    o.status,
    
    -- 使用者資訊
    u.username,
    u.email,
    
    -- 產品資訊
    p.product_name,
    p.category,
    
    -- 訂單明細資訊
    oi.quantity,
    oi.item_price,
    
    -- 計算該項目的小計
    -- 數量 * 單價
    (oi.quantity * oi.item_price) AS item_subtotal
FROM 
    orders o
INNER JOIN 
    -- 連接使用者資料表
    users u ON o.user_id = u.user_id
INNER JOIN 
    -- 連接訂單明細資料表
    order_items oi ON o.order_id = oi.order_id
INNER JOIN 
    -- 連接產品資料表
    products p ON oi.product_id = p.product_id
WHERE 
    -- 只查詢已完成的訂單
    o.status = 'delivered'
    
    -- 並且是特定分類的產品
    AND p.category = '電子產品'
ORDER BY 
    o.order_date DESC, o.order_id, p.product_name;

@startuml
!define PLANTUML_FORMAT svg
!theme _none_

skinparam dpi auto
skinparam shadowing false
skinparam linetype ortho
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 14
skinparam minClassWidth 100

|SQL 查詢引擎|
start

:接收 JOIN 查詢;

:解析 SQL 語句;
note right
  識別所有涉及的資料表
  分析連線條件
  檢查索引可用性
end note

:建立查詢執行計畫;

if (是否使用索引?) then (是)
  :使用索引掃描;
  note right
    透過索引快速定位資料
    減少全表掃描的開銷
  end note
else (否)
  :執行全表掃描;
  note right
    逐列讀取資料
    效能較差
  end note
endif

:執行第一個 JOIN;
note right
  讀取第一個資料表
  建立暫時結果集
end note

while (還有其他 JOIN?) is (是)
  :執行下一個 JOIN;
  
  :根據連線條件匹配資料;
  
  if (連線類型?) then (INNER JOIN)
    :只保留匹配的資料列;
  elseif (LEFT JOIN)
    :保留左表所有資料列;
    :右表無匹配時填入 NULL;
  elseif (RIGHT JOIN)
    :保留右表所有資料列;
    :左表無匹配時填入 NULL;
  else (FULL OUTER JOIN)
    :保留兩表所有資料列;
  endif
  
  :更新暫時結果集;
endwhile (否)

:套用 WHERE 條件篩選;

:套用 GROUP BY 分組;

:套用 HAVING 條件;

:套用 ORDER BY 排序;

:套用 LIMIT 限制;

:回傳最終結果集;

stop

@enduml

比較運算子的實戰應用

SQL 的比較運算子是查詢條件的基礎，透過這些運算子可以精確地篩選出符合條件的資料。除了基本的等於與不等於，SQL 還提供了許多進階的比較方式，每種都有其特定的應用場景與效能考量。

最基本的比較是等於（=）與不等於（<> 或 !=），這些運算子用於精確匹配。當比較的欄位有索引時，等於運算子能夠快速定位資料。但需要注意的是，NULL 值的比較需要特殊處理，不能使用一般的等於運算子。

-- 等於運算子範例
-- 查詢特定狀態的訂單
SELECT 
    order_id,
    order_date,
    total_amount,
    status
FROM 
    orders
WHERE 
    -- 使用等於運算子精確匹配狀態
    -- 如果 status 欄位有索引，這個查詢會很快
    status = 'pending'
ORDER BY 
    order_date DESC;

-- 不等於運算子範例
-- 查詢所有非取消的訂單
SELECT 
    order_id,
    order_date,
    total_amount,
    status
FROM 
    orders
WHERE 
    -- 使用不等於運算子排除特定值
    -- 兩種寫法都可以：<> 或 !=
    status <> 'cancelled'
ORDER BY 
    order_date DESC;

-- NULL 值的特殊處理
-- 查詢沒有電子郵件的使用者
SELECT 
    user_id,
    username,
    email
FROM 
    users
WHERE 
    -- 不能使用 email = NULL
    -- 必須使用 IS NULL
    email IS NULL;

-- 查詢有電子郵件的使用者
SELECT 
    user_id,
    username,
    email
FROM 
    users
WHERE 
    -- 不能使用 email <> NULL
    -- 必須使用 IS NOT NULL
    email IS NOT NULL;

數值比較運算子（>、>=、<、<=）用於範圍查詢，在處理日期、金額、數量等數值型資料時特別有用。這些運算子也能利用索引來提升查詢效能。

-- 大於運算子範例
-- 查詢金額超過 1000 元的訂單
SELECT 
    order_id,
    order_date,
    total_amount,
    status
FROM 
    orders
WHERE 
    -- 查詢總金額大於 1000 的訂單
    total_amount > 1000.00
ORDER BY 
    total_amount DESC;

-- 範圍查詢範例
-- 查詢特定日期範圍內的訂單
SELECT 
    order_id,
    order_date,
    total_amount,
    status
FROM 
    orders
WHERE 
    -- 使用大於等於和小於等於組合範圍條件
    order_date >= '2025-01-01'
    AND order_date <= '2025-01-31'
ORDER BY 
    order_date;

IN 運算子允許在一個條件中指定多個可能的值，這比使用多個 OR 條件更簡潔。當需要匹配的值不多時，IN 是一個很好的選擇。但如果值的數量過多，可能需要考慮其他方式。

-- IN 運算子範例
-- 查詢特定狀態的訂單
SELECT 
    order_id,
    order_date,
    total_amount,
    status
FROM 
    orders
WHERE 
    -- 使用 IN 匹配多個可能的值
    -- 這比 status = 'pending' OR status = 'processing' 更簡潔
    status IN ('pending', 'processing', 'shipped')
ORDER BY 
    order_date DESC;

-- NOT IN 運算子範例
-- 查詢排除特定狀態的訂單
SELECT 
    order_id,
    order_date,
    total_amount,
    status
FROM 
    orders
WHERE 
    -- 使用 NOT IN 排除多個值
    status NOT IN ('cancelled', 'delivered')
ORDER BY 
    order_date DESC;

-- IN 搭配子查詢
-- 查詢有訂單的使用者
SELECT 
    user_id,
    username,
    email
FROM 
    users
WHERE 
    -- 使用子查詢產生 IN 清單
    -- 子查詢會先執行，回傳所有有訂單的使用者 ID
    user_id IN (
        SELECT DISTINCT user_id 
        FROM orders
    );

BETWEEN 運算子用於指定一個範圍，包含起始值與結束值。這對於日期範圍、數值範圍的查詢特別方便。BETWEEN 在內部會被轉換為大於等於和小於等於的組合。

-- BETWEEN 運算子範例
-- 查詢特定金額範圍的訂單
SELECT 
    order_id,
    order_date,
    total_amount,
    status
FROM 
    orders
WHERE 
    -- BETWEEN 包含起始值和結束值
    -- 相當於 total_amount >= 500.00 AND total_amount <= 2000.00
    total_amount BETWEEN 500.00 AND 2000.00
ORDER BY 
    total_amount;

-- BETWEEN 用於日期範圍
-- 查詢特定月份的訂單
SELECT 
    order_id,
    order_date,
    total_amount,
    status
FROM 
    orders
WHERE 
    -- 查詢 2025 年 1 月的所有訂單
    order_date BETWEEN '2025-01-01' AND '2025-01-31 23:59:59'
ORDER BY 
    order_date;

-- NOT BETWEEN 運算子
-- 查詢金額在範圍外的訂單
SELECT 
    order_id,
    order_date,
    total_amount,
    status
FROM 
    orders
WHERE 
    -- 排除特定範圍的金額
    total_amount NOT BETWEEN 100.00 AND 500.00
ORDER BY 
    total_amount DESC;

子查詢與 Common Table Expression

子查詢是嵌套在主查詢中的查詢，它可以出現在 SELECT、FROM、WHERE 等多個位置。子查詢讓我們能夠將複雜的查詢邏輯分解為多個步驟，提高查詢的可讀性。但需要注意的是，子查詢的執行方式可能影響查詢效能。

-- 子查詢在 WHERE 子句中的應用
-- 查詢訂單金額高於平均值的訂單
SELECT 
    order_id,
    order_date,
    total_amount,
    status
FROM 
    orders
WHERE 
    -- 子查詢計算所有訂單的平均金額
    -- 主查詢篩選出金額高於平均的訂單
    total_amount > (
        SELECT AVG(total_amount)
        FROM orders
    )
ORDER BY 
    total_amount DESC;

-- 子查詢在 FROM 子句中的應用（衍生表格）
-- 查詢每個使用者的訂單統計
SELECT 
    u.username,
    u.email,
    order_stats.order_count,
    order_stats.total_spent
FROM 
    users u
INNER JOIN (
    -- 這是一個衍生表格
    -- 先計算每個使用者的訂單統計
    SELECT 
        user_id,
        COUNT(*) AS order_count,
        SUM(total_amount) AS total_spent
    FROM 
        orders
    GROUP BY 
        user_id
) AS order_stats ON u.user_id = order_stats.user_id
WHERE 
    -- 只顯示有超過 3 筆訂單的使用者
    order_stats.order_count > 3
ORDER BY 
    order_stats.total_spent DESC;

-- 相關子查詢
-- 查詢每個產品分類中價格最高的產品
SELECT 
    p1.product_name,
    p1.category,
    p1.unit_price
FROM 
    products p1
WHERE 
    -- 這是一個相關子查詢
    -- 它會針對外部查詢的每一列執行一次
    p1.unit_price = (
        SELECT MAX(p2.unit_price)
        FROM products p2
        WHERE p2.category = p1.category
    )
ORDER BY 
    p1.category, p1.product_name;

Common Table Expression（CTE）是 SQL 提供的一種更清晰的方式來定義暫時結果集。相較於子查詢，CTE 提供了更好的可讀性，特別是在處理複雜查詢時。CTE 使用 WITH 關鍵字定義，可以在主查詢中多次引用。

-- CTE 基本範例
-- 使用 CTE 計算使用者的訂單統計
WITH user_order_stats AS (
    -- 定義一個名為 user_order_stats 的 CTE
    -- 計算每個使用者的訂單數量和總金額
    SELECT 
        user_id,
        COUNT(*) AS order_count,
        SUM(total_amount) AS total_spent,
        AVG(total_amount) AS avg_order_value
    FROM 
        orders
    GROUP BY 
        user_id
)
-- 主查詢可以像使用一般資料表一樣使用 CTE
SELECT 
    u.username,
    u.email,
    u.registration_date,
    uos.order_count,
    uos.total_spent,
    uos.avg_order_value
FROM 
    users u
INNER JOIN 
    user_order_stats uos ON u.user_id = uos.user_id
WHERE 
    -- 篩選高價值客戶
    uos.total_spent > 10000.00
ORDER BY 
    uos.total_spent DESC;

-- 多個 CTE 的應用
-- 分析產品銷售情況
WITH monthly_sales AS (
    -- 第一個 CTE：計算每個產品的月銷售額
    SELECT 
        p.product_id,
        p.product_name,
        p.category,
        DATE_FORMAT(o.order_date, '%Y-%m') AS sale_month,
        SUM(oi.quantity * oi.item_price) AS monthly_revenue
    FROM 
        order_items oi
    INNER JOIN 
        orders o ON oi.order_id = o.order_id
    INNER JOIN 
        products p ON oi.product_id = p.product_id
    WHERE 
        o.status = 'delivered'
    GROUP BY 
        p.product_id, p.product_name, p.category, sale_month
),
product_summary AS (
    -- 第二個 CTE：彙總每個產品的總銷售
    SELECT 
        product_id,
        product_name,
        category,
        SUM(monthly_revenue) AS total_revenue,
        AVG(monthly_revenue) AS avg_monthly_revenue,
        COUNT(DISTINCT sale_month) AS months_with_sales
    FROM 
        monthly_sales
    GROUP BY 
        product_id, product_name, category
)
-- 主查詢使用兩個 CTE
SELECT 
    category,
    product_name,
    total_revenue,
    avg_monthly_revenue,
    months_with_sales
FROM 
    product_summary
WHERE 
    -- 只顯示總銷售額超過 50000 的產品
    total_revenue > 50000.00
ORDER BY 
    category, total_revenue DESC;

@startuml
!define PLANTUML_FORMAT svg
!theme _none_

skinparam dpi auto
skinparam shadowing false
skinparam linetype ortho
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 14
skinparam minClassWidth 100

|SQL 查詢引擎|
start

:接收含有子查詢或 CTE 的查詢;

if (查詢類型?) then (子查詢)
  :解析子查詢;
  
  if (子查詢位置?) then (WHERE 子句)
    :先執行子查詢;
    :取得子查詢結果;
    :將結果用於主查詢的條件判斷;
  elseif (FROM 子句)
    :執行子查詢建立衍生表格;
    :將衍生表格作為主查詢的資料來源;
  else (SELECT 子句)
    :對每一列執行子查詢;
    :將結果作為欄位值;
  endif
  
elseif (CTE)
  :解析所有 CTE 定義;
  
  while (還有 CTE?) is (是)
    :執行 CTE 查詢;
    :建立暫時結果集;
    :儲存於記憶體中;
  endwhile (否)
  
  :執行主查詢;
  :引用 CTE 結果集;
else (一般查詢)
  :直接執行查詢;
endif

:套用 WHERE 條件;

:執行資料篩選;

:套用 GROUP BY 分組;

:計算彙總函數;

:套用 HAVING 條件;

:套用 ORDER BY 排序;

:套用 LIMIT 限制;

:回傳最終結果集;

:釋放暫時資源;

stop

@enduml

從實務角度來看，選擇適當的查詢技術對於資料庫效能至關重要。JOIN 操作應該優先使用有索引的欄位作為連線條件，這能大幅減少資料掃描的範圍。當需要從多個資料表提取資料時，應該先評估各資料表的大小與過濾條件，優先過濾能夠顯著減少資料量的資料表。

比較運算子的選擇也會影響查詢效能。等於運算子能夠充分利用索引，IN 運算子在值的數量不多時效能良好，但如果需要匹配的值過多，可能需要考慮使用 JOIN 或其他方式。BETWEEN 運算子適合範圍查詢，但如果範圍過大導致回傳大量資料，查詢效能仍會受到影響。

子查詢與 CTE 各有其適用場景。子查詢語法簡潔，但在複雜查詢中可能影響可讀性。CTE 提供了更清晰的查詢結構，特別適合需要多次引用中間結果的情況。遞迴 CTE 則為處理階層式資料提供了強大的工具。無論選擇哪種方式，都應該透過執行計畫分析來驗證查詢效能，確保查詢能夠有效利用索引並避免不必要的全表掃描。