[PostgreSQL, Greenplum] SELECT 결과의 합집합 UNION, UNION ALL
Greenplum and PostgreSQL Database 2021. 5. 30. 23:21지난번 포스팅에서는 PostgreSQL, Greenplum DB에서 JOIN 문을 사용하여 여러개의 테이블을 Key 값을 기준으로 왼쪽+오른쪽으로 연결하는 다양한 방법(INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL JOIN)을 소개하였습니다. (참고 ==> https://rfriend.tistory.com/657)
이번 포스팅에서는 PostgreSQL, Greenplum DB에서 UNION, UNION ALL 함수를 사용해서 여러개의 테이블을 위+아래로 합치는 방법을 소개하겠습니다. 이전의 JOIN 이 Key값 기준 연결/매칭의 개념이었다면 이번 포스팅의 UNION, UNION ALL은 합집합(union of sets) 의 개념이라고 볼 수 있습니다.
(1) UNION : 중복값 제거 후 테이블을 위+아래로 합치기
(2) UNION ALL : 중복값 제거하지 않은 채 테이블을 위+아래로 합치기
(3) 전제조건: 합치려는 테이블 칼럼의 데이터 유형(data type)이 서로 같아야 함.
UNION 은 두 테이블의 중복값을 제거한 후에 두 테이블을 위+아래로 합친 결과를 반환하는 반면에, UNION ALL 은 중복값 여부를 확인하지 않고 두 테이블의 모든 값을 위+아래로 합친 결과를 반환합니다.
UNION 의 경우 두 테이블의 값을 스캔 해서 두 테이블 간의 중복값 여부를 확인하는 중간 단계가 존재하기 때문에 두 테이블의 각 크기가 매우 큰 경우 UNION ALL 대비 상대적으로 연산 시간이 오래 걸립니다. 만약 위+아래로 합치려는 두 테이블의 값 간에 중복값이 없다거나 혹은 중복값 여부를 확인할 필요 없이 모두 합치기만 필요한 요건의 경우에는 UNION ALL 을 사용하는 것이 속도 면에서 유리합니다.
간단한 예제 테이블들을 만들어서 예를 들어보겠습니다.
-------------------------------------
-- UNION vs. UNION ALL
-------------------------------------
-- Sample Table 1
DROP TABLE IF EXISTS sample_1;
CREATE TABLE sample_1 (x1 int, x2 int)
DISTRIBUTED randomly;
INSERT INTO sample_1 VALUES (1, 11), (2, 12), (3, 13), (4, 14), (5, 15);
SELECT * FROM sample_1 ORDER BY x1;
--x1 x2
--1 11
--2 12
--3 13
--4 14
--5 15
-- Sample Table 2
DROP TABLE IF EXISTS sample_2;
CREATE TABLE sample_2 (x1 int, x2 int)
DISTRIBUTED randomly;
INSERT INTO sample_2 VALUES (4, 14), (5, 15), (6, 16), (7, 17), (8, 18);
SELECT * FROM sample_2 ORDER BY x1;
--x1 x2
--4 14
--5 15
--6 16
--7 17
--8 18
(1) UNION : 중복값 제거 후 테이블을 위+아래로 합치기
SELECT column1, column2, ... FROM table1
UNION
SELECT column1, column2, ... FROM table2
와 같은 형식의 구문으로 두 테이블에서 중복값을 제거한 후에 위+아래로 합칠 수 있습니다.
--------------------
-- (1) UNION
--------------------
SELECT * FROM sample_1
UNION
SELECT * FROM sample_2
ORDER BY x1;
--x1 x2
--1 11
--2 12
--3 13
--4 14
--5 15
--6 16
--7 17
--8 18
(2) UNION ALL : 중복값 제거하지 않은 채 테이블을 위+아래로 합치기
SELECT column1, column2, ... FROM table1
UNION ALL
SELECT column1, column2, ... FROM table2
와 같은 형식의 구문으로 두 테이블에서 중복값을 제거하지 않은 상태에서 (중복값 체크 없음) 위+아래로 합칠 수 있습니다.
-------------------------
-- (2) UNION ALL
-------------------------
SELECT * FROM sample_1
UNION ALL
SELECT * FROM sample_2
ORDER BY x1;
--x1 x2
--1 11
--2 12
--3 13
--4 14
--4 14 -- 중복
--5 15
--5 15 -- 중복
--6 16
--7 17
--8 18
(3) 전제조건: 합치려는 테이블 칼럼의 데이터 유형(data type)이 서로 같아야 함.
만약 UNION, UNION ALL 로 합치려는 두 테이블의 칼럼의 데이터 유형(data type)이 서로 같지 않다면, (아래의 예에서는 sample_1 테이블의 x2 칼럼은 integer, sample_3 테이블의 x2 칼럼은 text로서 서로 다름), "ERROR: UNION types integer and text cannot be matched" 라는 에러 메시지가 발생합니다.
-- (3) The data types of all corresponding columns must be compatible.
-- Sample Table 3
DROP TABLE IF EXISTS sample_3;
CREATE TABLE sample_3 (x1 int, x2 text)
DISTRIBUTED randomly;
INSERT INTO sample_3 VALUES (10, 'a'), (20, 'b'), (30, 'c'), (40, 'd'), (50, 'f');
SELECT * FROM sample_3 ORDER BY x1;
--x1 y
--10 a
--20 b
--30 c
--40 d
--50 f
-- ERROR
SELECT * FROM sample_1
UNION
SELECT * FROM sample_3;
--SQL Error [42804]: ERROR: UNION types integer and text cannot be matched
-- Position: 38
이번 포스팅이 많은 도움이 되었기를 바랍니다.
행복한 데이터 과학자 되세요. :-)