UNION & UNION ALL

SQL Server'a Giriş

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

Sonuçları birleştirme

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

Yinelenen satırlar hariç tutulur

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

Yinelenen satırlar dahil edilir

Sonuçlar için yeni sütun adları oluşturma

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

Özet

UNION veya UNION ALL: Aynı ya da farklı tablolardaki sorguları birleştirir

Farklı tablolardan veri birleştirirken:

Aynı sırada aynı sayıda sütun seçin
Sütun tipleri aynı olmalı

Kaynak tablolarda sütun adları farklıysa

Sütunlara takma ad verin (alias)

UNION: Yinelenenleri atar (daha yavaş)

UNION ALL: Yinelenenleri dahil eder (daha hızlı)

Hadi pratik yapalım!

SQL Server'a Giriş