UNION & UNION ALL

Introductie tot 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          |
+------------+-------------------------+------------+

Resultaten combineren

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          |
+------------+-------------------------+------------+

Dubbele rijen worden uitgesloten

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          |
+------------+-------------------------+------------+

Bevat dubbele rijen

Nieuwe kolomnamen voor het eindresultaat maken

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          |
+------------+-------------------------+------------+

Samenvatting

UNION of UNION ALL: combineert resultaten van dezelfde of verschillende tabellen

Als je data uit verschillende tabellen combineert:

Kies hetzelfde aantal kolommen in dezelfde volgorde
Kolommen moeten hetzelfde datatype hebben

Als brontabellen andere kolomnamen hebben

Gebruik aliassen voor kolomnamen

UNION: verwijdert duplicaten (trager)

UNION ALL: behoudt duplicaten (sneller)

Laten we oefenen!

Introductie tot SQL Server