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 입문