萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL 表連接完全圖解:內連接、外連接、交叉連接與實戰範例

表連接是 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

IDName
1張三
2李四
3王五

Score

IDSubjectScore
1國文80
1數學90
2國文75
3英文85

1. 內連接

僅返回**兩表皆符合連接條件**的行。

SELECT s.Name, sc.Subject, sc.Score
FROM Student s
INNER JOIN Score sc ON s.ID = sc.ID;
NameSubjectScore
張三國文80
張三數學90
李四國文75
王五英文85

2. 左外連接

返回**左表所有行**,右表無匹配則為 `NULL`。

SELECT s.Name, sc.Subject, sc.Score
FROM Student s
LEFT JOIN Score sc ON s.ID = sc.ID;
NameSubjectScore
張三國文80
張三數學90
李四國文75
王五NULLNULL

3. 右外連接

返回**右表所有行**,左表無匹配則為 `NULL` (實務較少用,可改寫為左連接)。

SELECT s.Name, sc.Subject, sc.Score
FROM Student s
RIGHT JOIN Score sc ON s.ID = sc.ID;
NameSubjectScore
張三國文80
張三數學90
李四國文75
王五英文85

4. 完整外連接

返回**兩表所有行**,無匹配處雙邊填 `NULL`。

SELECT s.Name, sc.Subject, sc.Score
FROM Student s
FULL JOIN Score sc ON s.ID = sc.ID;
NameSubjectScore
張三國文80
張三數學90
李四國文75
王五英文85
NULLNULLNULL

5. 交叉連接

笛卡兒積:左表每行 × 右表每行,**無 ON 條件**。

SELECT s.Name, sc.Subject, sc.Score
FROM Student s
CROSS JOIN Score sc;

結果 3 × 4 = 12 行。常用於生成測試資料、座標網格、全排列場景。

進階:多表連接順序與效能

  • 優化器會自動重排連接順序,但**舊版外連結語法 (`*=`/`=*`) 會強制順序**且已棄用
  • 大表驅動小表:小表在前 (驅動表)、大表在後 (被驅動表),配合索引查找
  • 必要時用 `OPTION (FORCE ORDER)` 或 `LOOP/HASH/MERGE JOIN` Hint 干預

行轉列 / 列轉行 實戰 (連接的變體應用)

-- 行轉列:學生成績橫向展示
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;

熟練掌握連接類型與轉換技巧,能將複雜業務邏輯轉化為集合運算,充分發揮關聯式引擎的集合處理優勢。

copyright © 萬盛學電腦網 all rights reserved