9-4.테이블 조합

1.UNION

하나의 엔터티를 저장하는 테이블이라도 성능상의 이유로 일정한 조건에 따라 테이블을 나누어 놓기도 한다. 예를 들어 매출 정보를 몇 년치 모으면 너무 거대해지는데다 작년, 재작년 데이터를 굳이 같이 둘 필요가 없다. 이럴 때는 년도별로 또는 월별로 테이블을 분할하여 관리한다.

여러 가지 목적으로 정보를 분할하여 저장하다 보니 필요할 때 흩어진 정보를 합치거나 여러 테이블의 정보를 조합하여 볼 필요도 있다. 집합 연산자는 여러 테이블의 정보를 조합하여 하나의 결과셋을 만든다. 수학 시간에 배우는 집합과 개념상 같아 이해하기 쉽다.

UNION 연산자는 복수 개의 결과셋에 대한 합집합을 생성한다. 합집합은 이쪽에 있는 정보와 저쪽에 있는 정보를 합쳐 한 덩어리로 보여주는 것이다. 다음 쿼리문은 식품 유형의 상품과 가전 유형의 상품을 각각 조사하여 하나로 합친다.

SELECT * FROM tItem WHERE category = '식품'

UNION

SELECT * FROM tItem WHERE category = '가전';

각 쿼리를 따로 실행해 보면 식품에 대추, 사과 2개의 상품이 있고 가전에 노트북과 마우스 2개의 상품이 있다. UNION은 이 두 결과를 합쳐서 보여준다.

만약 두 집합에 공통적으로 존재하는 레코드가 있다면 하나로 합친다. 집합의 정의상 같은 원소를 두 개 이상 가질 필요가 없어 중복 원소는 제거한다. 이에 비해 UNION ALL 연산자는 중복을 제거하지 않고 있는 그대로 모두 보여 준다. 다음 쿼리로 테스트해 보자.

SELECT DISTINCT depart FROM tStaff WHERE salary > 400

UNION ALL

SELECT DISTINCT depart FROM tStaff WHERE score > 80;

월급 400 초과의 직원이 있는 부서는 총무부와 인사과이다. 성취도 80 초과의 직원이 있는 부서는 총무부와 영업부이다. 이 둘을 UNION ALL로 합치면 총무부가 두 번 반복되어 결과셋은 4개이다. 반면 UNION으로 합치면 중복된 총무부를 제거하고 세 부서만 나타난다.

합집합의 정의상으로는 UNION이 맞지만 중복 원소까지 같이 보고 싶을 때는 UNION ALL 연산자를 사용한다. UNION ALL이 더 큰 결과셋을 만들지만 중복 제거 과정이 필요 없어 속도는 더 빠르다.

간단한 샘플 테이블로 UNION 연산자의 동작을 연구해 봤다. 그런데 이런 조합이 필요하다면 굳이 결과셋을 합칠 필요 없이 조건을 합치는 편이 더 간단하다. 식품 유형의 제품과 가전 유형의 제품을 같이 보고 싶으면 다음 쿼리문이 정답이다.

SELECT * FROM tItem WHERE category = '식품' OR category = '가전';

그러나 UNION 연산자는 다른 테이블로부터 읽은 결과셋도 합칠 수 있다는 점에서 OR 연산자와는 질적으로 다르다. OR 연산자는 같은 테이블 내에서 조건을 합치는 것이고 UNION은 완전히 다른 쿼리로부터 생성된 결과셋까지도 합칠 수 있다.

하나로 합칠 결과셋은 필드의 개수와 타입이 일치하거나 적어도 호환되어야 한다. 구조가 비슷해야 합집합을 만들 수 있다. 다음 쿼리문은 회원과 상품의 합집합을 구하는데 이 둘은 논리적으로 합칠 수 있는 대상이 아니다. 당연히 에러 처리된다.

SELECT * FROM tMember UNION SELECT * FROM tItem;

다른 테이블끼리도 필드 타입만 맞추면 UNION으로 합집합을 만들 수 있으며 UNION을 여러 번 사용해서 세 개 이상의 결과셋에 대한 합집합도 만들 수 있다. 다음은 회원의 이름, 직원의 이름, 종업원의 이름을 모두 합해 본 것이다.

SELECT member FROM tMember

UNION

SELECT name FROM tStaff

UNION

SELECT name FROM tEmployee;

각 테이블의 필드 이름은 다르지만 모두 논리적 의미가 같고 문자열 형으로 타입이 같아 합집합을 만들 수 있다. 세 테이블에 등장하는 26명의 인물을 나열한다. 샘플 테이블이 빈약해 실용적인 예를 보이기 어려운데 실무에서는 UNION을 종종 사용한다. 사용예 몇 가지를 들어 보자.

① 관리 목적상 테이블을 일정한 기준에 따라 나누어 놓은 경우 물리적으로 테이블을 합칠 필요 없이 UNION으로 잠시 합집합을 만들어 볼 수 있다. 예를 들어 1월매출 UNION 2월매출 쿼리문으로 두 달의 매출을 한꺼번에 살펴볼 수 있다.

② UNION은 다른 DB는 물론이고 다른 서버의 테이블끼리도 합집합을 만들 수 있다. 전국에 판매망을 갖춘 업체는 지점별로 매출 테이블을 관리한다. 물리적으로는 분리되어 있지만 테이블 구조는 동일하다. 이 상태에서 각 지점의 매출을 합쳐서 보고 싶으면 합집합을 만든다.

SELECT * FROM 부산대리점 UNION SELECT * FROM 서울대리점;

이 명령을 내리면 부산과 서울의 판매량이 한 테이블처럼 출력된다. 물론 서버는 모두 네트워크로 연결되어 있어야 하며 원격 접속을 해야 하므로 속도는 다소 느릴 것이다.

③ 웹사이트의 게시판은 강좌 게시판, 질문 게시판, 팁 게시판 등 주제별로 테이블을 따로 관리한다. 이때 모든 테이블의 게시물을 통합 검색하거나 전체적인 통계를 낸다고 해 보자. 개별 테이블에 일일이 쿼리를 날리는 것보다 합친 후 한 번에 쿼리를 날리는 것이 간편하고 빠르다.

테이블을 분리하여 관리하는 경우가 많아 각각의 테이블을 다시 합치는 UNION 명령을 쓸 일이 심심치 않게 많다. 같은 테이블에 대해서도 결과셋을 따로 구해 합치면 큰 문제를 작게 나누어 처리할 수 있어 효율적이다.

2.INTERSECT

UNION 연산자가 합집합을 구하는데 비해 INTERSECT 연산자는 교집합을 구한다. 교집합은 두 결과셋에 모두 포함된 레코드만으로 집합을 구성하며 중복 레코드는 한 번만 포함한다. 다음 쿼리는 영업부 직원 집합과 여직원 집합의 교집합을 구한다.

SELECT name FROM tStaff WHERE depart = '영업부'

INTERSECT

SELECT name FROM tStaff WHERE gender = '여';

같은 테이블내의 교집합이라 별 재미도 없고 이 경우는 사실 AND 연산자로 조건을 합치는 것이 더 효율적이다. AND 연산자에 비해 INTERSECT 연산자는 다른 테이블끼리도 교집합을 구할 수 있다는 면에서 수준이 더 높다. 다음 쿼리는 직원 목록에 포함된 사람과 쇼핑몰에 가입한 회원의 교집합을 구한다.

SELECT name FROM tStaff

INTERSECT

SELECT member FROM tMember;

우리 샘플 테이블에는 이런 예가 없어 아무 결과셋도 나타나지 않지만 양쪽에 모두 있는 사람이 있다면 교집합으로 조사될 것이다. 두 집합에 동시에 가입된 사람이 있는지 조사해 보고 싶을 때는 교집합을 뽑아 보면 된다.

3.MINUS

MINUS 연산자는 차집합을 구한다. 차집합은 앞쪽 집합의 원소에서 뒤쪽 집합의 원소를 제외한 집합이다. 중복행이 있으면 하나만 포함한다. 다음 쿼리문은 영업부에서 여직원을 뺀 집합을 구한다.

SELECT name FROM tStaff WHERE depart = '영업부'

MINUS

SELECT name FROM tStaff WHERE gender = '여';

영업부에는 총 7명의 직원이 있지만 여직원인 유관순과 신사임당 두 명을 제외한 남자직원 다섯 명만 출력한다. 교집합이나 합집합과는 달리 차집합은 교환 법칙이 성립하지 않아 결과셋의 순서에 따라 집합이 달라진다. 다음 쿼리는 반대로 여직원 집합에서 영업부를 뺀다.

SELECT name FROM tStaff WHERE gender = '여'

MINUS

SELECT name FROM tStaff WHERE depart = '영업부';

여섯명의 여직원중 영업부 소속 두 명이 빠지고 네 명만 출력한다. MINUS 연산자는 두 테이블의 모든 필드를 일일이 비교하기 때문에 특정 조건만 비교하는 서브쿼리나 조인에 비해 속도가 느리다. 그래서 실무에서는 차집합보다는 다른 논리적인 방법을 더 많이 사용한다. 그러나 개발 중에 테이블의 변화를 관찰할 때는 차집합이 유용하다. 실습을 위해 tStaff 테이블의 사본을 떠 보자.

오라클, MariaDB : CREATE TABLE tStaff2 AS SELECT * FROM tStaff;

SQL Server : SELECT * INTO tStaff2 FROM tStaff;

사본을 뜬 직후에는 두 테이블의 내용이 똑같지만 현실의 데이터는 지속적으로 바뀐다. 월급이 오를 수도 있고 부서가 바뀔 수도 있으며 퇴사하거나 새로 입사하는 직원도 있다. 다음 쿼리를 실행하여 이런 상황을 만들어 보자.

UPDATE tStaff2 SET salary = 500 WHERE name = '안창호';

UPDATE tStaff2 SET depart = '인사과' WHERE name = '성삼문';

DELETE FROM tStaff2 WHERE name = '홍길동';

INSERT INTO tStaff2 VALUES ('어우동', '총무부', '여', '20220401', '신입', 450, 0);

시점이 다른 두 테이블의 어디가 어떻게 바뀌었는지 조사해 보고 싶을 때 육안으로 찾아내는 것은 쉽지 않으며 정확하지도 않다. 이럴 때 차집합을 구해 보면 신속, 정확하게 파악할 수 있다. tStaff2에서 tStaff에 있는 레코드를 빼면 조금이라도 달라진 레코드를 모두 출력한다.

SELECT * FROM tStaff2

MINUS

SELECT * FROM tStaff;

퇴사한 직원의 정보는 tStaff2에 없어 나타나지 않는다. 이 정보는 차집합을 반대로 구해야 알 수 있다. 양방향으로 차집합을 구해 보면 변경된 모든 레코드를 정확히 찾을 수 있다.

호환성 체크

SQL Server, MariaDB : 차집합을 구할 때 EXCEPT 연산자를 사용한다. 키워드만 다를 뿐 오라클의 MINUS 연산자와 동작이나 사용 방법은 같다.