表連接是 SQL 查詢的核心,掌握各類連接的語意與結果集差異,是寫出高效正確查詢的前提。本文以兩張示例表 (`Student`、`Score`) 為基礎,透過視覺化圖解與實際執行結果,系統梳理五大連接類型。
CREATE TABLE Student (ID INT PRIMARY KEY, Name NVARCHAR(20));
CREATE TABLE Score (ID INT, Subject NVARCHAR(10), Score INT);
INSERT INTO Student VALUES (1, N'張三'), (2, N'李四'), (3, N'王五');
INSERT INTO Score VALUES
(1, N'國文', 80), (1, N'數學', 90),
(2, N'國文', 75), (3, N'英文', 85);
Student
| ID | Name |
|---|---|
| 1 | 張三 |
| 2 | 李四 |
| 3 | 王五 |
Score
| ID | Subject | Score |
|---|---|---|
| 1 | 國文 | 80 |
| 1 | 數學 | 90 |
| 2 | 國文 | 75 |
| 3 | 英文 | 85 |
僅返回**兩表皆符合連接條件**的行。
SELECT s.Name, sc.Subject, sc.Score
FROM Student s
INNER JOIN Score sc ON s.ID = sc.ID;
| Name | Subject | Score |
|---|---|---|
| 張三 | 國文 | 80 |
| 張三 | 數學 | 90 |
| 李四 | 國文 | 75 |
| 王五 | 英文 | 85 |
返回**左表所有行**,右表無匹配則為 `NULL`。
SELECT s.Name, sc.Subject, sc.Score
FROM Student s
LEFT JOIN Score sc ON s.ID = sc.ID;
| Name | Subject | Score |
|---|---|---|
| 張三 | 國文 | 80 |
| 張三 | 數學 | 90 |
| 李四 | 國文 | 75 |
| 王五 | NULL | NULL |
返回**右表所有行**,左表無匹配則為 `NULL` (實務較少用,可改寫為左連接)。
SELECT s.Name, sc.Subject, sc.Score
FROM Student s
RIGHT JOIN Score sc ON s.ID = sc.ID;
| Name | Subject | Score |
|---|---|---|
| 張三 | 國文 | 80 |
| 張三 | 數學 | 90 |
| 李四 | 國文 | 75 |
| 王五 | 英文 | 85 |
返回**兩表所有行**,無匹配處雙邊填 `NULL`。
SELECT s.Name, sc.Subject, sc.Score
FROM Student s
FULL JOIN Score sc ON s.ID = sc.ID;
| Name | Subject | Score |
|---|---|---|
| 張三 | 國文 | 80 |
| 張三 | 數學 | 90 |
| 李四 | 國文 | 75 |
| 王五 | 英文 | 85 |
| NULL | NULL | NULL |
笛卡兒積:左表每行 × 右表每行,**無 ON 條件**。
SELECT s.Name, sc.Subject, sc.Score
FROM Student s
CROSS JOIN Score sc;
結果 3 × 4 = 12 行。常用於生成測試資料、座標網格、全排列場景。
-- 行轉列:學生成績橫向展示
SELECT Name,
MAX(CASE WHEN Subject = N'國文' THEN Score END) AS 國文,
MAX(CASE WHEN Subject = N'數學' THEN Score END) AS 數學,
MAX(CASE WHEN Subject = N'英文' THEN Score END) AS 英文
FROM Student s LEFT JOIN Score sc ON s.ID = sc.ID
GROUP BY Name;
-- 列轉行:UNPIVOT (2005+)
SELECT Name, Subject, Score
FROM (
SELECT Name, 國文, 數學, 英文 FROM 上述結果
) p
UNPIVOT (Score FOR Subject IN (國文, 數學, 英文)) AS u;
熟練掌握連接類型與轉換技巧,能將複雜業務邏輯轉化為集合運算,充分發揮關聯式引擎的集合處理優勢。