[빅쿼리 #10] 테이블 합치기 (UNION ALL, INNER JOIN, OUTER JOIN)

- Today's Topic

• 테이블을 세로로 결합하기 (UNION ALL구문 vs. UNION구문)
• 테이블을 가로로 결합하기 (조인을 활용)

- Used Function

• UNION ALL 구문
• INNER JOIN, OUTER JOIN

---

데이터를 처리하거나, 탐색, 분석을 하다 보면 테이블을 합쳐야 하는 경우가 많이 생깁니다.

하나의 테이블로는 디테일한 정보를 뽑아내기 어렵기 때문에, 다른 테이블을 이용해 결합시켜 추가 정보를 얻게 되는데, 

이 때 활용되는 구문이 바로 UNION ALL 그리고 JOIN개념입니다.

 

1.  UNION ALL 구문

2021년의 구매 테이블과 2022년의 구매 테이블을 같이 보고 싶다면 어떻게 하면 될지 생각해 봅시다.

INSERT구문을 사용해서 2021년 구매 테이블에다가 2022년 구매 테이블을 붙이면 될까요?

이렇게 한다면 엄청 시간이 오래 걸릴 것입니다. 아마 몇날 며칠을 고생해야할 수도 있습니다.

 

하지만 이러한 시간을 획기적으로 줄여줄 수 있는 방법이 있는데, 그것이 바로 UNION ALL구문입니다.

UNION ALL은 여러 개의 테이블을 세로로 결합할 경우 사용되는 구문입니다.

쉽게 말해, 하나의 테이블에 행을 이어붙인다고 생각하시면 됩니다.

앞서 말씀드린 바와 같이, 2021년 구매 테이블 밑에 2022년 구매 테이블을 붙여서 같이 보고 싶다면 이 UNION ALL구문이 적합하다고 볼 수 있겠습니다.

 

1-1) 사용할 함수 및 설명

-- UNION ALL -- : 두 테이블을 한 테이블로 합칠 때, 행을 기준으로 결합합니다.

SELECT 칼럼1, 칼럼2, 칼럼3...['추가할 칼럼에 들어갈 값1' AS 추가할 칼럼이름] FROM 테이블1
UNION ALL
SELECT 칼럼1, 칼럼2, 칼럼3...['추가할 칼럼에 들어갈 값2' AS 추가할 칼럼이름] FROM 테이블2

 

UNION ALL구문은 칼럼 명을 적어주되, 새로운 칼럼을 만들고 싶을 경우, 값과 칼럼명을 지정해 줍니다.

새로운 칼럼을 만드는 것은 선택사항입니다.

만일, 하나의 테이블에만 존재하는 칼럼일 경우, 'NULL AS 하나의 테이블에만 존재하는 칼럼명'이라고

적어주면 한 테이블에는 있지만, 한 테이블에는 없는 칼럼도 같이 합칠 수 있습니다.

물론 데이터 값은 없기 때문에 NULL값이 뜹니다.

 

추가로, UNION 구문만을 사용할 수도 있습니다.

UNION구문과 UNION ALL구문의 차이점은 바로 데이터의 중복을 허용하느냐 입니다.

UNION구문을 사용하게 되면, UNION ALL을 사용할 경우와 달리 데이터의 중복을 제외하고 결과가 출력되므로  행의 수를 줄여줄 수 있습니다.

다만 중복 제거라는 절차가 추가되는 것이므로 성능을 더 사용하기 때문에 특별한 상황이 아니라면 잘 사용되지는 않습니다.

또한, 구매 로그 데이터와 같이 중복되는 행도 의미가 있는 경우라면 UNION ALL을 사용하는게 더 적합하겠습니다.

 

이제 본격적으로 예제 테이블 실습을 해 보겠습니다.

 

1-2) 테이블 정보

CREATE TABLE inlaid-lane-373607.sqlr202301.p2021 (
  user_id string(255),
  purchase_date date,
  product_id string(255),
  price integer
);

insert into inlaid-lane-373607.sqlr202301.p2021 values 
  ('A001', '2021-04-01','ICE01', 2000),
  ('A002', '2021-07-07','ICE02', 3000),
  ('B001', '2021-09-09','BLACK03', 5000)
;

SELECT * FROM  inlaid-lane-373607.sqlr202301.p2021;

CREATE TABLE inlaid-lane-373607.sqlr202301.p2022 (
  user_id string(255),
  purchase_date date,
  product_id string(255),
  price integer
);

insert into inlaid-lane-373607.sqlr202301.p2022 values 
  ('A003', '2022-02-07', 'BLACK02', 7000),
  ('B001', '2022-05-22', 'ICE01', 2000),
  ('B002', '2022-08-14', 'BLACK04', 9000)
;

SELECT * FROM  inlaid-lane-373607.sqlr202301.p2022;

P2021 테이블 (2021년 구매 테이블)

P2022 테이블 (2022년 구매 테이블)

 

현재 이 두 테이블은 따로 되어 있기 때문에 한 번에 분석하기가 어렵습니다.

물론 지금은 3행으로 되어 있어 간단하겠지만, 데이터가 1000개 혹은 1만개가 넘어간다면 한계가 있습니다.

그렇기에 다음 쿼리를 통해 두 테이블을 합쳐 하나의 테이블로 만들어 보겠습니다.

 

 

1-3) 예제 테이블을 활용한 쿼리와 결과

SELECT user_id, purchase_date, product_id, price, 2021 as year 
from inlaid-lane-373607.sqlr202301.p2021
union all
select user_id, purchase_date, product_id, price, 2022 as year 
from inlaid-lane-373607.sqlr202301.p2022
order by purchase_date;

 

이렇게 두 개의 테이블이 하나의 테이블로 만들어진 것을 볼 수 있습니다.

이 테이블을 활용한다면 이전에 각각 존재했던 테이블보다 더욱 의미 있는 정보로 분석할 수 있을 것 같습니다.

 

---

2.  INNER JOIN & OUTER JOIN 

앞서 세로로 행을 결합할 경우에는 UNION구문을 활용하면 되었지만

이제는 가로로 칼럼(열)을 결합하고자 할 경우에는 어떻게 하면 될까요?

바로 JOIN을 활용합니다.

주로 두 테이블을 결합 시 사용되는 JOIN에는 크게 INNER JOIN과 OUTER JOIN 이렇게 두 가지의 종류가 있습니다.

또한, 세부적으로 들어가면 OUTER JOIN의 경우 LEFT/RIGHT OUTER JOIN으로 나뉩니다. 아래 설명을 참고해 주세요.

 

2-1) JOIN의 종류

-- JOIN의 개념 --

1) INNER JOIN : 
- A테이블과 B테이블을 결합했을 때, 서로 겹치는 데이터만 출력됩니다. (교집합의 개념)
- 즉, 조건에 부합하는 행만 출력되게 합니다.

2) (FULL) OUTER JOIN :
- A테이블과 B테이블을 결합했을 때, 겹치지 않는 데이터도 출력됩니다. (합집합의 개념)
- LEFT OUTER JOIN과 RIGHT OUTER JOIN을 합친 개념입니다.
- 즉, 조건에 부합하지 않더라도 출력이 됩니다.

3) LEFT (OUTER) JOIN: 
- A테이블과 B테이블을 결합했을 때, A테이블(왼쪽 테이블)을 기준으로 출력됩니다.
- A테이블에 속한 데이터는 전체가 출력되고
  B테이블에 속한 데이터는 A테이블과 겹치는 데이터만 출력됩니다.
- LEFT JOIN로 적어도 됩니다.

4) RIGHT (OUTER) JOIN
- A테이블과 B테이블을 결합했을 때, B테이블(오른쪽 테이블)을 기준으로 출력됩니다.
- B테이블에 속한 데이터는 전체가 출력되고
  A테이블에 속한 데이터는 B테이블과 겹치는 데이터만 출력됩니다.
 -RIGHT JOIN으로 적어도 됩니다.

 

조인의 종류는 생각보다 많습니다.

물론 상기했던 INNER JOIN 및 OUTER JOIN 만 있는 것이 아니라 

자신의 테이블을 2개로 만들어서 결합하는 SELF JOIN(자체조인),

두 테이블의 모든 행을 곱하여 결과를 반환하는 CROSS JOIN(교차 조인, 곱연산), 

조인 대상 테이블의 칼럼값이 정확하게 일치할 경우 사용하는 EQUI JOIN(동등 조인),

공통적인 속성을 이용해 동일한 칼럼을 갖는 데이터가 조회되는 NATURAL JOIN(자연 조인) 등등

많은 조인이 있습니다.

 

하지만 이번에는 INNER JOIN과 OUTER JOIN에 대해서만 다루어 보겠습니다.

예제 테이블을 활용해 직접 실습해 보겠습니다.

 

 

2-2) INNER JOIN

다음과 같은 테이블이 있다고 보겠습니다.

 

category 테이블

movie테이블

 

그리고 해당 테이블을 다음 쿼리문을 이용하여 inner join해 보겠습니다.

select c.category_id, c.category_name, m.movie_name, m.audience
from inlaid-lane-373607.sqlr202301.category as c
inner join
inlaid-lane-373607.sqlr202301.movie as m
on c.category_id = m.category_id;

 

SELECT 절에 표시할 칼럼을 테이블 별명.칼럼명으로 적어줍니다.

먼저 FROM 절에는 결합하는 테이블 중 왼쪽에 놓을 테이블을 먼저 적어주고 AS구문을 사용하여 별칭을 부여합니다.

또한 어떤 조인을 사용할 것인지 적어주고 (INNER JOIN 등)

다음으로 결합하는 테이블 중 오른쪽에 놓을 테이블을 적어주고 AS구문을 사용하여 별칭을 부여합니다.

그리고 ON구문을 사용해 각 테이블에서 어떤 칼럼을 기준으로 결합할 것인지 지정해 줍니다. (값이 중복되어야 합니다.)

 

해당 쿼리문에 대한 결과는 다음과 같습니다.

 

위 쿼리문에서 c테이블의 카테고리 id를 보여달라고 했음에도 불구하고 카테고리 id가 1인 데이터가 보이지 않습니다.

그 이유는 바로 inner join의 특성 때문입니다. inner join은 서로 중복되는 데이터에 한해 리턴해주므로 카테고리 테이블과 영화 테이블에 값이 겹치는 데이터만 출력되는 것입니다.

 

카테고리ID가 1이라는 것도 모두 보여주고 싶을 경우에는 outer join을 사용합니다.

 

 

2-3) OUTER JOIN 

역시 INNER JOIN과 동일한 테이블이 있다고 보겠습니다.

OUTER JOIN은 LEFT OUTER JOIN과 RIGHT OUTER JOIN, FULL OUTER JOIN으로 나뉜다고 말씀드린 바가 있습니다.

 

category 테이블

movie테이블

 

쿼리 작성 방식은 비슷하기 때문에 결과 테이블 중심으로 보겠습니다.

 

2-3-1) LEFT (OUTER) JOIN

select c.category_id, c.category_name, m.movie_name, m.audience
from inlaid-lane-373607.sqlr202301.category as c
LEFT join
inlaid-lane-373607.sqlr202301.movie as m
on c.category_id = m.category_id;

 

categoty 테이블을 기준으로 정렬되므로 movie테이블에는 category id가 1에 해당하는 데이터가 없지만 출력된 모습을 볼 수 있습니다.

 

 

2-3-2) RIGHT (OUTER) JOIN

select c.category_id, c.category_name, m.movie_name, m.audience
from inlaid-lane-373607.sqlr202301.category as c
RIGHT join
inlaid-lane-373607.sqlr202301.movie as m
on c.category_id = m.category_id;

 

right join은 반대로 오른쪽 테이블을 기준으로 결합하는 것입니다.

이 경우에는 결과가 오른쪽 테이블에 있는 데이터를 기준으로 출력되었습니다.

그래서 카테고리 id가 1인 데이터는 보이지 않는 모습입니다.

 

만일 위의 left join과 같은 결과를 얻고자 한다면, 다음 쿼리처럼 from절에 들어가는 테이블 순서와 on절 다음의 기준을 바꾸어 주어야 합니다.

 

select c.category_id, c.category_name, m.movie_name, m.audience
from inlaid-lane-373607.sqlr202301.movie as m
RIGHT join
inlaid-lane-373607.sqlr202301.category as c
on m.category_id = c.category_id;

 

2-3-3) FULL (OUTER) JOIN

FULL OUTER JOIN은 LEFT JOIN + RIGHT JOIN과 같으므로

양쪽에 모든 데이터를 출력해 줍니다.

현재는 데이터가 3행밖에 없어서 그렇게 큰 상관이 없지만

크기가 큰 데이터를 사용할 경우이고 빈 데이터가 많다면 NULL이 꽤 많이 보일 수도 있습니다.

한쪽에 NULL이 있더라도, 칼럼을 있는 대로 결합하는 것이기 때문입니다.

 

select c.category_id, c.category_name, m.movie_name, m.audience
from inlaid-lane-373607.sqlr202301.movie as m
RIGHT join
inlaid-lane-373607.sqlr202301.category as c
on m.category_id = c.category_id;

 

 

이것으로 테이블 합치기에 대한 포스팅을 마무리하겠습니다.

 

감사합니다.

 

---