[PostgreSQL, Greenplum] SELECT 결과의 합집합 UNION, 교집합 INTERSECT, 차집합 EXCEPT

지난번 포스팅에서는 여러개 테이블에서 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

 

 

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

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