UNION & UNION ALL

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

Kombinera resultat

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

Dubbletter utesluts

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

Inkluderar dubbletter

Skapa nya kolumnnamn för slutresultatet

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

Sammanfattning

UNION eller UNION ALL: Kombinerar frågor från samma eller olika tabeller

Vid kombination av data från olika tabeller:

Välj samma antal kolumner i samma ordning
Kolumnerna ska ha samma datatyper

Om källtabellerna har olika kolumnnamn

Använd alias för kolumnnamnen

UNION: Tar bort dubbletter (långsammare)

UNION ALL: Behåller dubbletter (snabbare)

Nu kör vi en övning!

Introduktion till SQL Server