프로그래밍 일기 — SQL로 조인(Join)해!

14 min readAug 4, 2023

서로 다른 곳에 있는 데이터를 엮기

#SQL, #RDBMS

데이터끼리 서로 관계를 정의할 수 있을까? 오늘 SQL 학습은 이 질문에 대해 답할 수 있을 것 같다.

Join

SQL에서 Join 이라는 키워드를 활용하면 서로 다른 데이터의 관계를 지을 수 있다. 예를 들어 특정한 조건을 만족하는 유저 10명을 추첨해서 기프티콘을 지급하는 이벤트를 열었다고 가정한다. 이벤트 당첨자 추첨을 위해서, 이름과 연락처 등의 정보를 알아야한다. 문제는 이 정보가 user_id 가 있는 테이블이 아닌 다른 곳에 위치해 있다는 것이다. 그렇다면 이 상황에서, 서로 다른 데이터가 존재하는 각각의 테이블에서 필요한 데이터를 어떻게 불러올 수 있을까? 동시에 이들을 서로 어떻게 연관지을 수 있을까?

먼저 테이블을 확인한다. 회원정보가 필요한 테이블은 아무래도 users 일 것이다. 그리고 user_id 가 존재하는 테이블은 checkins 테이블이라고하자. 두 테이블을 잘 보면 둘 다 user_id 가 존재한다. 이 둘을 연관지을 수 있을 것이다.

먼저 user_id 의 값을 하나 복사해서 가져온다. 다음으로 where 를 활용해 해당 필드의 값을 지정해준다.

select * from users where user_id = "4b8a10e6";

이번에도 같은 방법으로 checkins 테이블에서 해당 값을 갖는 데이터를 가져온다.

select * from checkins where user_id = "4b8a10e6";

자 이것이 무슨 의미일까? 결국 checkins 테이블과 users 테이블에 존재하는 필드 user_id 는 공통된 값을 한 개 이상 갖는, 같은 성질의 필드라는 의미다. 그렇다면 이는 우리가 두 테이블을 이 필드를 기준으로 연관지을 수 있다는 의미가된다. 이때 join 키워드를 활용하게된다.

주의할 것은, 한 테이블에 모든 정보를 담을 수도 있지만, 테이블이 너무 커질 수 있다는 단점이 있다. 그래서 데이터를 범주화하여 각 범주에 따라 다른 테이블에 담아놓고 연결이 필요할 때 연결할 수 있도록 만드는 경우가 많다. 위에서 확인 한 users 와 checkins 테이블에 동시에 존재하는 위 user_id 가 좋은 예제이다. 이렇게 두 테이블을 연결해 주는 필드를 열쇠라는 의미의 key 로 부른다.

Join 활용

Join은 두 테이블의 공통된 키(key)값을 기준으로 테이블을 연결하여 한 테이블로 만들기 위해 활용한다. 예를 들어서 우리는 user_id 필드를 기준으로 users 와 checkins 테이블을 연결하여 한 테이블로 볼 수 있다.

사실 checkins 테이블에 존재하면서, 다른 테이블에도 존재하는 키들은 user_id 이외에도 더 많다. 데이터베이스를 확인해보면 알겠지만, 아래 3가지 필드가 모두 키가될 수 있다.

checkin_id, course_id, user_id는 모두 다른 테이블에도 존재하는 키들이다.

만약 두 테이블 사이에 존재하는 키값이 두 개 이상이라면 이때는 어떤 키값을 기준으로 연결해야할지 컴퓨터가 자동으로 알 수 없다. 따라서 어떤 키값을 기준으로 연결할지를 분명하게 명기해 주어야한다.

예를 들어 아래 쿼리를 보자. 아래 쿼리는 point_users 라는 테이블에서 users 라는 테이블과 join 을 하는데, 그 기준은 user_id 필드로 수행한다는 의미다.

select * from point_users
left join users
on point_users.user_id = users.user_id

벤다이어그램으로 Join이해하기

혹시라도 엑셀을 써 본 사람은 알겠지만 vlookup 이라는 명령어가 SQL의 join 과 같은 기능을 한다는 것을 이해할 수 있다. join 에는 left join 및 inner join 이 있을 수 있다. 벤다이어그램을 상상하면쉽다. 두 집합사이의 관계를 정의하는 것과 같기 때문이다.

Left Join(2)

왼쪽으로만 join한다. 즉, 아래와 같은 그림이다. 다른 말로 Left Outer Join이라고도한다. A와 B 테이블 사이에 공통된 값들이 있다고할 때, 그 둘 사이에있는 교집합을 포함한 A테이블에 있는 모든 데이터만을 고려하는 것을 뜻한다.

위를 쿼리로 보면 아래와 같다. users 라는 테이블을 기준으로 point_users 라는 테이블 사이에 공통된 값을 user_id 키를 기준으로 left join하겠다는 것이다.

select * from users u
left join point_users p
on u.user_id = p.user_id;

결과를 출력해보면 이상한 점이 있다. 특정 필드의 값이 NULL로 표기된 것이 보이기 때문이다. 이는 왜일까?

만약 email 처럼 데이터가 채워져있는 경우, 이는 user_id 값이 point_users 에존재하기 때문이다. 반대로 비어있는 point_user_id 같은 경우, 해당 데이터의 user_id 값이 point_users 테이블에 존재하지 않기 때문이다. 이때는 비어있는 데이터가 회원이지만 수강을 등록/시작하지 않아 포인트가 없는 경우이다. 이렇듯 서로 데이터가 없는 필드에 대해서는 상황에 따라 또 데이터의 특성에 따라 데이터를 다루는 사람이 해석할 줄 알아야할 필요가있다. 하지만 이는 프로그래밍 밖의 영역이므로 길게 다루지 않는다. 그냥 지금은 특정한 데이터를 해석하는데 있어 도메인 지식이 필요하다. 정도로만 이해하면 될 것 같다. 이는 실제 현업에서 경험을 해야 늘 수 있는 부분이기 때문이다.

차있는 데이터와 비어있는 데이터를 비교해보자. 쿼리를 작성하여 비교해보면 조금 더 직관적으로 알 수 있을 것이다.

// 비어있는 데이터, 두 테이블내 해당 필드에 출력값이 없거나 한 곳에만 있는 경우
select * from point_users where user_id = "3b3eac9f";
select * from users where user_id = "3b3eac9f";
// 꽉찬 데이터, 두 테이블내 해당 필드에 출력값이 모두 있는 경우
select * from point_users where user_id = "d90e7626";
select * from users where user_id = "d90e7626";

Inner Join(2)

Inner Join은 두 테이블의 교집합만 참조하는 경우다. 즉, 두 테이블의 공통된 키값만 참조한다고 이해하면 쉽다.

예를 들어 아래 쿼리만 보자. point_users 테이블과 users 테이블의 교집합만 참조하는 것이다. 기준이되는 키값은 user_id 다.

select * from users u
inner join point_users p
on u.user_id = p.user_id

두 테이블을 조인할 때 NULL로 빈값이 없다면, 두 테이블 사이에 해당 필드값이 없는 부분은 없다는 의미다. 이를 확인하기 위해서는 각 테이블에서 해당 필드의 데이터 값 수가 같은지 확인하면 된다.

select count(*) from users u
inner join point_users p
on u.user_id = p.user_id;

이어서 두 테이블의 사이즈를 확인하자.

select count(*) from users; >> 498
select count(*) from point_users; >> 271

두 테이블 중 point_users 의 데이터 수가 inner join 으로 찾은 데이터의 총계수와 같은 것을 알 수 있다. 이 말은 users 데이터의 모든 데이터 중 point_users 테이블과 공통된 부분은 모두 가져왔다는 의미다. 결국 빈 데이터가 없는 이유는 pointer_users 의 모든 user_id 필드의 값을 users 필드도 가지고 있기 때문이다.

또 하나 확인하는 방법은 left join 을 했을 때의 데이터 수와 inner join 을 했을 때 데이터 수를 비교하는 것이다.

select count(*) from point_users
left join users
on point_users.user_id = users.user_id;

left join과 inner join의 수가 같으므로, 기준으로 삼은 키값들은 최소한 두 테이블에서 모두 꽉찬 데이터를 가지고 있다는 의미다.

심화 연습

Join을 가지고 계속해서 연습해본다. 두 테이블을 연결할 때는 일단 테이블을 보고 공통된 부분이 있는지 확인한다. 그 후에 공통된 키 중 하나를 기준으로 잡아 두 테이블을 연결하면 된다.

예를 들어 users 와 checkins 테이블을 보니 두 테이블 다 user_id 키를 가지고 있다. 이 부분을 기준으로 교집합(inner join)을 찾는다.

select * from checkins ch 
inner join users u on ch.user_id = u.user_id;

left join시 테이블 A/B가 나뉘어지는 기준은 from 뒤에 오는 테이블이 무엇인가에 따라 다르다. 즉, from 뒤에 오는 테이블이 기준점 A가 되는 것이다. 그 후 테이블 B를 left join 뒤에 입력하면된다.

Join 실행 순서

위 쿼리가 실행되는 순서를 알아보자. from + 테이블 명령을 일단 먼저 처리하고 join 을 통해 교집합인 데이터를 찾는다. 이후 이 조건에 맞는 모든 데이터를 select 하는 것이다.

from [테이블 이름] : 테이블 데이터 전체 가져오기
inner join [키 이름] on [테이블 이름].[키 이름] = [테이블 2 이름].[키 이름] : 두 테이블 사이의 공통된 키를 기준으로 교집합 찾기
select * : 모든 데이터를 출력한다.

조금 더 심화 연습을 위해 아래 쿼리를 확인하자. 아래 쿼리가 실행되는 순서는 어떨까?

select u.name, count(u.name) from orders o 
inner join users u 
on o.user_id = u.user_id 
where u.email like '%naver.com'
group by u.name;

from orders o : 테이블 전체 데이터 읽어오기

2. inner join users u on o.user_id = u.user_id : 키값을 기준으로 교집합을 찾는다.

3. where u.email like ‘%naver.com' : users 테이블 email 필드값이 naver.com 으로 끝나는 값만 가져온다.

4. group by u.name : users 테이블의 name 값이 같은 값들을 묶기

5. select u.name, count(u.name) : users 테이블의 name 필드를 기준으로 묶인 데이터의 범주와 개수를 출력

Round(반올림)

만약 평균을 계산했는데 소수점 수가 나온다면, 상황에 따라 의미가 없을 수 있다. 이 경우 반올림을 위해 round 를 활용할 수 있다.

select o.payment_method, round(avg(pu.point)) 
from point_users pu
inner join orders o 
on pu.user_id = o.user_id group by o.payment_method;

만약 세 개의 테이블을 join한다면 어떨까? 아래와 같은 코드를 볼 수 있다.

select c.title, ch.week, count(*) from courses c 
inner join checkins ch on c.course_id = ch.course_id 
inner join orders o on ch.course_id = o.course_id 
where o.created_at >= "2020-08-01" group by c.title, ch.week
order by c.title, ch.week;

값이 없는 경우는 어떨까? 값이 없는 경우에는 is Null 을 활용할 수 있다.

select name, count(*) from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is NULL
group by name

또한 select 이후 특정 필드가 아닌 값을 입력하여 새로운 값과 필드를 생성할 수 있다. 아래 쿼리는 select ‘7월' as month 라는 키워드를 통해 "7월” 이라는 값을 month 라는 새로운 필드를 만들어 이 것에 지정하고, 실제로는 where o.created_at < '2020-08-01' 조건에 맞춰 필터링한 데이터를 c2.course_id 와 c2.week 를 기준으로 범주화하고 정렬한 것이다.

select '7월' as month, c.title, c2.week, count(*) as cnt from checkins c2
inner join courses c on c2.course_id = c.course_id
inner join orders o on o.user_id = c2.user_id
where o.created_at < '2020-08-01'
group by c2.course_id, c2.week
order by c2.course_id, c2.week

Union

Select를 두 번 하는게 번거롭다고 느껴지면 이를 한번에 할 수 있는 방법이 없는지 고민할 수도 있다. 참조하고자하는 값을 가진 두 필드의 이름이 같아야한다.

아래는 위 코드를 비슷하게 짠 하나의 코드와 union all 을 통해 병합한 것이다. "7월" 의 데이터와 "8월" 의 데이터를 합친 것이라고 보면된다.

(
select '7월' as month, c.title, c2.week, count(*) as cnt from checkins c2
inner join courses c on c2.course_id = c.course_id
inner join orders o on o.user_id = c2.user_id
where o.created_at < '2020-08-01'
group by c2.course_id, c2.week
order by c2.course_id, c2.week
)
union all
(
select '8월' as month, c.title, c2.week, count(*) as cnt from checkins c2
inner join courses c on c2.course_id = c.course_id
inner join orders o on o.user_id = c2.user_id
where o.created_at > '2020-08-01'
group by c2.course_id, c2.week
order by c2.course_id, c2.week
)

참조:

(1) https://pixabay.com/photos/forest-trees-sunlight-the-path-7179046/

(2) https://learnsql.com/blog/sql-joins/