SQL 집합 연산자(UNION, UNION ALL, INTERSECT, EXCEPT)

집합연산자에는 우선 UNION, UNION ALL, INTERSECT, EXCEPT 이렇게 종류가 있습니다. MySQL에서는 INTERSECT 와 EXCEPT가 따로 존재하지 않고 JOIN으로 해결할 수 있는데 이것도 이번에 같이 살펴보겠습니다.

💡 UNION/ UNION ALL

조회한 다수의 SELECT 문을 합치고 싶을때 바로 UNION을 사용할 수 있습니다.
조건은 컬럼의 수가 같아야 하고, 각 컬럼의 타입이 같아야합니다.

간단한 예시

-- UNION
select ~~~ from ~~~ --첫번째 select문
UNION 
select ~~~ from ~~~ --두번째 selec문

-- UNION ALL
select ~~~ from ~~~ --첫번째 select문
UNION ALL
select ~~~ from ~~~ --두번째 selec문

• UNION 의 경우 그림에 보이는 것처럼 중복을 제거한 모습으로 합쳐서 조회가 됩니다. 만약에 중복이 있다면 UNION을 기준으로 위에서 만든 SELECT문의 값으로 출력이됩니다!
• 반대로 UNION ALL의 경우 중복이 제거되지 않은채 모든값 합쳐서 출력하게 됩니다.

💡 INTERSECT

INTERSECT의 경우 교집합을 출력합니다. 한가지 예시를 들어보겠습니다. 만약에 그림에서 첫번째 원이 A라는 드라마이고 두번째 원이 B라는 드라마 라고 해보겠습니다.

A드라마에는 김우빈, 마동석, 지창욱, 혜리 가 등장하고
B드라마에는 김소현, 지창욱, 공유, 전소민 이 등장합니다.
C드라마
D드라마
.
.
.

문제에서 A드라마에도 출연했으면서, B드라마에 출연한 사람의 이름과, 성, 연령, 키 를 출력하시오.
라는 문제가 나왔을때 아래와 같이 구성해 볼 수 있습니다.

select first_name, last_name, age, height from actor
where actor.name = 'A'
INTERSECT
select first_name, last_name, age, height from actor
where actor.name = 'B'

(위의 코드는 이해를 돕기위한 코드입니다.)

위와 같이 코드를 작성함으로서 A드라마에 출연한 배우와 B드라마에 출연한 배우의 교집합에 있는 배우인 '지창욱'이 출력됩니다.

MySQL 에서는 INTERSECT가 존재하지 않아서 이를 JOIN으로 해결할 수 있습니다.

(레퍼런스 : https://yahwang.github.io/posts/52)

SELECT t1.col1, t1.col2
FROM table1 t1 INNER JOIN table2 t2 
	ON t1.col1 = t2.col1 AND t1.col2 = t2.col2

💡 EXCEPT

EXCEPT의 경우는 그림에 보이는것 처럼 차집합이라고 생각할 수 있습니다.
드라마의 예시를 다시 가지고 오겠습니다.

그림에서 첫번째 원이 A라는 드라마이고 두번째 원이 B라는 드라마 라고 해보겠습니다.

A드라마에는 김우빈, 마동석, 지창욱, 혜리 가 등장하고
B드라마에는 김소현, 지창욱, 공유, 전소민 이 등장합니다.
C드라마
D드라마
.
.
.

문제에서는 다음과 같이 제시될 수 있습니다. A드라마에 등장한 배우이며, B에는 등장하지 않은 배우의 이름과, 성, 연령, 키 를 출력하시오.

select DISTINCT(first_name), DISTINCT(last_name), age, height from actor
where actor.name = 'A'
EXCEPT
select DISTINCT(first_name), DISTINCT(last_name), age, height from actor
where actor.name = 'B'

이렇게 코드를 작성할 수 있고 출력은 "김우빈, 마동석, 혜리" 이렇게 출력이 될것입니다. 교집합 부분인 "지창욱"이 빠진것을 알 수 있습니다.

이부분에서 주의할점이 있습니다!! 바로 DISTINCT입니다. EXCEPT(MINUS)절에서는 출력할때 DISTINCT를 반드시 넣어주셔야 합니다.

MySQL 에서는 EXCEPT도 마찬가지로 존재하지 않습니다. 따라서 이또한 JOIN으로 해결할 수 있습니다.

예제 쿼리문이 아래와 같다고 했을때 MySQL은 세가지 방법으로 할 수 있습니다.
(레퍼런스 : http://intomysql.blogspot.com/2011/01/mysql-minus-intersect.html)

SELECT member_id as uid, member_name as uname FROM member
EXCEPT
SELECT emp_id as uid, emp_name as uname FROM emp;

• NOT IN 을 이용하는 방법

SELECT DISTINCT m.member_id as uid, m.member_name as uname
FROM member m
WHERE (m.member_id, m.member_name) NOT IN
  (SELECT e.emp_id, e.emp_name FROM emp e);
  

• NOT EXISTS 을 이용하는 방법

SELECT DISTINCT m.member_id as uid, m.member_name as uname
FROM member m
WHERE NOT EXISTS (
  SELECT 1
  FROM emp e
  WHERE e.emp_id=m.member_id
    AND e.emp_name=m.member_name
);

• LEFT (OUTER) JOIN 을 이용하는 방법

SELECT DISTINCT m.member_id as uid, m.member_name as uname
FROM member m
  LEFT JOIN emp e ON e.emp_id=m.member_id
    AND e.emp_name=m.member_name
WHERE e.emp_id IS NULL;	

세가지 방법중 성능으로 봤을때 LEFT JOIN -> NOT EXISTS -> NOT IN 이므로 가능하면 성능이 좋은것으로 쓰면 좋다고 한다.