SQL多表連線查詢技巧與資料庫設計最佳化

在關聯式資料庫的世界中，資料通常分散儲存於多個相關聯的表格之中。這種設計雖然能夠有效減少資料冗餘並維護資料一致性，但同時也意味著我們經常需要從多個表格中擷取並組合資料。SQL 的 JOIN 運算正是為了解決這個需求而生，它提供了一套強大且靈活的機制，讓我們能夠根據表格之間的關聯條件，將分散的資料重新組合成有意義的資訊。

掌握多表連線查詢不僅是 SQL 程式設計的核心技能，更是理解關聯式資料庫運作原理的關鍵。從簡單的兩表內連接到複雜的多表混合連接，從基本的子查詢到進階的 Common Table Expression，這些技術共同構成了資料查詢的完整工具箱。本文將從連線操作的基本概念開始，逐步深入各種連線型態的特性與應用場景，並探討如何透過適當的資料庫設計與查詢策略來最佳化系統效能。

連線操作的基本概念與原理

在深入各種 JOIN 型態之前，我們需要先理解連線操作的本質。當我們執行 JOIN 運算時，資料庫引擎會根據指定的連接條件，將兩個或多個表格的資料列進行配對組合。這個過程本質上是一種集合運算，將來自不同表格的資料集合根據特定規則合併成一個新的結果集。

連線操作的核心在於連接條件（Join Condition）。這個條件通常是兩個表格之間共享的欄位值相等比較，但也可以是任何有效的布林運算式。資料庫引擎會檢查每一對可能的資料列組合，只有滿足連接條件的組合才會出現在最終結果中。理解這個原理對於撰寫正確且高效的 JOIN 查詢至關重要。

讓我們透過一個天文資料庫的例子來說明連線操作的運作方式。假設我們有兩個表格，分別儲存太陽系行星的基本資訊以及行星環的數量資料：

-- 建立行星資料表
-- 儲存太陽系各行星的基本資訊
CREATE TABLE planet (
    planet_id INT PRIMARY KEY,      -- 行星唯一識別碼
    name VARCHAR(50) NOT NULL,      -- 行星名稱
    diameter_km DECIMAL(10,2),      -- 直徑（公里）
    distance_au DECIMAL(8,4)        -- 與太陽距離（天文單位）
);

-- 建立行星環資料表
-- 儲存擁有環系統的行星及其環的數量
CREATE TABLE ring (
    planet_id INT PRIMARY KEY,      -- 對應的行星識別碼
    ring_total INT NOT NULL,        -- 環的總數
    main_rings INT,                 -- 主要環數量
    FOREIGN KEY (planet_id) REFERENCES planet(planet_id)
);

-- 插入行星資料
INSERT INTO planet (planet_id, name, diameter_km, distance_au) VALUES
(1, 'Mercury', 4879.00, 0.3871),
(2, 'Venus', 12104.00, 0.7233),
(3, 'Earth', 12756.00, 1.0000),
(4, 'Mars', 6792.00, 1.5237),
(5, 'Jupiter', 142984.00, 5.2034),
(6, 'Saturn', 120536.00, 9.5371),
(7, 'Uranus', 51118.00, 19.1913),
(8, 'Neptune', 49528.00, 30.0690);

-- 插入行星環資料
-- 注意：只有外行星擁有環系統
INSERT INTO ring (planet_id, ring_total, main_rings) VALUES
(5, 3, 1),      -- 木星有 3 個環
(6, 7, 4),      -- 土星有 7 個主要環組
(7, 13, 2),    -- 天王星有 13 個環
(8, 6, 3);     -- 海王星有 6 個環

在這個資料模型中，planet 表格包含所有八顆行星，而 ring 表格只包含四顆擁有環系統的外行星。這種設計反映了真實世界的資料特性：不是所有行星都有環，因此將環的資訊獨立成一個表格可以避免大量的 NULL 值。

內連接的特性與應用

INNER JOIN 是最常用也是最直觀的連線型態。它只傳回兩個表格中都存在匹配資料的記錄，本質上是取兩個資料集的交集。當我們需要查詢必定存在對應關係的資料時，INNER JOIN 是最適合的選擇。

-- 使用 INNER JOIN 查詢擁有環的行星
-- 結果只包含在兩個表格中都有匹配記錄的行星
SELECT
    p.name AS planet_name,           -- 行星名稱
    p.diameter_km,                   -- 行星直徑
    r.ring_total,                    -- 環的總數
    r.main_rings                     -- 主要環數量
FROM planet p
INNER JOIN ring r ON p.planet_id = r.planet_id
ORDER BY r.ring_total DESC;          -- 按環數量降冪排序

這個查詢會傳回四筆記錄，分別是木星、土星、天王星和海王星。水星、金星、地球和火星因為在 ring 表格中沒有對應的記錄，所以不會出現在結果中。INNER JOIN 的這個特性使它非常適合用於查詢強制性關聯的資料，例如訂單與訂單明細、員工與部門等必然存在對應關係的場景。

INNER JOIN 的執行效率通常很高，因為資料庫引擎可以利用索引快速找到匹配的記錄。當連接欄位上建有索引時，引擎可以採用 Nested Loop Join 或 Hash Join 等高效演算法來處理連線操作。因此，在設計資料庫時，確保經常用於 JOIN 的欄位上建有適當的索引是非常重要的最佳化手段。

值得注意的是，INNER JOIN 的關鍵字 INNER 是可以省略的。在 SQL 標準中，單獨使用 JOIN 就等同於 INNER JOIN。然而，為了程式碼的可讀性和明確性，建議在正式的專案中還是明確寫出 INNER 關鍵字。

外連接的各種型態

當我們需要保留某一邊表格的所有記錄，即使在另一邊找不到匹配時，就需要使用外連接（Outer Join）。外連接有三種型態：LEFT OUTER JOIN、RIGHT OUTER JOIN 和 FULL OUTER JOIN，它們的差異在於保留哪一邊或兩邊的所有記錄。

左外連接

LEFT OUTER JOIN 會傳回左邊表格的所有記錄，以及右邊表格中匹配的記錄。如果右邊表格沒有匹配的記錄，則對應的欄位會填入 NULL：

-- 使用 LEFT JOIN 查詢所有行星及其環資訊
-- 即使行星沒有環，也會顯示在結果中
SELECT
    p.name AS planet_name,           -- 行星名稱
    p.distance_au,                   -- 與太陽距離
    COALESCE(r.ring_total, 0) AS rings,  -- 環數量，若無則顯示 0
    CASE
        WHEN r.ring_total IS NULL THEN 'No rings'
        WHEN r.ring_total < 5 THEN 'Few rings'
        ELSE 'Many rings'
    END AS ring_status               -- 環狀態分類
FROM planet p
LEFT OUTER JOIN ring r ON p.planet_id = r.planet_id
ORDER BY p.distance_au;              -- 按距離排序

這個查詢會傳回所有八顆行星，其中水星、金星、地球和火星的環相關欄位會顯示為 0 和 ‘No rings’。LEFT JOIN 特別適合用於需要顯示主表格所有記錄的報表查詢，例如顯示所有客戶及其訂單（包括尚未下單的客戶）、所有產品及其銷售記錄（包括尚未銷售的產品）等場景。

右外連接

RIGHT OUTER JOIN 的運作方式與 LEFT JOIN 相反，它保留右邊表格的所有記錄。實際上，任何 RIGHT JOIN 都可以透過交換表格順序改寫成 LEFT JOIN，因此在實務上 LEFT JOIN 的使用頻率遠高於 RIGHT JOIN：

-- 使用 RIGHT JOIN 達成與上例相同的效果
-- 透過交換表格順序來保留所有行星記錄
SELECT
    p.name AS planet_name,
    COALESCE(r.ring_total, 0) AS rings
FROM ring r
RIGHT OUTER JOIN planet p ON r.planet_id = p.planet_id
ORDER BY p.planet_id;

雖然這個查詢的結果與前一個 LEFT JOIN 的例子相同，但程式碼的可讀性稍差。大多數資料庫專家建議統一使用 LEFT JOIN，並透過調整表格在 FROM 子句中的順序來達成所需的效果。這樣可以使程式碼風格一致，更容易維護。

完全外連接

FULL OUTER JOIN 會傳回兩個表格中的所有記錄，無論是否找到匹配。這種連線型態在實務上較少使用，但在某些需要比對兩個資料集差異的場景中非常有用：

-- 假設我們有另一個表格儲存已探測的行星
CREATE TABLE explored_planet (
    planet_id INT PRIMARY KEY,
    exploration_year INT,
    probe_name VARCHAR(100)
);

INSERT INTO explored_planet VALUES
(3, 1959, 'Luna 1'),
(4, 1965, 'Mariner 4'),
(5, 1973, 'Pioneer 10'),
(6, 1979, 'Pioneer 11'),
(9, 2015, 'New Horizons');  -- 冥王星，不在 planet 表格中

-- 使用 FULL OUTER JOIN 找出所有行星和探測記錄
-- 可以發現哪些行星尚未被探測，以及哪些探測目標不在行星表中
SELECT
    p.name AS planet_name,
    e.exploration_year,
    e.probe_name
FROM planet p
FULL OUTER JOIN explored_planet e ON p.planet_id = e.planet_id
ORDER BY COALESCE(p.planet_id, e.planet_id);

需要注意的是，MySQL 並不支援 FULL OUTER JOIN 語法。在 MySQL 中，我們需要使用 UNION 來模擬這個功能：

-- 在 MySQL 中模擬 FULL OUTER JOIN
-- 使用 LEFT JOIN 和 RIGHT JOIN 的 UNION
SELECT p.name, e.exploration_year, e.probe_name
FROM planet p
LEFT JOIN explored_planet e ON p.planet_id = e.planet_id

UNION

SELECT p.name, e.exploration_year, e.probe_name
FROM planet p
RIGHT JOIN explored_planet e ON p.planet_id = e.planet_id
WHERE p.planet_id IS NULL;

多表連線的策略與實務

在真實的資料庫應用中，我們經常需要同時連線三個或更多的表格。這種多表連線的查詢需要仔細考慮連接順序和連線型態的選擇，以確保結果正確且效能良好。

讓我們以一個警務資料庫為例，說明如何處理多表連線。這個資料庫包含犯罪事件、發生地點和嫌疑人三個實體：

-- 建立地點資料表
CREATE TABLE location (
    location_id INT PRIMARY KEY,
    location_name VARCHAR(200) NOT NULL,
    district VARCHAR(50),
    latitude DECIMAL(9,6),
    longitude DECIMAL(9,6)
);

-- 建立嫌疑人資料表
CREATE TABLE suspect (
    suspect_id INT PRIMARY KEY,
    suspect_name VARCHAR(100) NOT NULL,
    age INT,
    prior_offenses INT DEFAULT 0
);

-- 建立犯罪事件資料表
CREATE TABLE crime (
    crime_id INT PRIMARY KEY,
    location_id INT NOT NULL,
    suspect_id INT,              -- 可為 NULL，表示嫌疑人未知
    crime_name VARCHAR(200) NOT NULL,
    crime_date DATE,
    severity_level INT,
    FOREIGN KEY (location_id) REFERENCES location(location_id),
    FOREIGN KEY (suspect_id) REFERENCES suspect(suspect_id)
);

-- 插入範例資料
INSERT INTO location VALUES
(1, '主街與榆樹街交叉口', '市中心', 25.0330, 121.5654),
(2, '家庭甜甜圈店', '商業區', 25.0478, 121.5170),
(3, '綠色素食餐廳', '文教區', 25.0173, 121.5397);

INSERT INTO suspect VALUES
(1, '艾琳', 28, 0),
(2, '乎乎', 35, 2);

INSERT INTO crime VALUES
(1, 1, 1, '闖紅燈穿越馬路', '2024-03-15', 1),
(2, 2, 2, '竊盜：甜甜圈', '2024-03-16', 3),
(3, 3, NULL, '以虛假名義接受沙拉', '2024-03-17', 2);

在這個資料模型中，每個犯罪事件都必定有一個發生地點（location_id 為 NOT NULL），但嫌疑人可能尚未確認（suspect_id 可為 NULL）。這個設計反映了真實情況：案發地點是確定的，但嫌疑人可能還在調查中。

-- 查詢所有犯罪事件的詳細資訊
-- 使用 INNER JOIN 連接地點（必定存在）
-- 使用 LEFT JOIN 連接嫌疑人（可能不存在）
SELECT
    c.crime_id,
    c.crime_name,
    c.crime_date,
    l.location_name,
    l.district,
    COALESCE(s.suspect_name, '調查中') AS suspect_name,
    COALESCE(s.prior_offenses, 0) AS prior_offenses
FROM crime c
INNER JOIN location l ON c.location_id = l.location_id
LEFT JOIN suspect s ON c.suspect_id = s.suspect_id
ORDER BY c.crime_date DESC;

這個查詢示範了混合使用不同連線型態的技巧。因為每個犯罪事件都有對應的地點，所以使用 INNER JOIN 連接 location 表格。而嫌疑人可能未知，所以使用 LEFT JOIN 連接 suspect 表格，確保即使沒有嫌疑人的犯罪事件也會出現在結果中。

在處理多表連線時，連接順序可能會影響查詢效能。雖然現代資料庫最佳化器通常會自動選擇最佳的執行計劃，但在某些情況下，手動調整連接順序可以提升效能。一般的原則是先連接能夠過濾掉最多資料的表格，以減少後續連線需要處理的資料量。

自我連接的應用場景

自我連接（Self Join）是指將一個表格與自己進行連線操作。這種技巧在處理階層式資料結構或需要比較同一表格中不同記錄時特別有用。

考慮一個員工資料表，其中包含員工的主管資訊：

-- 建立員工資料表，包含主管關係
CREATE TABLE employee (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    manager_id INT,              -- 指向同一表格中的主管記錄
    FOREIGN KEY (manager_id) REFERENCES employee(employee_id)
);

-- 插入員工資料
INSERT INTO employee VALUES
(1, '王大明', '研發部', 120000, NULL),     -- 最高主管
(2, '李小華', '研發部', 95000, 1),
(3, '張美玲', '研發部', 85000, 1),
(4, '陳建國', '研發部', 75000, 2),
(5, '林雅婷', '行銷部', 110000, NULL),
(6, '黃志明', '行銷部', 80000, 5);

-- 使用自我連接查詢員工及其主管資訊
SELECT
    e.employee_name AS employee,
    e.department,
    e.salary,
    COALESCE(m.employee_name, '無') AS manager,
    COALESCE(m.salary, 0) AS manager_salary
FROM employee e
LEFT JOIN employee m ON e.manager_id = m.employee_id
ORDER BY e.department, e.salary DESC;

在這個查詢中，employee 表格同時作為左表（別名 e，代表員工）和右表（別名 m，代表主管）。透過 LEFT JOIN，我們可以保留所有員工記錄，包括沒有主管的最高階主管。

自我連接還可以用於找出同一表格中相關的記錄，例如找出薪資高於部門平均的員工：

-- 找出薪資高於同部門其他同事的員工
SELECT DISTINCT
    e1.employee_name,
    e1.department,
    e1.salary
FROM employee e1
INNER JOIN employee e2
    ON e1.department = e2.department
    AND e1.salary > e2.salary
ORDER BY e1.department, e1.salary DESC;

交叉連接與笛卡兒積

CROSS JOIN 會產生兩個表格的笛卡兒積（Cartesian Product），也就是將第一個表格的每一筆記錄與第二個表格的所有記錄進行配對。這種連線型態沒有連接條件，結果集的大小等於兩個表格記錄數的乘積。

-- 建立尺寸和顏色表格，用於生成產品組合
CREATE TABLE size (
    size_id INT PRIMARY KEY,
    size_name VARCHAR(20)
);

CREATE TABLE color (
    color_id INT PRIMARY KEY,
    color_name VARCHAR(20)
);

INSERT INTO size VALUES (1, 'S'), (2, 'M'), (3, 'L'), (4, 'XL');
INSERT INTO color VALUES (1, '紅色'), (2, '藍色'), (3, '黑色');

-- 使用 CROSS JOIN 生成所有可能的尺寸-顏色組合
SELECT
    s.size_name,
    c.color_name,
    CONCAT(s.size_name, '-', c.color_name) AS sku_variant
FROM size s
CROSS JOIN color c
ORDER BY s.size_id, c.color_id;

這個查詢會產生 12 筆記錄（4 個尺寸 × 3 個顏色）。CROSS JOIN 在需要生成所有可能組合的場景中非常有用，例如生成產品規格組合、時間區間與類別的交叉表、或者用於某些報表的基礎架構。

然而，由於 CROSS JOIN 會產生大量的記錄，在使用時必須非常謹慎。如果兩個表格各有 1000 筆記錄，CROSS JOIN 會產生 100 萬筆記錄。因此，通常會搭配 WHERE 子句來過濾不需要的組合，或者只在確實需要完整笛卡兒積時才使用。

暫存查詢結果的技術

在處理複雜查詢時，我們經常需要將中間結果暫時儲存起來，以便後續的查詢使用。SQL 提供了幾種不同的機制來達成這個目的，包括臨時表、衍生表和 Common Table Expression（CTE）。

臨時表

臨時表是在資料庫中實際建立的表格，但它只在當前連線或交易期間存在，連線結束後會自動刪除。臨時表適合用於需要多次存取中間結果的場景：

-- 建立臨時表儲存犯罪統計資料
CREATE TEMPORARY TABLE crime_stats AS
SELECT
    l.district,
    COUNT(*) AS crime_count,
    AVG(c.severity_level) AS avg_severity
FROM crime c
INNER JOIN location l ON c.location_id = l.location_id
GROUP BY l.district;

-- 查詢臨時表
SELECT * FROM crime_stats WHERE crime_count > 1;

-- 使用臨時表進行進一步分析
SELECT
    cs.district,
    cs.crime_count,
    cs.avg_severity,
    RANK() OVER (ORDER BY cs.crime_count DESC) AS crime_rank
FROM crime_stats cs;

臨時表的優點是可以建立索引以提升後續查詢的效能，並且可以在多個查詢中重複使用。缺點是需要額外的磁碟空間，且建立和刪除臨時表會產生額外的開銷。

衍生表

衍生表（Derived Table）是在 FROM 子句中的子查詢。它不會實際建立表格，而是作為外層查詢的資料來源：

-- 使用衍生表計算每個地區的犯罪數量並排名
SELECT
    district,
    crime_count,
    CASE
        WHEN crime_count >= 2 THEN '高犯罪區'
        ELSE '一般區域'
    END AS risk_level
FROM (
    SELECT
        l.district,
        COUNT(*) AS crime_count
    FROM crime c
    INNER JOIN location l ON c.location_id = l.location_id
    GROUP BY l.district
) AS district_crimes    -- 衍生表必須有別名
WHERE crime_count > 0
ORDER BY crime_count DESC;

衍生表的優點是不需要額外的資源來儲存資料，適合一次性使用的中間結果。缺點是在複雜查詢中可能會使程式碼變得難以閱讀，且無法在查詢中多次參照同一個衍生表。

Common Table Expression

CTE 是 SQL:1999 標準引入的功能，它提供了一種更清晰、更易讀的方式來定義暫存結果集。CTE 使用 WITH 關鍵字定義，可以在後續的查詢中像一般表格一樣參照：

-- 使用 CTE 進行多層次的資料分析
WITH crime_by_location AS (
    -- 第一個 CTE：計算每個地點的犯罪統計
    SELECT
        c.location_id,
        l.location_name,
        l.district,
        COUNT(*) AS crime_count,
        AVG(c.severity_level) AS avg_severity
    FROM crime c
    INNER JOIN location l ON c.location_id = l.location_id
    GROUP BY c.location_id, l.location_name, l.district
),
district_summary AS (
    -- 第二個 CTE：彙總地區統計
    SELECT
        district,
        SUM(crime_count) AS total_crimes,
        AVG(avg_severity) AS district_avg_severity
    FROM crime_by_location
    GROUP BY district
)
-- 主查詢：結合兩個 CTE 的資料
SELECT
    cbl.location_name,
    cbl.district,
    cbl.crime_count,
    ds.total_crimes AS district_total,
    ROUND(cbl.crime_count * 100.0 / ds.total_crimes, 2) AS percentage
FROM crime_by_location cbl
INNER JOIN district_summary ds ON cbl.district = ds.district
ORDER BY cbl.district, cbl.crime_count DESC;

CTE 的優點是語法清晰、可讀性高，且可以在同一個查詢中多次參照。某些資料庫還支援遞迴 CTE，可以用於處理階層式資料結構。CTE 通常是現代 SQL 程式設計中處理複雜查詢的首選方式。

@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 16
skinparam minClassWidth 100

rectangle "暫存結果集技術比較" as title

rectangle "臨時表" as temp {
    rectangle "實際儲存於磁碟"
    rectangle "可建立索引"
    rectangle "可多次存取"
    rectangle "連線結束自動刪除"
}

rectangle "衍生表" as derived {
    rectangle "FROM 子句中的子查詢"
    rectangle "不佔用額外空間"
    rectangle "只能使用一次"
    rectangle "必須指定別名"
}

rectangle "CTE" as cte {
    rectangle "WITH 關鍵字定義"
    rectangle "語法清晰易讀"
    rectangle "同查詢可多次參照"
    rectangle "支援遞迴查詢"
}

temp -[hidden]right-> derived
derived -[hidden]right-> cte

@enduml

資料庫設計的正規化原則

良好的資料庫設計是高效查詢的基礎。正規化（Normalization）是一套系統性的設計原則，目的是減少資料冗餘、避免更新異常，並確保資料完整性。理解正規化的各個層級，有助於我們設計出結構良好且易於維護的資料庫。

第一正規形式（1NF）要求每個欄位都只包含原子值（Atomic Value），不可再分割。這意味著我們不應該在單一欄位中儲存多個值，例如將多個電話號碼以逗號分隔儲存在同一個欄位中。

第二正規形式（2NF）要求滿足 1NF，且所有非鍵欄位都完全依賴於主鍵。這主要針對複合主鍵的情況，要求非鍵欄位不能只依賴於主鍵的一部分。

第三正規形式（3NF）要求滿足 2NF，且所有非鍵欄位都直接依賴於主鍵，不存在遞移依賴。這意味著非鍵欄位之間不應該存在依賴關係。

讓我們以葡萄酒產業資料庫為例，說明如何設計符合正規化原則的資料模型：

-- 國家資料表
CREATE TABLE country (
    country_id INT PRIMARY KEY,
    country_name VARCHAR(100) NOT NULL,
    continent VARCHAR(50)
);

-- 產區資料表
CREATE TABLE region (
    region_id INT PRIMARY KEY,
    region_name VARCHAR(100) NOT NULL,
    country_id INT NOT NULL,
    climate_type VARCHAR(50),
    FOREIGN KEY (country_id) REFERENCES country(country_id)
);

-- 葡萄種植區資料表
CREATE TABLE viticultural_area (
    area_id INT PRIMARY KEY,
    area_name VARCHAR(100) NOT NULL,
    region_id INT NOT NULL,
    elevation_meters INT,
    soil_type VARCHAR(100),
    FOREIGN KEY (region_id) REFERENCES region(region_id)
);

-- 酒種類型資料表
CREATE TABLE wine_type (
    wine_type_id INT PRIMARY KEY,
    wine_type_name VARCHAR(100) NOT NULL,
    grape_variety VARCHAR(100),
    typical_aging_months INT
);

-- 酒莊資料表
CREATE TABLE winery (
    winery_id INT PRIMARY KEY,
    winery_name VARCHAR(200) NOT NULL,
    area_id INT NOT NULL,
    founded_year INT,
    offers_tours BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (area_id) REFERENCES viticultural_area(area_id)
);

-- 酒莊產品組合資料表（多對多關聯的聯結表）
CREATE TABLE portfolio (
    winery_id INT,
    wine_type_id INT,
    is_signature BOOLEAN DEFAULT FALSE,  -- 是否為招牌產品
    annual_production INT,               -- 年產量（瓶）
    PRIMARY KEY (winery_id, wine_type_id),
    FOREIGN KEY (winery_id) REFERENCES winery(winery_id),
    FOREIGN KEY (wine_type_id) REFERENCES wine_type(wine_type_id)
);

這個設計遵循了正規化原則：國家、產區、種植區、酒種類型和酒莊各自獨立成表格，避免資料重複。酒莊與酒種類型之間是多對多的關係（一個酒莊可以生產多種酒，一種酒可以由多個酒莊生產），透過 portfolio 聯結表來實現。

-- 查詢特定產區的所有酒莊及其產品
WITH winery_details AS (
    SELECT
        w.winery_id,
        w.winery_name,
        w.founded_year,
        w.offers_tours,
        va.area_name,
        r.region_name,
        c.country_name
    FROM winery w
    INNER JOIN viticultural_area va ON w.area_id = va.area_id
    INNER JOIN region r ON va.region_id = r.region_id
    INNER JOIN country c ON r.country_id = c.country_id
)
SELECT
    wd.winery_name,
    wd.area_name,
    wd.country_name,
    wt.wine_type_name,
    wt.grape_variety,
    p.annual_production,
    CASE WHEN p.is_signature THEN '是' ELSE '否' END AS signature_wine
FROM winery_details wd
INNER JOIN portfolio p ON wd.winery_id = p.winery_id
INNER JOIN wine_type wt ON p.wine_type_id = wt.wine_type_id
ORDER BY wd.country_name, wd.area_name, wd.winery_name;

反正規化與效能考量

雖然正規化可以減少資料冗餘並維護資料完整性，但過度正規化可能會導致查詢需要大量的 JOIN 操作，影響效能。在某些情況下，適度的反正規化（Denormalization）可以提升查詢效率。

反正規化的常見策略包括：在子表格中冗餘儲存父表格的某些欄位、預先計算並儲存統計數值、或者將經常一起查詢的資料合併到同一個表格中。然而，反正規化會增加維護資料一致性的複雜度，因此需要謹慎評估。

-- 反正規化範例：在訂單表中冗餘儲存客戶名稱
-- 優點：查詢訂單時不需要 JOIN 客戶表
-- 缺點：客戶改名時需要更新所有相關訂單

CREATE TABLE order_denormalized (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    customer_name VARCHAR(100),  -- 冗餘欄位
    order_date DATE,
    total_amount DECIMAL(10,2)
);

-- 查詢不需要 JOIN
SELECT order_id, customer_name, order_date, total_amount
FROM order_denormalized
WHERE order_date >= '2024-01-01';

決定是否採用反正規化時，需要考慮以下因素：讀取與寫入的比例（讀取頻繁的系統更適合反正規化）、資料更新的頻率（頻繁更新的資料不適合冗餘儲存）、以及應用程式的效能需求。

@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 16
skinparam minClassWidth 100

rectangle "資料庫設計權衡" as title

rectangle "正規化" as norm {
    rectangle "減少資料冗餘"
    rectangle "維護資料一致性"
    rectangle "較少的儲存空間"
    rectangle "需要較多 JOIN"
}

rectangle "反正規化" as denorm {
    rectangle "查詢效能提升"
    rectangle "減少 JOIN 操作"
    rectangle "增加儲存空間"
    rectangle "維護一致性較複雜"
}

norm -[hidden]right-> denorm

note bottom of norm
適合：
寫入頻繁的系統
資料完整性要求高
儲存空間有限
end note

note bottom of denorm
適合：
讀取頻繁的系統
效能要求高
資料相對穩定
end note

@enduml

查詢最佳化的實務技巧

除了良好的資料庫設計之外，撰寫高效的查詢語句也是最佳化系統效能的重要環節。以下是一些實用的查詢最佳化技巧：

索引的有效運用

索引是提升查詢效能最有效的手段之一。在設計索引時，應該優先考慮經常用於 WHERE 子句、JOIN 條件和 ORDER BY 子句的欄位：

-- 為經常查詢的欄位建立索引
CREATE INDEX idx_crime_date ON crime(crime_date);
CREATE INDEX idx_crime_location ON crime(location_id);
CREATE INDEX idx_location_district ON location(district);

-- 複合索引：適用於經常一起出現在查詢條件中的欄位
CREATE INDEX idx_crime_date_severity ON crime(crime_date, severity_level);

避免在索引欄位上使用函數

當我們在索引欄位上使用函數時，資料庫引擎無法使用索引，會導致全表掃描：

-- 不佳：在索引欄位上使用函數
SELECT * FROM crime WHERE YEAR(crime_date) = 2024;

-- 較佳：使用範圍查詢
SELECT * FROM crime
WHERE crime_date >= '2024-01-01' AND crime_date < '2025-01-01';

使用 EXISTS 替代 IN 子查詢

在處理大型子查詢時，EXISTS 通常比 IN 更有效率：

-- 使用 IN（可能效能較差）
SELECT * FROM crime
WHERE location_id IN (
    SELECT location_id FROM location WHERE district = '市中心'
);

-- 使用 EXISTS（通常效能較佳）
SELECT * FROM crime c
WHERE EXISTS (
    SELECT 1 FROM location l
    WHERE l.location_id = c.location_id AND l.district = '市中心'
);

限制結果集大小

在開發和測試階段，使用 LIMIT 限制結果集大小可以加快查詢速度並減少資源消耗：

-- 限制結果數量
SELECT c.crime_name, l.location_name, s.suspect_name
FROM crime c
INNER JOIN location l ON c.location_id = l.location_id
LEFT JOIN suspect s ON c.suspect_id = s.suspect_id
ORDER BY c.crime_date DESC
LIMIT 100;

使用 EXPLAIN 分析查詢計劃

大多數資料庫系統都提供 EXPLAIN 命令，可以顯示查詢的執行計劃，幫助識別效能瓶頸：

-- 分析查詢執行計劃
EXPLAIN SELECT c.crime_name, l.location_name
FROM crime c
INNER JOIN location l ON c.location_id = l.location_id
WHERE l.district = '市中心';

透過分析執行計劃，我們可以了解資料庫是否使用了索引、預計掃描的記錄數、以及 JOIN 的執行方式，從而針對性地進行最佳化。

綜合應用案例

讓我們透過一個完整的案例，綜合運用本文介紹的各種技術。假設我們需要為警務資料庫建立一個犯罪分析報表，顯示各地區的犯罪統計、嫌疑人資訊，以及與歷史資料的比較：

-- 綜合案例：犯罪分析報表
-- 使用多個 CTE 進行分層分析

WITH current_month_crimes AS (
    -- 統計本月各地區的犯罪數量
    SELECT
        l.district,
        COUNT(*) AS crime_count,
        AVG(c.severity_level) AS avg_severity,
        COUNT(c.suspect_id) AS identified_suspects
    FROM crime c
    INNER JOIN location l ON c.location_id = l.location_id
    WHERE c.crime_date >= DATE_TRUNC('month', CURRENT_DATE)
    GROUP BY l.district
),
suspect_profile AS (
    -- 分析嫌疑人特徵
    SELECT
        l.district,
        AVG(s.age) AS avg_suspect_age,
        AVG(s.prior_offenses) AS avg_prior_offenses
    FROM crime c
    INNER JOIN location l ON c.location_id = l.location_id
    INNER JOIN suspect s ON c.suspect_id = s.suspect_id
    WHERE c.crime_date >= DATE_TRUNC('month', CURRENT_DATE)
    GROUP BY l.district
),
district_ranking AS (
    -- 計算各地區的犯罪排名
    SELECT
        district,
        crime_count,
        RANK() OVER (ORDER BY crime_count DESC) AS crime_rank,
        PERCENT_RANK() OVER (ORDER BY crime_count DESC) AS percentile
    FROM current_month_crimes
)
-- 主查詢：整合所有分析結果
SELECT
    cmc.district AS 地區,
    cmc.crime_count AS 犯罪數量,
    dr.crime_rank AS 排名,
    ROUND(cmc.avg_severity, 2) AS 平均嚴重程度,
    cmc.identified_suspects AS 已辨識嫌疑人,
    cmc.crime_count - cmc.identified_suspects AS 未破案件,
    ROUND(
        cmc.identified_suspects * 100.0 / NULLIF(cmc.crime_count, 0), 1
    ) AS 破案率,
    ROUND(COALESCE(sp.avg_suspect_age, 0), 1) AS 嫌疑人平均年齡,
    ROUND(COALESCE(sp.avg_prior_offenses, 0), 1) AS 平均前科次數
FROM current_month_crimes cmc
LEFT JOIN suspect_profile sp ON cmc.district = sp.district
INNER JOIN district_ranking dr ON cmc.district = dr.district
ORDER BY dr.crime_rank;

這個查詢展示了如何結合多個 CTE、各種 JOIN 型態、視窗函數和條件運算來產生一份完整的分析報表。每個 CTE 負責一個特定的分析任務，最後在主查詢中整合所有結果。這種模組化的方式使程式碼更易於理解、維護和除錯。

資料庫查詢技術是軟體開發中不可或缺的核心能力。從基本的內連接到複雜的多表連線，從簡單的子查詢到強大的 CTE，這些技術共同構成了資料處理的完整工具箱。理解每種技術的特性和適用場景，能夠幫助我們在實務中做出正確的選擇。

良好的資料庫設計是高效查詢的基礎。遵循正規化原則可以確保資料的完整性和一致性，而適度的反正規化則可以在必要時提升效能。在設計和實作過程中，需要根據具體的應用需求在這兩者之間取得平衡。

查詢最佳化是一個持續的過程，需要結合索引設計、查詢改寫和執行計劃分析等多種手段。透過不斷的實踐和經驗累積，我們可以逐漸掌握撰寫高效查詢的技巧，為應用程式提供可靠且高效的資料支援。

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。