SQL: mysql에서의 FULL OUTER JOIN

 

 

 

 

<서론>

실무에서는 종종 두 개 이상의 테이블의 모든 데이터ㅡ기준키가 공백인 데이터를 포함한, 다시 말해 합집합ㅡ를 불러와야 할 경우가 생긴다. 타 데이터베이스에서는 이를 FULL OUTER JOIN이라는 이름으로 지원하지만, mysql에서는 아쉽게도 이를 공식적으로 지원하고 있지 않다. 그러면 mysql에서는 합집합을 구현할 수 없냐고? 그건 아니다. LEFT JOIN과 RIGHT JOIN을 결합하는 것으로 mysql에서도 이를 간접적으로 구현할 수 있다. 외국 블로그에 해당 내용에 대해 예제와 함께 표시한 포스트가 있어 번역해 가져와 본다.

 

 

FULL OUTER JOIN의 형태. 출처 //&nbsp;https://sql-joins.leopard.in.ua/

 

 

 

 

<본문>

sales와 orders라는 두 테이블이 있다고 가정해 보자. sales와 orders에는 동일하게 id주문 아이디와 order_date(형식이 살짝 다른)주문 일자, amount주문 금액 데이터가 들어 있다.

 

SELECT *
FROM sales;

 +------+---------------------+--------+
 | id   | order_date          | amount |
 +------+---------------------+--------+
 |    1 | 2021-02-02 08:15:00 |    250 |
 |    2 | 2021-02-02 08:30:00 |    200 |
 |    3 | 2021-02-02 08:55:00 |    150 |
 |    4 | 2021-02-02 09:15:00 |    125 |
 |    5 | 2021-02-02 09:30:00 |    250 |
 |    6 | 2021-02-02 09:45:00 |    200 |
 |    7 | 2021-02-02 10:15:00 |    180 |
 |    8 | 2021-02-02 10:30:00 |    125 |
 |    9 | 2021-02-02 10:45:00 |    200 |
 |   10 | 2021-02-02 11:15:00 |    250 |
 |   11 | 2021-02-02 11:30:00 |    150 |
 |   12 | 2021-02-02 11:45:00 |    200 |
 +------+---------------------+--------+


SELECT *
FROM orders;

 +------+------------+--------+
 | id   | order_date | amount |
 +------+------------+--------+
 |    5 | 2021-01-28 |    250 |
 |    6 | 2021-01-29 |    250 |
 |    7 | 2021-01-30 |    250 |
 |    8 | 2021-01-31 |    250 |
 |    9 | 2021-02-01 |    250 |
 +------+------------+--------+

 

 

만약 당신이 전체 기간 동안의 매출 합계를 계산한다거나 하는 이유로 두 테이블을 합쳐야 할 필요성이 있다면 아래와 같이 LEFT JOIN과 RIGHT JOIN을 UNION ALL로 결합해 사용할 수 있다. 아래와 같은 식으로.

mysql> SELECT * FROM sales
       LEFT JOIN orders ON sales.id = orders.id
       UNION ALL
       SELECT * FROM sales
       RIGHT JOIN orders ON sales.id = orders.id
       WHERE sales.id IS NULL ;
       
 +------+---------------------+--------+------+------------+--------+
 | id   | order_date          | amount | id   | order_date | amount |
 +------+---------------------+--------+------+------------+--------+
 |    5 | 2021-02-02 09:30:00 |    250 |    5 | 2021-01-28 |    250 |
 |    6 | 2021-02-02 09:45:00 |    200 |    6 | 2021-01-29 |    250 |
 |    7 | 2021-02-02 10:15:00 |    180 |    7 | 2021-01-30 |    250 |
 |    8 | 2021-02-02 10:30:00 |    125 |    8 | 2021-01-31 |    250 |
 |    9 | 2021-02-02 10:45:00 |    200 |    9 | 2021-02-01 |    250 |
 |    1 | 2021-02-02 08:15:00 |    250 | NULL | NULL       |   NULL |
 |    2 | 2021-02-02 08:30:00 |    200 | NULL | NULL       |   NULL |
 |    3 | 2021-02-02 08:55:00 |    150 | NULL | NULL       |   NULL |
 |    4 | 2021-02-02 09:15:00 |    125 | NULL | NULL       |   NULL |
 |   10 | 2021-02-02 11:15:00 |    250 | NULL | NULL       |   NULL |
 |   11 | 2021-02-02 11:30:00 |    150 | NULL | NULL       |   NULL |
 |   12 | 2021-02-02 11:45:00 |    200 | NULL | NULL       |   NULL |
 +------+---------------------+--------+------+------------+--------+

 

 

다만 UNION ALL의 경우 합집합이기 때문에 중복 행이 섞여 나올 수 있다. 이럴 경우에는 UNION을 사용해 주면 되는데, UNION의 경우에는 t1과 t2 사이의 중복 값을 걸러서 출력하는 것이어서 유니크 값을 거르는 인덱스가 하나 더 추가된다고 한다. 그래서 ㅡ중복을 거르지 않는 UNION ALL과 대비해서ㅡ 데이터의 크기에 따라 1.5배에서 4배까지 성능 차이가 날 수도 있다고.

 

그러니까, 웬만하면 FULL OUTER JOIN을 사용할 때는 UNION ALL을 쓰는 게 효율적이겠다. (+ 최소한으로 SELECT해서.)물론 애초에 모델링 차원에서 결합할 일을 배제하도록 설계하는 게 가장 좋겠지만.

 

 

 

<정리>

마지막으로 문법을 정리해 두고 글을 마친다. 순서대로 UNION ALL중복 미제거, UNION중복 제거으로 FULL OUTER JOIN을 적용하는 쿼리다.

SELECT * FROM t1 
LEFT JOIN t2 ON t1.id = t2.id 
UNION ALL 
SELECT * FROM t1 
RIGHT JOIN t2 ON t1.id = t2.id 
WHERE t1.id IS NULL

 

 

SELECT * FROM t1 
LEFT JOIN t2 ON t1.id = t2.id 
UNION 
SELECT * FROM t1 
RIGHT JOIN t2 ON t1.id = t2.id

 

 

<참고>

ubiq 블로그

sewonzzang님 블로그