집합 연산
집합 연산 (Set Operations)
여러 쿼리의 결과를 하나로 합치고 싶을 때 집합 연산(set operations)을 써요. 집합 연산은 집합 연산 의미론(set operation semantics)에 따라 쿼리를 결합할 수 있게 해줘요. 집합 연산은 UNION [ALL], INTERSECT [ALL], EXCEPT [ALL] 절을 말해요. 기본 변형(vanilla)은 집합 의미론(set semantics)을 따르므로 중복을 제거하고, ALL이 붙은 변형은 다중 집합 의미론(bag semantics)을 사용해요.
전통적인 집합 연산은 쿼리를 **열 위치(column position)**로 결합하며, 결합할 쿼리들이 동일한 수의 입력 열을 가져야 해요. 열의 타입이 같지 않으면 캐스트(cast)가 추가될 수 있어요. 결과는 첫 번째 쿼리의 열 이름을 사용해요.
DuckDB는 또한 열을 위치가 아닌 이름으로 결합하는 UNION [ALL] BY NAME을 지원해요. UNION BY NAME은 입력이 동일한 수의 열을 가질 필요가 없어요. 누락된 열이 있으면 NULL 값이 추가돼요.
UNION
UNION 절은 여러 쿼리의 행을 결합하는 데 사용돼요. 쿼리들은 동일한 수의 열을 반환해야 해요. 필요한 경우 서로 다른 타입의 열을 결합하기 위해 반환된 타입 중 하나로 암시적 캐스팅(implicit casting)이 수행돼요. 이것이 불가능하면 UNION 절은 오류를 발생시켜요.
기본 UNION (집합 의미론)
기본 UNION 절은 집합 의미론을 따르므로 중복 제거를 수행해요. 즉, 결과에 고유한 행만 포함돼요.
SELECT * FROM range(2) t1(x)
UNION
SELECT * FROM range(3) t2(x);
| x |
|---|
| 2 |
| 1 |
| 0 |
UNION ALL (다중 집합 의미론)
UNION ALL은 다중 집합 의미론(bag semantics)에 따라 두 쿼리의 모든 행을 중복 제거 없이 반환해요.
SELECT * FROM range(2) t1(x)
UNION ALL
SELECT * FROM range(3) t2(x);
| x |
|---|
| 0 |
| 1 |
| 0 |
| 1 |
| 2 |
UNION [ALL] BY NAME
UNION [ALL] BY NAME 절은 위치가 아닌 이름으로 서로 다른 테이블의 행을 결합하는 데 사용할 수 있어요. UNION BY NAME은 두 쿼리가 동일한 수의 열을 가질 필요가 없어요. 한 쿼리에서만 찾을 수 있는 열은 다른 쿼리에서는 NULL 값으로 채워져요.
예를 들어 다음 테이블들을 봐요:
CREATE TABLE capitals (city VARCHAR, country VARCHAR);
INSERT INTO capitals VALUES
('Amsterdam', 'NL'),
('Berlin', 'Germany');
CREATE TABLE weather (city VARCHAR, degrees INTEGER, date DATE);
INSERT INTO weather VALUES
('Amsterdam', 10, '2022-10-14'),
('Seattle', 8, '2022-10-12');
SELECT * FROM capitals
UNION BY NAME
SELECT * FROM weather;
| city | country | degrees | date |
|---|---|---|---|
| Seattle | NULL | 8 | 2022-10-12 |
| Amsterdam | NL | NULL | NULL |
| Berlin | Germany | NULL | NULL |
| Amsterdam | NULL | 10 | 2022-10-14 |
UNION BY NAME은 집합 의미론을 따르므로 중복 제거를 수행하는 반면, UNION ALL BY NAME은 다중 집합 의미론을 따라요.
INTERSECT
INTERSECT 절은 두 쿼리 결과 모두에 나타나는 행을 모두 선택하는 데 사용돼요.
기본 INTERSECT (집합 의미론)
기본 INTERSECT는 중복 제거를 수행하므로 고유한 행만 반환돼요.
SELECT * FROM range(2) t1(x)
INTERSECT
SELECT * FROM range(6) t2(x);
| x |
|---|
| 0 |
| 1 |
INTERSECT ALL (다중 집합 의미론)
INTERSECT ALL은 다중 집합 의미론을 따르므로 중복이 반환돼요.
SELECT unnest([5, 5, 6, 6, 6, 6, 7, 8]) AS x
INTERSECT ALL
SELECT unnest([5, 6, 6, 7, 7, 9]);
| x |
|---|
| 5 |
| 6 |
| 6 |
| 7 |
EXCEPT
EXCEPT 절은 왼쪽 쿼리에만 나타나는 모든 행을 선택하는 데 사용돼요.
기본 EXCEPT (집합 의미론)
기본 EXCEPT는 집합 의미론을 따르므로 중복 제거를 수행하고, 고유한 행만 반환돼요.
SELECT * FROM range(5) t1(x)
EXCEPT
SELECT * FROM range(2) t2(x);
| x |
|---|
| 2 |
| 3 |
| 4 |
EXCEPT ALL (다중 집합 의미론)
EXCEPT ALL은 다중 집합 의미론을 사용해요:
SELECT unnest([5, 5, 6, 6, 6, 6, 7, 8]) AS x
EXCEPT ALL
SELECT unnest([5, 6, 6, 7, 7, 9]);
| x |
|---|
| 5 |
| 8 |
| 6 |
| 6 |