[MySQL] MySql집합연산 - 합집합

집합 연산의 유래

RDBMS의 창시자인 에드거 커드(Edgar F. Codd)는 관계형 모델을 고안한 인물이다. 관계형 모델을 채택한 데이터베이스를 관계형 데이터 베이스라 부른다.
관계형 모델에서의 관계형은 수학 집합론의 관계형 이론에서 유래했다. 집합론이라고 거창하게 말하지만 실질적으로는 데이터베이스의 데이터를 집합으로 간주해 다루기 쉽게 하자는 것에 지나지 않는다. 뜻을 잘 기억해 두면 이해하기 쉽다.

 

1. SQL과 집합

벤 다이어 그램에서는 하나의 원이 곧 하나의 집합이다.  원 안에는 몇 가지 요소가 포함된다. 한편 데이터베이스에서는 테이블의 행이 요소에 해당한다. 행은 여러 개의 열로 구성되는 경우도 있으므로, 수치 상으로는 복수의 값이 존재한다. 하지만 집합의 요소라는 측면에서 보면 하나의 행이 곧 하나의 요소가 된다.
SELECT 명령을 실행하면 데이터베이스에 질의하며 그 결과 몇 개의 행이 반환된다. 이때 반환된 결과 전체를 하나의 집합이라고 생각하면 된다.

---

합집합(UNION)

 

집합의 연산에는 '합집합'이라는 것이 있다. 이는 집합을 서로 더한 것을 말한다.

ex) A집합 -> {1,2,3,}  B집합 -> {2,10,11}

그중 2라는 요소는 A에도 B에도 모두 존재한다.

A U B = {1,2,3,10,11}

이러한 연산을 mysql에서는 UNION 을 사용한다.

SQL 에서는 SELECT 명령의 실행 결과를 하나의 집합으로 다룰 수 있다. 합집합을 계산할 경우에는 수학에서 사용하던 U대신 UNION 키워드를 사용한다. 

즉, 수학에서의 'A U B'는 SQL에서는 'A UNION B'라고 표현한다. A나 B로 표현했지만 실제로는 SELECT 명령이다.

CREATE TABLE test1(a int);
INSERT INTO test1 VALUES(1);
INSERT INTO test1 VALUES(2);
INSERT INTO test1 VALUES(3);

CREATE TABLE test2(b int);
INSERT INTO test2 VALUES(2);
INSERT INTO test2 VALUES(10);
INSERT INTO test2 VALUES(11);

SELECT * FROM test1
UNION
SELECT * FROM test2;	# 두 개의 명령을 하나의 명령으로 합친다.

결과

한 번의 쿼리 실행으로 두 개의 SELECT 명령이 내부적으로 실행되는 형식이다. 이때 각 SELECT 명령의 실행결과(집합)를 합집합(UNION)으로 계산하여 최종적으로 결과를 반환한다.

 

UNION으로 두 개의 SELECT 명령을 하나로 연계해 질의 결과를 얻을 수 있다!

---

UNION의 조건

 

UNION을 이용하면 여러 개의 SELECT 명령을 하나로 묶을 수 있다. 1 + 2 + 3 ... 처럼 연속해서 더하는 것과 같은 형식이다. 이때 각각의 SELECT 명령의 열의 내용은 서로 일치해야 한다. 예를 들어 test1와 test2의 경우, 열 이름은 서로 다르지만 열 개수와 자료형이 서로 같기 때문에 일치한다고 말할 수 있다. 반면 다음과 같이 완전히 열 구성이 다른 테이블을 UNION으로 묶을 수는 없다.

CREATE TABLE test3(
c INT,
d VARCHAR(10)
);

SELECT * FROM test1
UNION
SELECT * FROM test2
UNION
SELECT * FROM test3;

error가 발생한다.

다만 전체 데이터를 반환하는 애스터리스크(*)를 쓰지 않고, 열을 따로 지정하여 각 SELECT 명령에서 집합의 요소가 될 데이터를 서로 맞춰주면 UNION으로 실행할 수 있는 쿼리가 된다. 예를 들면 다음과 같다.

SELECT a FROM test1
UNION
SELECT b FROM test2
UNION
SELECT c FROM test3;

결과

test3에 INSERT를 해주지 않았으므로 위의 코드와 결과가 같다.

---

UNION의 ORDER BY

SELECT 명령들은 UNION으로 묶을 때 나열 순서는 합집합의 결과에 영향을 주지 않는다. 따라서 다음 명령들은 결과가 모두 같다. 단, 결괏값의 나열 순서는 달라질 수도 있다. ORDR BY를 지정하지 않은 SELECT 명령은 결과가 내부처리의 상황에 따라 바뀌기 때문이다.

SELECT * FROM test1 UNION SELECT * FROM test2; #1번코드
SELECT * FROM test2 UNION SELECT * FROM test1; #2번코드

1번코드

2번코드

- UNION을 사용할 때의 ORDER BY
UNION으로 SELECT 명령을 결합해 합집합을 구하는 경우, 각 SELECT 명령에 ORDER BY를 지정해 정렬할 수는 없다. ORDER BY를 지정할 때는 마지막 SELECT 명령에만 지정하도록 한다.

SELECT a FROM test1 ORDER BY a
UNION
SELECT b FROM test2; # ERROR 첫 번째 SELECT 명령에 ORDER BY를 지정할 수 없다.

ORDER BY로 정렬할 수 없다는 뜻이 아니다. 합집합의 결과를 정렬하므로, 가장 마지막의 SELECT 명령에 ORDER BY를 지정해야 한다는 의미이다.

 

SELECT a FROM test1
UNION
SELECT b FROM test2 ORDER BY b; # ERROR

하지만 이 쿼리에서도 에러가 발생한다. 

ORDER BY를 지정할 수 있다고 해도 마지막의 SELECT 명령의 결과만 정렬하는 것이 아니고 합집합의 결과를 정렬하는 것이기 때문이다. 

이때 두 개의 SELECT 명령에서 열 이름이 서로 일치한다면 문제가 없겠지만 앞의 예제에서처럼 반드시 그렇다는 보장이 없다. 이런 경우 서로 동일하게 별명을 붙여 정렬할 수 있다.

SELECT a AS c FROM test1
UNION
SELECT b AS c FROM test2 ORDER BY c;

결론

UNION으로 SELECT 명령을 연결하는 경우, 가장 마지막 SELECT 명령에 대해서만 ORDER BY 구를 지정할 수 있다! ORDER BY 구에 지정하는 열은 별명을 붙여 이름을 일치시킨다!

---

UNION ALL

SELECT 명령에서 중복을 제거할 때는 SELECT 구에 DISTINCT를 지정. 이때 기본값은 ALL로, 명시적으로 지정하거나 생략할 수도 있다. 

즉, 중복을 제거하는 경우에는 DISTINCT, 중복을 제거하지 않고 모두를 반환하는 경우에는 ALL을 지정했다. 한편 UNION의 경우는 기본 동작이 DISTINCT이고, 모든 결과를 얻고 싶을 때는 ALL을 추가적으로 지정한다. 즉, DISTINCT나 ALL로 중복제거 여부를 지정할 수 있다는 점은 똑같지만, UNION의 기본동작은 ALL이 아닌 DISTINCT라는 점이 다르다. 

두 개의 SELECT 명령에 UNION ALL을 적용해 합집합 구하기

SELECT * FROM test1
UNION ALL
SELECT * FROM test2;

중복되는 2가 두개가 되는 것이 보인다.

UNION ALL은 두 개의 집합을 단순하게 합치는 것이다. UNION에서는 이미 존재하는 값인지를 검사하는 처리가 필요한 만큼, UNION ALL 쪽이 성능적으로는 유리할 경우가 있다. 즉, 중복값이 없는 경우에는 UNION ALL을 사용하는 편이 좋은 성능을 보여준다.