지난번 포스팅에서는 여러개 테이블에서 SELECT 문으로 가져온 결과들의 합집합을 구할 때 UNION 은 중복 확인 및 처리를 하고 UNION ALL 은 중복확인 없이 여러 테이블의 모든 값을 합친다는 차이점을 소개하였습니다. (참고 => https://rfriend.tistory.com/658 )

 

이번 포스팅에서는 PostgreSQL, Greenplum DB에서 SELECT 문의 결과끼리 합치고 빼는 집합 연산자로서 

 

(1) 합집합 UNION

(2) 교집합 INTERSECT 

(3) 차집합 EXCEPT 

(4) 필요조건: 칼럼의 개수가 같아야 하고, 모든 칼럼의 데이터 유형이 동일해야 함. 

 

에 대해서 알아보겠습니다. 

 

벤다이어 그램으로 PostgreSQL, Greenplum DB에서 SELECT 문 결과에 대한 합집합 UNION, 교집합 INTERSECT, 차집합 EXCEPT 했을 때의 결과를 도식화하면 아래와 같습니다. 

 

PostgreSQL, Greenplum DB, UNION, INTERSECT, EXCEPT

 

예제로 사용할 간단한 테이블 두 개를 만들어보겠습니다.  'x1', 'x2' 의 두 개 칼럼이 있고, 두 개 모두 동일하게 'integer' 데이터 유형이어서 테이블 집합연산자인 합집합, 교집합, 차집합의 예제로 사용할 수 있습니다. 

 

-- creating sample tables 

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

 

 

아래의SELECT 문 결과에 대한 UNION, INTERSECT, EXCEPT query 구문은 별도의 추가 설명이 필요 없을 정도로 쉬운 내용이므로 예제 집합연산자의 결과만 제시하는 것으로 설명을 갈음하겠습니다. 

 

 

(1) 합집합 UNION

-- 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) 교집합 INTERSECT

-- INTERSECT
SELECT * FROM sample_1 
INTERSECT 
SELECT * FROM sample_2
ORDER BY x1;

--x1    x2
--4	14
--5	15

 

 

 

(3) 차집합  EXCEPT

 

두 테이블의 차집합 EXCEPT 는 먼저 SELECT 한 결과에서 나중에 SELECT 한 결과 중 중복되는 부분을 제외한 후의 나머지 결과를 반환합니다. 

참고로, Oracle, MySQL DB에서는 SELECT 문 결과에 대한 차집합은 MINUS 함수를 사용해서 구할 수 있습니다. 

 

-- EXCEPT
SELECT * FROM sample_1 
EXCEPT 
SELECT * FROM sample_2 
ORDER BY x1;

--x1    x2
--1	11
--2	12
--3	13

 

 

 

(4) 필요조건: 칼럼의 개수가 같아야 하고, 모든 칼럼의 데이터 유형이 동일해야 함. 

 

UNION, UNION ALL, INTERSECT, EXCEPT 의 집합연산자를 사용하려면 SELECT 문으로 불러온 두 테이블의 결과에서 칼럼의 개수가 서로 같아야 하고 또 모든 칼럼의 데이터 유형(Data Type)이 서로 동일해야만 합니다. 만약 칼럼의 데이터 유형이 서로 다르다면 아래와 같은 에러가 발생합니다. (아래 예에서는 'x2' 칼럼이 하나는 'integer', 또 하나는 'text' 로서 서로 다르기때문에 에러가 발생한 경우임)/ 

 

SQL Error [42804]: ERROR: UNION types integer and text cannot be matched

 

-- 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 
INTERSECT 
SELECT * FROM sample_3;

--SQL Error [42804]: ERROR: UNION types integer and text cannot be matched

 

 

이번 포스팅이 많은 도움이 되었기를 바랍니다. 

행복한 데이터 과학자 되세요!

 

 

반응형
Posted by Rfriend

댓글을 달아 주세요

지난번 포스팅에서는 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 은 중복값 여부를 확인하지 않고 두 테이블의 모든 값을 위+아래로 합친 결과를 반환합니다. 

 

POSTGRESQL, GREENPLUM 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

 

 

이번 포스팅이 많은 도움이 되었기를 바랍니다. 

행복한 데이터 과학자 되세요. :-)

 

반응형
Posted by Rfriend

댓글을 달아 주세요