合併資料表
SQL Server 入門
John MacKintosh
Instructor
關聯式資料庫
Primary Keys（主鍵）
- 主鍵：唯一識別資料表中的每一列
+-----------+-------------------+
| artist_id | name |
|-----------+-------------------|
| 1 | AC/DC |
| 2 | Accept |
| 3 | Aerosmith |
| 4 | Alanis Morissette |
| 5 | Alice In Chains |
+-----------+-------------------+
- 主鍵：
artist_id
+----------+-------------------------+-----------+
| album_id | title | artist_id |
|----------+-------------------------+-----------|
| 1 | For Those About To Rock | 1 |
| 2 | Balls to the Wall | 2 |
| 3 | Restless and Wild | 2 |
| 4 | Let There Be Rock | 1 |
| 5 | Big Ones | 3 |
+----------+-------------------------+-----------+
- 主鍵：
album_id - 那
artist_id呢？
外鍵
artist資料表
+-----------+-------------------+
| artist_id | name |
|-----------+-------------------|
| 1 | AC/DC |
| 2 | Accept |
| 3 | Aerosmith |
| 4 | Alanis Morissette |
| 5 | Alice In Chains |
+-----------+-------------------+
album資料表
+----------+-------------------------+-----------+
| album_id | title | artist_id |
|----------+-------------------------+-----------|
| 1 | For Those About To Rock | 1 |
| 2 | Balls to the Wall | 2 |
| 3 | Restless and Wild | 2 |
| 4 | Let There Be Rock | 1 |
| 5 | Big Ones | 3 |
+----------+-------------------------+-----------+
artist_id：指向artist的外鍵
連接 album 與 artist
artist資料表
+-----------+-------------------+
| artist_id | name |
|-----------+-------------------|
| 1 | AC/DC |
| 2 | Accept |
| 3 | Aerosmith |
| 4 | Alanis Morissette |
| 5 | Alice In Chains |
+-----------+-------------------+
- AC/DC 的
artist_id= 1
album資料表
+----------+-------------------------+-----------+
| album_id | title | artist_id |
|----------+-------------------------+-----------|
| 1 | For Those About To Rock | 1 |
| 2 | Balls to the Wall | 2 |
| 3 | Restless and Wild | 2 |
| 4 | Let There Be Rock | 1 |
| 5 | Big Ones | 3 |
+----------+-------------------------+-----------+
- 第 1 與第 4 列的
artist_id= 1
連接 album 與 artist
+----------+-------------------------+-----------+-------------+
| album_id | title | artist_id | artist_name |
|----------+---------------------------------------+-----------|
| 1 | For Those About To Rock | 1 | AC/DC |
| 4 | Let There Be Rock | 1 | AC/DC |
+----------+-------------------------+-----------+-------------|
- 從
album表回傳專輯細節 - 從
artist表回傳對應的藝人細節 - 以
artist_id欄位連接
INNER JOIN（內部連接）
SELECT
album_id,
title,
album.artist_id,
name AS artist_name
FROM album
INNER JOIN artist ON artist.artist_id = album.artist_id
WHERE album.artist_id = 1;
+----------+-------------------------+-----------+-------------+
| album_id | title | artist_id | artist_name |
|----------+---------------------------------------+-----------|
| 1 | For Those About To Rock | 1 | AC/DC |
| 4 | Let There Be Rock | 1 | AC/DC |
+----------+-------------------------+-----------+-------------|
INNER JOIN 語法
SELECT
table_A.columnX,
table_A.columnY,
table_B.columnZ
FROM table_A
INNER JOIN table_B ON table_A.foreign_key = table_B.primary_key;
SELECT
album_id,
title,
album.artist_id,
name AS artist_name
FROM album
INNER JOIN artist on artist.artist_id = album.artist_id;
+----------+---------------------------------------+-----------+
| album_id | title | artist_id | artist_name |
|----------+---------------------------------------+-----------|
| 1 | For Those About To Rock | 1 | AC/DC |
| 4 | Let There Be Rock | 1 | AC/DC |
| 2 | Balls To The Wall | 2 | Accept |
| 3 | Restless and Wild | 2 | Accept |
+----------+---------------------------------------+-----------+
- 回傳
album與artist之間所有配對的所有組合
多重 INNER JOIN
SELECT
table_A.columnX,
table_A.columnY,
table_B.columnZ, table_C columnW
FROM table_A
INNER JOIN table_B ON table_B.foreign_key = table_A.primary_key
INNER JOIN table_C ON table_C.foreign_key = table_B.primary_key;
一起來練習吧！
SQL Server 入門
