지난 글에서는 데이터를 집계하거나 정렬해서 볼 수 있는 group by, order by와 테이블, 필드에 별칭을 부여할 수 있는 alias에 대해 알아봤습니다.
이번 글에서는 테이블을 합쳐서 (붙여서) 데이터를 조회할 수 있는 Join과 모든 필드가 동일한 테이블을 합치는 Union에 대해 알아보겠습니다.
(지난 글은 여기에서 확인 가능합니다.)
SQL 기초 - Join (left join, inner join), Union
테이블 합치기 Join
테이블은 목적에 맞는 데이터만 모아서 제작하는 것이 좋습니다. 예를 들어, 유저 정보는 user 테이블에 결제 정보는 payment 테이블에 따로 모아두는 것이 관리에도 용이하고 여러 데이터를 복합적으로 확인할 때 편리합니다.
하나의 엑셀 시트에 모든 회사의 정보를 모아두면 관리가 어려운 것처럼 테이블도 동일합니다.
다만, 유저 정보와 결제 정보를 각각 테이블을 번갈아가면서 확인하는 것은 어렵기 때문에 2개의 테이블에 공통된 값이 있다면 이 값을 기준으로 테이블을 합쳐서 하나의 테이블로 만들어 보는 것이 편리합니다.
(엑셀의 vlookup과 같은 기능이라고 이해하면 좋을 것 같습니다.)
Join에는 여러 종류가 있고 확인해야 하는 데이터 종류에 따라서 다양하게 활용할 수 있지만 보통은 left join과 inner join을 많이 사용합니다.
Join의 구조
Join은 테이블과 테이블을 잇는 역할로 아래 형식으로 사용합니다.
select 필드명 from 테이블A
join 테이블B on 테이블A.필드명 = 테이블B.필드명;join이 입력된 부분을 inner join 혹은 left join처럼 변경해 원하는 join의 형태로 사용할 수 있습니다.
'=' 기호를 보고서도 알 수 있듯이 join은 A테이블과 B테이블에 공통된 필드명 (ID)으로 데이터를 묶는다는 것을 알 수 있습니다.
추가로 Join 시에 필드명을 지정해서 조회하는 경우 (select와 from 사이에 필드명을 지정하는 경우) 다른 필드도 잘 Join이 되었는지 확인하려면 테이블.* 로 조회가능합니다.
교집합 Inner join
A테이블과 B테이블의 공통된 데이터만 가져오는 것이 inner join입니다.
정확하게는 A테이블과 B테이블에서 select와 from 사이에 작성한 필드의 값이 존재하는 데이터를 가져오는 것입니다.
예를 들어, 아래와 같은 user테이블과 payment테이블이 있다고 가정하겠습니다.
<user>
| name | created_time |
| 홍길동 | 2023-04-01 |
| 둘리 | 2023-04-02 |
| 손한량 | 2023-04-03 |
<payment>
| name | payment | created_time |
| 홍길동 | 1,000 | 2023-04-01 |
| 홍길동 | 2,000 | 2023-04-01 |
| 둘리 | 2,000 | 2023-04-02 |
| 홍길동 | 3,000 | 2023-04-03 |
위 2개의 테이블에서는 'name' 필드가 공통된 데이터로 ID가 될 수 있을 것입니다.
다만, payment 테이블을 확인하니 '손한량'이라는 user는 보이지 않습니다.
따라서 inner join을 진행하면 '손한량'의 데이터는 볼 수 없습니다. (user에는 있지만 payment에는 없기 때문입니다.)
쿼리는 아래처럼 구성했습니다.
select * from user u
inner join payment p on u.name = p.name;[실행결과]
| name (user) | created_time (user) | name1 (payment) | payment (payment) | created_time2 (payment) |
| 홍길동 | 2023-04-01 | 홍길동 | 1,000 | 2023-04-01 |
| 홍길동 | 2023-04-01 | 홍길동 | 2,000 | 2023-04-01 |
| 홍길동 | 2023-04-01 | 홍길동 | 3,000 | 2023-04-03 |
| 둘리 | 2023-04-02 | 둘리 | 2,000 | 2023-04-02 |
이처럼 user 테이블과 payment 테이블이 name이라는 값으로 묶여서 테이블이 합쳐진 것을 확인할 수 있습니다.
inner join이기 때문에 user테이블과 payment테이블에 공통으로 없는 값인 '손한량'의 값은 표시되지 않은 것을 볼 수 있습니다.
여집합 Left join
Join하는 모든 테이블에 값이 표시되는 inner join에 대해 알아봤습니다.
이제는 A테이블에만 값이 있고 B테이블에는 값이 없는 경우에도 데이터를 모두 불러오는 left join에 대해 알아보겠습니다.
수학시간에 배운 집합이랑은 조금 다른 개념인 것이 A여집합이라면 A에만 속한 값이 나와야 하지만 SQL에서는 A에 속한 값이라는 개념보다는 B의 값이 비어있어도 모두 표시한다는 개념으로 이해하면 좋습니다.
inner join에서 사용했던 동일한 user와 payment 테이블을 사용하겠습니다.
<user>
| name | created_time |
| 홍길동 | 2023-04-01 |
| 둘리 | 2023-04-02 |
| 손한량 | 2023-04-03 |
<payment>
| name | payment | created_time |
| 홍길동 | 1,000 | 2023-04-01 |
| 홍길동 | 2,000 | 2023-04-01 |
| 둘리 | 2,000 | 2023-04-02 |
| 홍길동 | 3,000 | 2023-04-03 |
이번에는 left join으로 진행해보겠습니다.
select * from user u
left join payment p on u.name = p.name;[실행결과]
| name (user) | created_time (user) | name1 (payment) | payment (payment) | created_time2 (payment) |
| 홍길동 | 2023-04-01 | 홍길동 | 1,000 | 2023-04-01 |
| 홍길동 | 2023-04-01 | 홍길동 | 2,000 | 2023-04-01 |
| 홍길동 | 2023-04-01 | 홍길동 | 3,000 | 2023-04-03 |
| 둘리 | 2023-04-02 | 둘리 | 2,000 | 2023-04-02 |
| 손한량 | 2023-04-03 |
이렇게 user 테이블에만 값이 있고 payment 테이블에는 값이 없는 '손한량' 데이터도 테이블에 포함되어 표시됩니다.
그렇다면 왜 left join을 쓰는지 궁금할 수 있습니다.
이렇게 left join한 테이블을 기준으로 where 조건을 걸어 payment 값이 없는 name만 찾았다고 가정하겠습니다.
select * from user u a
left join payment p on u.name = p.name
where p.name is null;[실행결과]
| name (user) | created_time (user) | name1 (payment) | payment (payment) | created_time2 (payment) |
| 손한량 | 2023-04-03 |
이러한 형식으로 payment 기록이 없는 user는 결제 기록이 없는 유저이기 때문에 이 명단을 가지고 결제를 유도하는 캠페인을 하는 등 다양한 활동을 할 수 있습니다.
left join 사용 시 주의사항
다만, left join은 공통된 값만 조회하는 inner join과는 다르게 테이블 1과 테이블 2 위치가 중요합니다.
지금의 경우 user 테이블이 테이블 1의 자리에 있기 때문에 손한량 데이터가 표시되었지만 만약 테이블의 위치가 바뀐다면 다른 결과가 표시됩니다.
select * from payment p
left join user u on p.name = u.name;
[실행결과]
| name (payment) | payment (payment) | created_time (payment) | name1 (user) | created_time2 (user) |
| 홍길동 | 1,000 | 2023-04-01 | 홍길동 | 2023-04-01 |
| 홍길동 | 2,000 | 2023-04-01 | 홍길동 | 2023-04-01 |
| 둘리 | 2,000 | 2023-04-02 | 둘리 | 2023-04-02 |
| 홍길동 | 3,000 | 2023-04-03 | 홍길동 | 2023-04-01 |
이렇게 payment 테이블이 A가 되니 user 테이블의 '손한량' 데이터는 표시되지 않았습니다.
payment테이블의 존재하는 값을 기준으로 실행되기 때문입니다.
이처럼 left join은 사용 시 테이블 순서에 주의해야 합니다.
모든 필드가 동일하다면 Union
간혹 결제 기록을 매월 별도의 테이블로 기록하고 있다면 1월 결제 테이블, 2월 결제 테이블처럼 테이블이 월마다 나뉘어 있을 것입니다.
다만, 이 때, 최근 3개월 결제 금액의 총합을 보고 싶다면 join만으로는 해결이 되지 않을 때가 있습니다.
그렇다고하더라도 1월 결제 테이블과 2월 결제 테이블은 필드의 구조는 동일할 것이기 때문에 union을 사용해 2개의 테이블을 합쳐서 확인할 수 있습니다.
union의 구조는 다음과 같습니다.
select 필드명 from 테이블명_1
union all
select 필드명 from 테이블명_2;예를 들어, 아래와 같은 1월 결제 테이블과 2월 결제 테이블이 있다고 가정하겠습니다.
<payment_1>
| name | payment | created_time |
| 홍길동 | 1,000 | 2023-01-01 |
| 홍길동 | 2,000 | 2023-01-01 |
| 둘리 | 2,000 | 2023-01-02 |
| 홍길동 | 3,000 | 2023-01-03 |
<payment_2>
| name | payment | created_time |
| 손한량 | 1,000 | 2023-02-01 |
| 홍길동 | 2,000 | 2023-02-01 |
| 둘리 | 2,000 | 2023-02-02 |
| 홍길동 | 3,000 | 2023-02-03 |
이 1월과 2월의 매출 총합을 알기 위해서는 2개의 테이블을 합쳐서 확인해야 합니다.
select * from payment_1
union all
select * from payment_2;[실행결과]
| name | payment | created_time |
| 홍길동 | 1,000 | 2023-01-01 |
| 홍길동 | 2,000 | 2023-01-01 |
| 둘리 | 2,000 | 2023-01-02 |
| 홍길동 | 3,000 | 2023-01-03 |
| 손한량 | 1,000 | 2023-02-01 |
| 홍길동 | 2,000 | 2023-02-01 |
| 둘리 | 2,000 | 2023-02-02 |
| 홍길동 | 3,000 | 2023-02-03 |
이렇게 필드가 동일하다면 union을 사용해서 테이블을 합치는 것도 가능합니다.
union 사용 시 주의사항
다만, union의 경우 order by를 사용한 정렬 기능이 동작하지 않으니 참고해주세요.
이번에는 다양한 테이블을 하나의 테이블로 만들어서 데이터를 확인할 수 있는 Join과 Union에 대해 알아봤습니다.
다음에는 쿼리 안에 쿼리를 추가로 사용해 데이터를 여러 방면으로 확인할 수 있는 Subquery에 대해 알아보겠습니다.
감사합니다.
손한량
최근댓글