SQL 조인을 위한 집합 이론

SQL에서 데이터 조인하기

Maham Faisal Khan

Senior Content Developer, DataCamp

벤 다이어그램과 집합 이론

A figure showing three Venn diagrams: UNION, INTERSECT, and EXCEPT. The UNION diagram has two overlapping circles, fully colored in green. The INTERSECT diagrams has two overlapping circles, but only the overlapping part is colored in green. The EXCEPT diagram only has the part of the left circle that has nothing in common with the right circle colored in green.

벤 다이어그램과 집합 이론

An image that shows three Venn diagrams, with two shaded out. Only the diagram with UNION is in focus. The UNION diagram has two overlapping circles, fully colored in green.

UNION 다이어그램

UNION은 두 테이블을 입력으로 받아, 두 테이블의 모든 레코드를 반환함

A diagram showing two tables, left and right. On the right side of the diagram, there is an illustration of what happens when a UNION operation is performed on the two tables.

UNION ALL 다이어그램

UNION ALL은 두 테이블을 받아 두 테이블의 모든 레코드를 반환하며, 중복을 포함함

A diagram showing two tables, left and right. On the right side of the diagram, there is an illustration of what happens when a UNION ALL operation is performed on the two tables.

UNION 및 UNION ALL 구문

UNION 구문

SELECT *
FROM left_table
UNION
SELECT *
FROM right_table;

UNION ALL 구문

SELECT *
FROM left_table
UNION ALL
SELECT *
FROM right_table;

UNION 및 UNION ALL 구문

A diagram showing two tables, left and right. On the right side of the diagram, there is an illustration of what happens when a UNION operation is performed on the two tables. Arrows call out that the left and right table require the same number of columns of the same field types.

UNION 및 UNION ALL 구문

monarchs 테이블 살펴보기

SELECT *
FROM monarchs;

| country        | continent   | monarch                 |
|----------------|-------------|-------------------------|
| Brunei         | Asia        | Hassanal Bolkiah        |
| Oman           | Asia        | Haitham bin Tarik       |
| Norway         | Europe      | Harald V                |
| United Kingdom | Europe      | Charles III             |

총리와 군주를 한 테이블에서

SELECT monarch AS leader, country
FROM monarchs
UNION
SELECT prime_minister, country
FROM prime_ministers
ORDER BY country, leader
LIMIT 10;

UNION 후 결과

|-------------------------|----------------|
| leader                  | country        |
|-------------------------|----------------|
| Anthony Albanese        | Australia      |
| Hassanal Bolkiah        | Brunei         |
| Mostafa Madbouly        | Egypt          |
| Narendra Modi           | India          |
| Christopher Luxon       | New Zealand    |
| Harald V                | Norway         |
| Jonas Gahr Støre        | Norway         |
| Haitham bin Tarik       | Oman           |
| Shehbaz Sharif          | Pakistan       |
| Luís Montenegro         | Portugal       |
|-------------------------|----------------|

지도자들과 UNION ALL

SELECT monarch AS leader, country
FROM monarchs
UNION ALL
SELECT prime_minister, country
FROM prime_ministers
ORDER BY leader, country
LIMIT 10;

UNION ALL 결과

| leader                  | country        |
|-------------------------|----------------|
| Anthony Albanese        | Australia      |
| Hassanal Bolkiah        | Brunei         |
| Hassanal Bolkiah        | Brunei         |
| Mostafa Madbouly        | Egypt          |
| Narendra Modi           | India          |
| Christopher Luxon       | New Zealand    |
| Harald V                | Norway         |
| Jonas Gahr Støre        | Norway         |
| Haitham bin Tarik       | Oman           |
| Haitham bin Tarik       | Oman           |

연습해봅시다!

SQL에서 데이터 조인하기