UNION 與 UNION ALL

SQL Server 入門

John MacKintosh

Instructor

SELECT 
  album_id, 
  title, 
  artist_id 
FROM album 
WHERE artist_id IN (1, 3)

+------------+-------------------------+------------|
| album_id   | title                   | artist_id  |
|------------+-------------------------|------------|
| 1          | For Those About To Rock | 1          |
| 4          | Let There Be Rock       | 1          |
| 5          | Big Ones                | 3          |
+------------+----------+---------------------------+

SELECT 
  album_id, 
  title, 
  artist_id 
FROM album 
WHERE artist_id IN (1, 4, 5)

+------------+-------------------------+------------|
| album_id   | title                   | artist_id  |
|------------+-------------------------|------------|
| 1          | For Those About To Rock | 1          |
| 4          | Let There Be Rock       | 1          |
| 6          | Jagged Little Pill      | 4          |
| 7          | Facelift                | 5          |
+------------+-------------------------+------------+

合併結果

SELECT 
  album_id, 
  title, 
  artist_id 
FROM album 
WHERE artist_id IN (1, 3)

UNION

SELECT 
  album_id, 
  title, 
  artist_id 
FROM album 
WHERE artist_id IN (1, 4, 5);

+------------+-------------------------+------------|
| album_id   | title                   | artist_id  |
|------------+-------------------------|------------|
| 1          | For Those About To Rock | 1          |
| 4          | Let There Be Rock       | 1          |
| 5          | Big Ones                | 3          |
| 6          | Jagged Little Pill      | 4          |
| 7          | Facelift                | 5          |
+------------+-------------------------+------------+

會排除重複列

UNION ALL

SELECT 
  album_id, 
  title, 
  artist_id 
FROM album 
WHERE artist_id IN (1, 3) 
UNION ALL 
SELECT 
  album_id, 
  title, 
  artist_id 
FROM album 
WHERE artist_id IN (1, 4, 5);

+------------+-------------------------+------------|
| album_id   | title                   | artist_id  |
|------------+-------------------------|------------|
| 1          | For Those About To Rock | 1          |
| 4          | Let There Be Rock       | 1          |
| 5          | Big Ones                | 3          |
| 1          | For Those About To Rock | 1          |
| 4          | Let There Be Rock       | 1          |
| 6          | Jagged Little Pill      | 4          |
| 7          | Facelift                | 5          |
+------------+-------------------------+------------+

會包含重複列

為最終結果建立新欄位名稱

SELECT 
  album_id AS ALBUM_ID, 
  title AS ALBUM_TITLE, 
  artist_id AS ARTIST_ID 
FROM album 
WHERE artist_id IN(1, 3) 
UNION ALL 
SELECT 
  album_id AS ALBUM_ID, 
  title AS ALBUM_TITLE, 
  artist_id AS ARTIST_ID 
FROM album 
WHERE artist_id IN(1, 4, 5)

+------------+-------------------------+------------|
| ALBUM_ID   | ALBUM_TITLE             | ARTIST_ID  |
|------------+-------------------------|------------|
| 1          | For Those About To Rock | 1          |
| 4          | Let There Be Rock       | 1          |
| 5          | Big Ones                | 3          |
| 1          | For Those About To Rock | 1          |
| 4          | Let There Be Rock       | 1          |
| 6          | Jagged Little Pill      | 4          |
| 7          | Facelift                | 5          |
+------------+-------------------------+------------+

重點整理

UNION 或 UNION ALL：合併同一或不同資料表的查詢

若要合併不同資料表：

以相同順序選取相同數量的欄位
欄位型別需相同

若來源資料表欄位名稱不同

為欄位取別名

UNION：去除重複（執行較慢）

UNION ALL：保留重複（執行較快）

一起來練習吧！

SQL Server 入門