在關聯式資料庫的世界中，資料通常分散儲存在多個相關聯的資料表中，這種設計稱為正規化，目的是減少資料重複並確保資料一致性。然而，當我們需要從多個資料表中取得完整資訊時，就必須使用連線操作來結合這些資料表。SQL 的 JOIN 語法提供了多種連線方式，每種方式都有其特定的使用場景和結果集特性，選擇正確的連線類型對於查詢的正確性和效能都至關重要。
表格連線是 SQL 查詢中最強大也最常用的功能之一。透過連線操作，我們可以在單一查詢中從多個資料表擷取資料，建立資料之間的關聯關係，並產生有意義的結果集。無論是簡單的兩表連線還是複雜的多表連線，理解各種連線類型的行為和特性都是資料庫開發者必備的核心技能。
本文將從最基本的連線概念開始，逐步深入到各種連線類型的詳細說明，包括內部連線、外部連線、交叉連線、自然連線以及自我連線。我們會透過實際的範例來展示每種連線的語法和結果，並探討它們在不同場景中的應用。此外，我們也會討論連線查詢的效能考量，以及如何撰寫高效的多表查詢。
連線基礎概念與資料表設計
在深入探討各種連線類型之前，我們需要先理解為什麼需要連線操作，以及連線是如何運作的。關聯式資料庫的核心思想是將資料分割成多個相關聯的資料表，透過主鍵和外鍵建立資料表之間的關係。這種設計避免了資料重複，但也意味著完整的資訊可能分散在多個資料表中。
讓我們建立一個完整的範例資料庫來說明連線操作。我們將創建一個包含員工、部門、專案和工作分配的資料庫結構，這個結構涵蓋了一對多和多對多的關係，可以展示各種連線情境。
-- 建立部門資料表
-- 儲存公司各部門的基本資訊
CREATE TABLE department (
dept_id INT PRIMARY KEY AUTO_INCREMENT, -- 部門唯一識別碼
dept_name VARCHAR(50) NOT NULL, -- 部門名稱
location VARCHAR(100), -- 部門所在位置
budget DECIMAL(15, 2) -- 部門預算
);
-- 建立員工資料表
-- 儲存員工的個人資訊與所屬部門
CREATE TABLE employee (
emp_id INT PRIMARY KEY AUTO_INCREMENT, -- 員工唯一識別碼
emp_name VARCHAR(100) NOT NULL, -- 員工姓名
email VARCHAR(100) UNIQUE, -- 電子郵件
hire_date DATE, -- 到職日期
salary DECIMAL(10, 2), -- 薪資
dept_id INT, -- 所屬部門外鍵
manager_id INT, -- 主管員工編號（自我參照）
FOREIGN KEY (dept_id) REFERENCES department(dept_id),
FOREIGN KEY (manager_id) REFERENCES employee(emp_id)
);
-- 建立專案資料表
-- 儲存公司各專案的資訊
CREATE TABLE project (
project_id INT PRIMARY KEY AUTO_INCREMENT, -- 專案唯一識別碼
project_name VARCHAR(100) NOT NULL, -- 專案名稱
start_date DATE, -- 開始日期
end_date DATE, -- 結束日期
budget DECIMAL(15, 2), -- 專案預算
dept_id INT, -- 負責部門
FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);
-- 建立專案分配資料表
-- 記錄員工與專案的多對多關係
CREATE TABLE project_assignment (
assignment_id INT PRIMARY KEY AUTO_INCREMENT,
emp_id INT NOT NULL, -- 員工編號
project_id INT NOT NULL, -- 專案編號
role VARCHAR(50), -- 在專案中的角色
hours_allocated INT, -- 分配時數
FOREIGN KEY (emp_id) REFERENCES employee(emp_id),
FOREIGN KEY (project_id) REFERENCES project(project_id),
UNIQUE KEY (emp_id, project_id) -- 確保同一員工不會重複分配到同一專案
);
現在讓我們插入一些測試資料，這些資料將用於後續所有連線範例的示範。
-- 插入部門資料
INSERT INTO department (dept_name, location, budget)
VALUES
('研發部', '台北總部 3F', 5000000),
('行銷部', '台北總部 5F', 3000000),
('人資部', '台北總部 2F', 1500000),
('財務部', '台北總部 2F', 2000000),
('業務部', '台中分公司', 4000000);
-- 插入員工資料
-- 注意 manager_id 的階層關係
INSERT INTO employee (emp_name, email, hire_date, salary, dept_id, manager_id)
VALUES
('陳大明', '[email protected]', '2018-03-15', 85000, 1, NULL),
('林小華', '[email protected]', '2019-06-01', 72000, 1, 1),
('王美玲', '[email protected]', '2020-01-10', 68000, 1, 1),
('張志偉', '[email protected]', '2017-08-20', 90000, 2, NULL),
('李佳慧', '[email protected]', '2021-04-05', 58000, 2, 4),
('黃建國', '[email protected]', '2019-11-12', 75000, 3, NULL),
('劉雅婷', '[email protected]', '2022-02-28', 52000, 3, 6),
('吳家豪', '[email protected]', '2020-07-15', 78000, 4, NULL),
('趙心怡', '[email protected]', '2023-01-03', 48000, NULL, NULL),
('周文傑', '[email protected]', '2018-09-25', 82000, 5, NULL);
-- 插入專案資料
INSERT INTO project (project_name, start_date, end_date, budget, dept_id)
VALUES
('電商平台開發', '2023-01-01', '2023-12-31', 2000000, 1),
('品牌形象重塑', '2023-03-01', '2023-09-30', 800000, 2),
('人才招募系統', '2023-06-01', '2024-03-31', 500000, 3),
('行動應用程式', '2023-04-01', '2024-06-30', 1500000, 1),
('市場調查研究', '2023-07-01', '2023-10-31', 300000, NULL);
-- 插入專案分配資料
INSERT INTO project_assignment (emp_id, project_id, role, hours_allocated)
VALUES
(1, 1, '專案經理', 160),
(2, 1, '前端工程師', 320),
(3, 1, '後端工程師', 320),
(4, 2, '專案經理', 120),
(5, 2, '行銷專員', 240),
(1, 4, '技術顧問', 80),
(2, 4, '前端工程師', 280),
(6, 3, '專案經理', 160),
(7, 3, '人資專員', 200);
INNER JOIN 內部連線
INNER JOIN 是最常用的連線類型，它只會傳回兩個資料表中都有匹配資料的記錄。換句話說，只有當連線條件在兩個資料表中都能找到對應的值時，該記錄才會出現在結果集中。
INNER JOIN 的基本語法如下，其中 ON 子句指定了連線條件，通常是比較兩個資料表中的相關欄位。
-- INNER JOIN 基本語法
-- 只傳回兩個資料表中都有匹配的記錄
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
讓我們用實際範例來說明 INNER JOIN 的使用。假設我們想要查詢所有員工及其所屬部門的資訊。
-- 查詢員工與部門資訊
-- 使用 INNER JOIN 連結員工和部門資料表
SELECT
e.emp_id,
e.emp_name,
e.salary,
d.dept_name,
d.location
FROM employee e
INNER JOIN department d ON e.dept_id = d.dept_id
ORDER BY d.dept_name, e.emp_name;
-- 注意：員工「趙心怡」沒有指定部門（dept_id 為 NULL）
-- 因此她不會出現在結果中
執行上述查詢後，我們會得到 9 筆記錄，因為有一位員工沒有指定部門。這就是 INNER JOIN 的特性：它會排除任何一邊沒有匹配的記錄。
INNER JOIN 也可以使用 USING 子句來簡化語法，當兩個資料表的連線欄位名稱相同時特別方便。
-- 使用 USING 子句簡化連線語法
-- 當連線欄位名稱相同時可使用此語法
SELECT
e.emp_name,
d.dept_name,
d.budget
FROM employee e
INNER JOIN department d USING (dept_id) -- 等同於 ON e.dept_id = d.dept_id
ORDER BY d.budget DESC;
INNER JOIN 可以包含多個連線條件，使用 AND 或 OR 運算子來組合條件。這在需要更精確控制連線行為時非常有用。
-- 多條件 INNER JOIN
-- 查詢特定條件下的員工與專案分配
SELECT
e.emp_name,
p.project_name,
pa.role,
pa.hours_allocated
FROM employee e
INNER JOIN project_assignment pa ON e.emp_id = pa.emp_id
INNER JOIN project p ON pa.project_id = p.project_id
WHERE pa.hours_allocated >= 200 -- 只顯示分配時數超過 200 的記錄
ORDER BY pa.hours_allocated 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 16
skinparam minClassWidth 100
title INNER JOIN 運作原理
rectangle "Table A" as a {
card "1" as a1
card "2" as a2
card "3" as a3
card "4" as a4
}
rectangle "Table B" as b {
card "2" as b2
card "3" as b3
card "4" as b4
card "5" as b5
}
rectangle "Result" as r {
card "2" as r2
card "3" as r3
card "4" as r4
}
a2 --> r2 : 匹配
a3 --> r3 : 匹配
a4 --> r4 : 匹配
b2 --> r2
b3 --> r3
b4 --> r4
note bottom of r
只保留兩邊都有的記錄
A 的 1 和 B 的 5 被排除
end note
@enduml
LEFT OUTER JOIN 左外部連線
LEFT OUTER JOIN（或簡稱 LEFT JOIN）會傳回左邊資料表的所有記錄，以及右邊資料表中匹配的記錄。如果右邊資料表沒有匹配的記錄，則對應的欄位會顯示 NULL 值。這種連線方式特別適合用於查詢主資料表的所有記錄，同時附帶相關資料表的資訊。
-- LEFT JOIN 基本語法
-- 傳回左表所有記錄，右表匹配的記錄或 NULL
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
讓我們用實際範例來說明 LEFT JOIN 的使用。假設我們想要查詢所有員工及其所屬部門，包括尚未分配部門的員工。
-- 查詢所有員工與部門資訊
-- 包含尚未分配部門的員工
SELECT
e.emp_id,
e.emp_name,
e.hire_date,
COALESCE(d.dept_name, '尚未分配') AS dept_name, -- 處理 NULL 值
d.location
FROM employee e
LEFT JOIN department d ON e.dept_id = d.dept_id
ORDER BY e.emp_id;
-- 結果會包含所有 10 位員工
-- 其中趙心怡的部門資訊會顯示 NULL 或 '尚未分配'
LEFT JOIN 的一個重要應用是找出沒有關聯記錄的資料。例如，找出沒有被分配到任何專案的員工。
-- 找出沒有專案分配的員工
-- 使用 LEFT JOIN 配合 IS NULL 條件
SELECT
e.emp_id,
e.emp_name,
e.dept_id
FROM employee e
LEFT JOIN project_assignment pa ON e.emp_id = pa.emp_id
WHERE pa.assignment_id IS NULL -- 只保留沒有匹配的記錄
ORDER BY e.emp_name;
-- 結果會顯示沒有參與任何專案的員工
我們也可以使用 LEFT JOIN 來建立報表，顯示所有部門及其員工人數，包括沒有員工的部門。
-- 統計各部門員工人數
-- 包含沒有員工的部門
SELECT
d.dept_id,
d.dept_name,
COUNT(e.emp_id) AS employee_count, -- COUNT 會忽略 NULL
COALESCE(SUM(e.salary), 0) AS total_salary,
COALESCE(AVG(e.salary), 0) AS avg_salary
FROM department d
LEFT JOIN employee e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY employee_count DESC;
RIGHT OUTER JOIN 右外部連線
RIGHT OUTER JOIN（或簡稱 RIGHT JOIN）與 LEFT JOIN 相反，它會傳回右邊資料表的所有記錄，以及左邊資料表中匹配的記錄。如果左邊資料表沒有匹配的記錄，則對應的欄位會顯示 NULL 值。
實際上，RIGHT JOIN 可以透過交換資料表的順序並使用 LEFT JOIN 來達到相同的效果。因此，許多開發者傾向於一致使用 LEFT JOIN，以提高程式碼的可讀性。
-- RIGHT JOIN 基本語法
-- 傳回右表所有記錄，左表匹配的記錄或 NULL
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
以下範例展示如何使用 RIGHT JOIN 查詢所有部門及其員工，包括沒有員工的部門。
-- 查詢所有部門與員工資訊
-- 使用 RIGHT JOIN 確保所有部門都會顯示
SELECT
d.dept_id,
d.dept_name,
e.emp_name,
e.salary
FROM employee e
RIGHT JOIN department d ON e.dept_id = d.dept_id
ORDER BY d.dept_name, e.emp_name;
-- 等效的 LEFT JOIN 寫法
SELECT
d.dept_id,
d.dept_name,
e.emp_name,
e.salary
FROM department d
LEFT JOIN employee e ON d.dept_id = e.dept_id
ORDER BY d.dept_name, e.emp_name;
RIGHT JOIN 也可以用於找出沒有關聯記錄的資料，例如找出沒有員工的部門。
-- 找出沒有員工的部門
-- 使用 RIGHT JOIN 配合 IS NULL 條件
SELECT
d.dept_id,
d.dept_name,
d.location
FROM employee e
RIGHT JOIN department d ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;
FULL OUTER JOIN 完全外部連線
FULL OUTER JOIN 會傳回兩個資料表的所有記錄。當某一邊沒有匹配的記錄時，對應的欄位會顯示 NULL 值。這種連線方式可以同時顯示兩個資料表中所有的資料，無論是否有匹配。
需要注意的是，MySQL 原生不支援 FULL OUTER JOIN 語法，但可以透過結合 LEFT JOIN 和 RIGHT JOIN 並使用 UNION 來模擬實現。
-- MySQL 模擬 FULL OUTER JOIN
-- 使用 LEFT JOIN 和 RIGHT JOIN 配合 UNION
SELECT
e.emp_id,
e.emp_name,
d.dept_id,
d.dept_name
FROM employee e
LEFT JOIN department d ON e.dept_id = d.dept_id
UNION
SELECT
e.emp_id,
e.emp_name,
d.dept_id,
d.dept_name
FROM employee e
RIGHT JOIN department d ON e.dept_id = d.dept_id;
-- 結果包含：
-- 1. 所有有部門的員工
-- 2. 沒有部門的員工（dept_name 為 NULL）
-- 3. 沒有員工的部門（emp_name 為 NULL）
以下是一個更完整的範例，展示如何使用模擬的 FULL OUTER JOIN 來分析員工與專案的關係。
-- 完整的員工與專案關係分析
-- 顯示所有員工和所有專案的關係
SELECT
COALESCE(e.emp_name, '無員工') AS emp_name,
COALESCE(p.project_name, '無專案') AS project_name,
pa.role,
pa.hours_allocated
FROM employee e
LEFT JOIN project_assignment pa ON e.emp_id = pa.emp_id
LEFT JOIN project p ON pa.project_id = p.project_id
UNION
SELECT
COALESCE(e.emp_name, '無員工') AS emp_name,
COALESCE(p.project_name, '無專案') AS project_name,
pa.role,
pa.hours_allocated
FROM project p
LEFT JOIN project_assignment pa ON p.project_id = pa.project_id
LEFT JOIN employee e ON pa.emp_id = e.emp_id
WHERE e.emp_id IS NULL; -- 只取沒有員工分配的專案
@startuml
!define DISABLE_LINK
!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
title 各種 JOIN 類型比較
rectangle "INNER JOIN" as inner
note right of inner
只保留匹配記錄
end note
rectangle "LEFT JOIN" as left
note right of left
左表全部 + 右表匹配
end note
rectangle "RIGHT JOIN" as right
note right of right
右表全部 + 左表匹配
end note
rectangle "FULL OUTER JOIN" as full
note right of full
兩表全部記錄
end note
inner -[hidden]down- left
left -[hidden]down- right
right -[hidden]down- full
@enduml
CROSS JOIN 交叉連線
CROSS JOIN 會產生兩個資料表的笛卡爾積，也就是第一個資料表的每一筆記錄都會與第二個資料表的每一筆記錄組合。如果第一個資料表有 m 筆記錄，第二個資料表有 n 筆記錄，則結果會有 m × n 筆記錄。
由於 CROSS JOIN 會產生大量的記錄，因此在使用時需要特別謹慎，通常只用於特定的場景，例如生成所有可能的組合。
-- CROSS JOIN 基本語法
-- 產生兩個資料表的笛卡爾積
SELECT columns
FROM table1
CROSS JOIN table2;
以下是一個實際應用範例，假設我們有餐點和飲料的資料表，想要生成所有可能的套餐組合。
-- 建立餐點與飲料資料表
CREATE TABLE main_dish (
dish_id INT PRIMARY KEY,
dish_name VARCHAR(50),
price DECIMAL(6, 2)
);
CREATE TABLE beverage (
beverage_id INT PRIMARY KEY,
beverage_name VARCHAR(50),
price DECIMAL(6, 2)
);
-- 插入測試資料
INSERT INTO main_dish VALUES
(1, '牛肉麵', 150),
(2, '雞腿飯', 120),
(3, '排骨便當', 100);
INSERT INTO beverage VALUES
(1, '紅茶', 25),
(2, '綠茶', 30),
(3, '可樂', 35);
-- 生成所有套餐組合
-- 使用 CROSS JOIN 產生笛卡爾積
SELECT
m.dish_name,
b.beverage_name,
m.price + b.price AS combo_price
FROM main_dish m
CROSS JOIN beverage b
ORDER BY combo_price;
-- 結果會有 3 × 3 = 9 筆記錄
-- 每種餐點都會與每種飲料組合
CROSS JOIN 的另一個應用是生成時間序列或日期範圍的所有組合，這在報表生成時特別有用。
-- 生成日期與部門的所有組合
-- 用於建立完整的報表框架
WITH date_range AS (
SELECT DATE('2023-01-01') AS report_date
UNION SELECT DATE('2023-02-01')
UNION SELECT DATE('2023-03-01')
)
SELECT
dr.report_date,
d.dept_name
FROM date_range dr
CROSS JOIN department d
ORDER BY dr.report_date, d.dept_name;
NATURAL JOIN 自然連線
NATURAL JOIN 是一種特殊的連線方式，它會自動根據兩個資料表中具有相同名稱的欄位進行連線。使用 NATURAL JOIN 不需要指定連線條件，資料庫會自動找出相同名稱的欄位並進行匹配。
雖然 NATURAL JOIN 語法簡潔，但由於它會自動匹配所有相同名稱的欄位，可能會產生意外的結果，特別是當資料表中有多個相同名稱的欄位時。因此，在實務中建議謹慎使用，並優先考慮使用明確指定條件的 JOIN。
-- NATURAL JOIN 基本語法
-- 自動根據相同名稱的欄位進行連線
SELECT columns
FROM table1
NATURAL JOIN table2;
以下範例展示 NATURAL JOIN 的使用，連結員工和部門資料表。
-- 使用 NATURAL JOIN 連結員工和部門
-- 自動根據 dept_id 進行連線
SELECT
emp_name,
dept_name,
salary
FROM employee
NATURAL JOIN department;
-- 等效的 INNER JOIN 寫法
SELECT
e.emp_name,
d.dept_name,
e.salary
FROM employee e
INNER JOIN department d ON e.dept_id = d.dept_id;
NATURAL JOIN 可能產生問題的例子：當兩個資料表有多個相同名稱的欄位時。
-- 潛在問題：多個相同名稱的欄位
-- 假設 employee 和 department 都有 created_at 和 updated_at 欄位
-- NATURAL JOIN 會要求這些欄位也要匹配，可能導致沒有結果
-- 建議使用明確的 JOIN 條件
SELECT
e.emp_name,
d.dept_name
FROM employee e
INNER JOIN department d ON e.dept_id = d.dept_id; -- 明確指定連線欄位
自我連線 Self Join
自我連線是指一個資料表與自己進行連線，這在資料表包含階層關係時特別有用。例如，員工資料表中每位員工都有一個主管欄位，而主管本身也是員工，這就形成了一個階層結構。
要實現自我連線，我們需要為同一個資料表指定不同的別名，然後像連結兩個不同的資料表一樣進行連線。
-- 自我連線：查詢員工與其主管
-- 使用不同別名來區分同一資料表的兩個實例
SELECT
e.emp_name AS employee_name,
e.salary AS employee_salary,
m.emp_name AS manager_name,
m.salary AS manager_salary
FROM employee e
LEFT JOIN employee m ON e.manager_id = m.emp_id -- e 為員工，m 為主管
ORDER BY m.emp_name, e.emp_name;
-- 使用 LEFT JOIN 確保沒有主管的員工也會顯示
-- （通常是最高階主管）
自我連線也可以用於找出具有特定關係的記錄，例如找出同一部門的員工配對。
-- 找出同一部門的員工配對
-- 排除自己與自己的配對
SELECT
e1.emp_name AS employee_1,
e2.emp_name AS employee_2,
d.dept_name
FROM employee e1
INNER JOIN employee e2 ON e1.dept_id = e2.dept_id AND e1.emp_id < e2.emp_id
INNER JOIN department d ON e1.dept_id = d.dept_id
ORDER BY d.dept_name, e1.emp_name;
-- 使用 e1.emp_id < e2.emp_id 避免重複配對
-- 例如只顯示 (A, B) 而不顯示 (B, A)
自我連線的另一個應用是建立組織階層報表。
-- 建立多層級的組織階層報表
-- 使用多次自我連線
SELECT
e.emp_name AS employee,
m1.emp_name AS direct_manager,
m2.emp_name AS skip_level_manager
FROM employee e
LEFT JOIN employee m1 ON e.manager_id = m1.emp_id
LEFT JOIN employee m2 ON m1.manager_id = m2.emp_id
ORDER BY m2.emp_name, m1.emp_name, e.emp_name;
多表連線與複雜查詢
在實際應用中，我們經常需要連結三個或更多的資料表。多表連線的原則與兩表連線相同，只是需要逐一指定各資料表之間的連線條件。
-- 多表連線範例：查詢完整的專案分配資訊
-- 連結員工、部門、專案分配和專案四個資料表
SELECT
e.emp_name,
d.dept_name AS employee_dept,
p.project_name,
pd.dept_name AS project_dept,
pa.role,
pa.hours_allocated,
p.budget AS project_budget
FROM employee e
INNER JOIN department d ON e.dept_id = d.dept_id
INNER JOIN project_assignment pa ON e.emp_id = pa.emp_id
INNER JOIN project p ON pa.project_id = p.project_id
LEFT JOIN department pd ON p.dept_id = pd.dept_id
ORDER BY p.project_name, e.emp_name;
當查詢變得複雜時，使用資料表別名可以大幅提高程式碼的可讀性。以下是一個完整的報表查詢範例。
-- 完整的專案報表查詢
-- 包含專案資訊、負責部門、參與人員與預算使用
SELECT
p.project_id,
p.project_name,
p.start_date,
p.end_date,
DATEDIFF(p.end_date, p.start_date) AS duration_days,
COALESCE(d.dept_name, '未指定') AS responsible_dept,
p.budget AS total_budget,
COUNT(DISTINCT pa.emp_id) AS team_size,
SUM(pa.hours_allocated) AS total_hours,
GROUP_CONCAT(e.emp_name ORDER BY pa.role SEPARATOR ', ') AS team_members
FROM project p
LEFT JOIN department d ON p.dept_id = d.dept_id
LEFT JOIN project_assignment pa ON p.project_id = pa.project_id
LEFT JOIN employee e ON pa.emp_id = e.emp_id
GROUP BY
p.project_id, p.project_name, p.start_date, p.end_date,
d.dept_name, p.budget
ORDER BY p.start_date;
混合使用不同類型的連線也是常見的需求，以下是一個綜合範例。
-- 混合連線類型的查詢
-- 查詢所有部門、其員工及專案參與情況
SELECT
d.dept_name,
d.budget AS dept_budget,
COUNT(DISTINCT e.emp_id) AS employee_count,
COUNT(DISTINCT pa.project_id) AS project_count,
COALESCE(SUM(pa.hours_allocated), 0) AS total_project_hours,
ROUND(AVG(e.salary), 2) AS avg_salary
FROM department d
LEFT JOIN employee e ON d.dept_id = e.dept_id -- 包含沒有員工的部門
LEFT JOIN project_assignment pa ON e.emp_id = pa.emp_id -- 包含沒有專案的員工
GROUP BY d.dept_id, d.dept_name, d.budget
ORDER BY project_count DESC, employee_count DESC;
連線效能最佳化
連線查詢的效能對於資料庫應用至關重要。當資料量增加時，未經最佳化的連線查詢可能會變得非常緩慢。以下是一些重要的效能最佳化建議。
首先，確保連線欄位上有適當的索引。索引可以大幅加速連線操作，特別是在大型資料表上。
-- 為連線欄位建立索引
-- 這些索引將加速連線查詢
CREATE INDEX idx_employee_dept ON employee(dept_id);
CREATE INDEX idx_employee_manager ON employee(manager_id);
CREATE INDEX idx_assignment_emp ON project_assignment(emp_id);
CREATE INDEX idx_assignment_project ON project_assignment(project_id);
CREATE INDEX idx_project_dept ON project(dept_id);
-- 複合索引用於經常一起查詢的欄位
CREATE INDEX idx_assignment_emp_project ON project_assignment(emp_id, project_id);
使用 EXPLAIN 來分析查詢的執行計畫，了解資料庫如何處理連線操作。
-- 分析查詢的執行計畫
EXPLAIN SELECT
e.emp_name,
d.dept_name,
p.project_name
FROM employee e
INNER JOIN department d ON e.dept_id = d.dept_id
INNER JOIN project_assignment pa ON e.emp_id = pa.emp_id
INNER JOIN project p ON pa.project_id = p.project_id;
-- 查看詳細的執行計畫
EXPLAIN FORMAT=JSON SELECT ...;
連線順序可能影響效能。一般而言，應該先連結較小的資料表，或者讓查詢最佳化器自動選擇最佳順序。
-- 使用 STRAIGHT_JOIN 強制指定連線順序
-- 只在確定順序更優時使用
SELECT STRAIGHT_JOIN
e.emp_name,
d.dept_name
FROM department d -- 先處理較小的部門表
STRAIGHT_JOIN employee e ON d.dept_id = e.dept_id;
-- 通常讓查詢最佳化器自動選擇順序
-- 除非測試表明手動順序更優
避免在連線條件中使用函式，這會阻止索引的使用。
-- 不好的做法：在連線條件中使用函式
SELECT *
FROM employee e
INNER JOIN department d ON UPPER(e.dept_code) = UPPER(d.dept_code);
-- 好的做法：直接比較欄位值
SELECT *
FROM employee e
INNER JOIN department d ON e.dept_id = d.dept_id;
只選取需要的欄位，避免使用 SELECT *。
-- 不好的做法：選取所有欄位
SELECT *
FROM employee e
INNER JOIN department d ON e.dept_id = d.dept_id
INNER JOIN project_assignment pa ON e.emp_id = pa.emp_id;
-- 好的做法：只選取需要的欄位
SELECT
e.emp_name,
d.dept_name,
pa.role
FROM employee e
INNER JOIN department d ON e.dept_id = d.dept_id
INNER JOIN project_assignment pa ON e.emp_id = pa.emp_id;
實務應用案例
讓我們透過幾個完整的實務案例來整合本文所學的連線技術。
案例一：人事報表
-- 人事報表：部門人員清單與統計
SELECT
d.dept_name,
d.location,
d.budget AS dept_budget,
e.emp_name,
e.hire_date,
e.salary,
TIMESTAMPDIFF(YEAR, e.hire_date, CURDATE()) AS years_of_service,
m.emp_name AS manager_name,
-- 計算部門統計
COUNT(*) OVER (PARTITION BY d.dept_id) AS dept_employee_count,
AVG(e.salary) OVER (PARTITION BY d.dept_id) AS dept_avg_salary,
e.salary - AVG(e.salary) OVER (PARTITION BY d.dept_id) AS salary_diff_from_avg
FROM department d
LEFT JOIN employee e ON d.dept_id = e.dept_id
LEFT JOIN employee m ON e.manager_id = m.emp_id
ORDER BY d.dept_name, e.salary DESC;
案例二：專案資源分析
-- 專案資源分析報表
-- 分析每個專案的人力資源分配情況
SELECT
p.project_name,
p.start_date,
p.end_date,
p.budget,
pd.dept_name AS owning_dept,
-- 團隊組成
COUNT(DISTINCT pa.emp_id) AS team_size,
GROUP_CONCAT(DISTINCT e.emp_name ORDER BY e.emp_name) AS team_members,
GROUP_CONCAT(DISTINCT pa.role ORDER BY pa.role) AS roles,
-- 時間分析
SUM(pa.hours_allocated) AS total_hours,
ROUND(SUM(pa.hours_allocated) / COUNT(DISTINCT pa.emp_id), 1) AS avg_hours_per_person,
-- 成本估算（假設每小時成本為員工薪資/176）
ROUND(SUM(pa.hours_allocated * e.salary / 176), 2) AS estimated_labor_cost,
-- 預算使用率
ROUND(SUM(pa.hours_allocated * e.salary / 176) / p.budget * 100, 2) AS budget_utilization_pct
FROM project p
LEFT JOIN department pd ON p.dept_id = pd.dept_id
LEFT JOIN project_assignment pa ON p.project_id = pa.project_id
LEFT JOIN employee e ON pa.emp_id = e.emp_id
GROUP BY
p.project_id, p.project_name, p.start_date, p.end_date,
p.budget, pd.dept_name
ORDER BY p.start_date;
案例三：跨部門協作分析
-- 跨部門協作分析
-- 找出有多個部門員工參與的專案
SELECT
p.project_name,
COUNT(DISTINCT d.dept_id) AS dept_count,
GROUP_CONCAT(DISTINCT d.dept_name ORDER BY d.dept_name) AS participating_depts,
COUNT(DISTINCT e.emp_id) AS total_participants,
SUM(pa.hours_allocated) AS total_hours
FROM project p
INNER JOIN project_assignment pa ON p.project_id = pa.project_id
INNER JOIN employee e ON pa.emp_id = e.emp_id
INNER JOIN department d ON e.dept_id = d.dept_id
GROUP BY p.project_id, p.project_name
HAVING COUNT(DISTINCT d.dept_id) > 1 -- 只顯示跨部門專案
ORDER BY dept_count DESC, total_participants DESC;
@startuml
!define DISABLE_LINK
!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
title 多表連線資料流程
rectangle "查詢流程" {
card "employee" as emp
card "department" as dept
card "project_assignment" as pa
card "project" as proj
}
rectangle "連線處理" as join
rectangle "結果集" as result {
card "員工資訊" as r1
card "部門資訊" as r2
card "專案資訊" as r3
card "分配資訊" as r4
}
emp --> join : dept_id
dept --> join : dept_id
pa --> join : emp_id, project_id
proj --> join : project_id
join --> r1
join --> r2
join --> r3
join --> r4
note bottom of join
透過外鍵關係
連結多個資料表
end note
@enduml
常見問題與解決方案
在使用連線查詢時，可能會遇到一些常見的問題。以下是這些問題及其解決方案。
問題一：欄位名稱衝突
當連結的資料表有相同名稱的欄位時，必須使用資料表別名或完整名稱來區分。
-- 問題：ambiguous column name
SELECT dept_id, emp_name, dept_name -- dept_id 在兩個表中都有
FROM employee
INNER JOIN department ON employee.dept_id = department.dept_id;
-- 解決方案：使用別名指定來源
SELECT
e.dept_id,
e.emp_name,
d.dept_name
FROM employee e
INNER JOIN department d ON e.dept_id = d.dept_id;
問題二：NULL 值處理
連線操作對 NULL 值的處理可能不如預期，因為 NULL 與任何值比較都不相等。
-- 問題：NULL 值不會匹配
SELECT *
FROM employee e
INNER JOIN department d ON e.dept_id = d.dept_id;
-- 如果 e.dept_id 為 NULL，該記錄不會出現
-- 解決方案：使用 LEFT JOIN 並處理 NULL
SELECT
e.emp_name,
COALESCE(d.dept_name, '未分配部門') AS dept_name
FROM employee e
LEFT JOIN department d ON e.dept_id = d.dept_id;
問題三：笛卡爾積意外
忘記指定連線條件或條件錯誤可能導致笛卡爾積。
-- 問題：缺少連線條件導致笛卡爾積
SELECT e.emp_name, d.dept_name
FROM employee e, department d; -- 這會產生 10 × 5 = 50 筆記錄
-- 解決方案：明確指定連線條件
SELECT e.emp_name, d.dept_name
FROM employee e
INNER JOIN department d ON e.dept_id = d.dept_id;
問題四：重複記錄
多對多關係可能產生重複記錄，需要使用 DISTINCT 或 GROUP BY 處理。
-- 問題：員工參與多個專案導致重複
SELECT e.emp_name, d.dept_name
FROM employee e
INNER JOIN department d ON e.dept_id = d.dept_id
INNER JOIN project_assignment pa ON e.emp_id = pa.emp_id;
-- 解決方案：使用 DISTINCT
SELECT DISTINCT e.emp_name, d.dept_name
FROM employee e
INNER JOIN department d ON e.dept_id = d.dept_id
INNER JOIN project_assignment pa ON e.emp_id = pa.emp_id;
-- 或使用 GROUP BY
SELECT e.emp_name, d.dept_name, COUNT(pa.project_id) AS project_count
FROM employee e
INNER JOIN department d ON e.dept_id = d.dept_id
LEFT JOIN project_assignment pa ON e.emp_id = pa.emp_id
GROUP BY e.emp_id, e.emp_name, d.dept_name;
本文全面介紹了 SQL 表格連線的各種技術，從基本的 INNER JOIN 到進階的自我連線和多表連線。每種連線類型都有其特定的使用場景：INNER JOIN 用於取得兩邊都有匹配的記錄，LEFT JOIN 用於保留左邊資料表的所有記錄，RIGHT JOIN 用於保留右邊資料表的所有記錄，FULL OUTER JOIN 用於保留兩邊的所有記錄，而 CROSS JOIN 用於生成所有可能的組合。
理解並熟練運用這些連線技術，對於資料庫開發者而言是不可或缺的核心能力。在實際應用中，除了選擇正確的連線類型外，還需要注意效能最佳化，包括建立適當的索引、避免不必要的連線、只選取需要的欄位等。透過本文的範例和說明，希望讀者能夠掌握這些技術，並在實務工作中靈活運用，建構出高效且正確的資料庫查詢。