엑셀보다 쉬운 SQL - 3주차

Join: 여러 정보를 한 눈에 보고 싶다면

• '오늘의 다짐'을 남긴 회원의 이름을 알고싶어요

오늘의 미션!

'오늘의 다짐' 이벤트 당첨자를 선정하여 스타벅스 기프티콘을 지급해야 해요.
우선, 배운 내용을 사용해서 '오늘의 다짐' 테이블을 불러와 볼까요?

*오늘의 다짐 이벤트: 오늘의 다짐을 남겨준 10명 추첨해서 기프티콘 지급하는 이벤트.

• 그런데 문제가 생겼어요!

'오늘의 다짐' 이벤트 당첨자를 추첨하기 위해서는, 이름과 연락처 등의 정보를 알아야 하는데 여기에는 user_id라는 정보만 있어요.

뭔가 user_id라는 정보에 힌트가 담겨있을 것 같은데... 어떻게 하면 좋을까요?

• 우선, 회원 정보가 필요하니 users 테이블을 한번 살펴볼까요?

• 마찬가지로 select 쿼리문을 사용해서 잘 불러오셨죠?

👉 오! 뭔가, 똑같은 이름의 필드를 발견했어요. users 테이블의 user_id 필드와, checkins 테이블의 user_id 필드의 이름이 같아요. 이걸 잘 연결시키면 될 것 같지 않나요?

• 오늘의 꿀팁

한 테이블에 모든 정보를 담을 수도 있겠지만, 불필요하게 테이블의 크기가 커져 불편해집니다.
그래서, 데이터를 종류별로 쪼개 다른 테이블에 담아놓고 연결이 필요한 경우 연결할 수 있도록 만들어놓습니다.

예를 들면, users와 checkins 테이블에 동시에 존재하는 user_id 처럼요.
이런 필드를 두 테이블을 연결시켜주는 열쇠라는 의미로 'key'라고 부릅니다.

3) Join 이란?

Join이란?
두 테이블의 공통된 정보 (key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것을 의미해요.

예) user_id 필드를 기준으로 users 테이블과 orders 테이블을 연결해서 한 눈에 보고 싶어요!

위의 예시와 같이, 두 테이블의 정보를 연결해서 함께 보고싶을 때가 있겠죠?

그럴 때를 대비해서 무언가 연결된 정보가 있을 때, user_id 처럼 동일한 이름과 정보가 담긴 필드를 두 테이블에 똑같이 담아놓는답니다. 이런 필드를 두 테이블을 연결시켜주는 열쇠라는 의미로 'key'라고 불러요.

• 직접 데이터로 살펴볼까요?

눈치채신 분도 계시겠지만, 빨간색으로 표시해놓은 필드가 모두 key값에 해당하는 필드입니다.

• [오늘의 꿀팁!]

병원에서 의사선생님이 '환자번호 101번님 진료받으러 들어오세요' 라고 불렀는데 같은 환자번호를 가진 사람이 여러명이 있으면 누가 들어와야 할지 환자번호만으로 알 수 없겠죠?

SQL에서의 Join도 마찬가지에요.
key값을 사용해 연결하고 싶은 테이블에 찾아가서 똑같은 값을 가지는 key를 찾게 되는데, 똑같은 key를 가지는 데이터가 여러개 있으면 어느 데이터를 가져와서 연결해야 할지 알 수 없어요.

• Join을 사용해서 Key값으로 두 테이블 연결해보기

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

Join의 종류: Left Join, Inner Join

• Left Join: 유저 데이터로 Left Join 이해해보기

여기서 A와 B는 각각의 테이블을 의미합니다. 둘 사이의 겹치는 부분은, 뭔가 테이블 A와 B의 key 값이 연결되는 부분일 것 같지 않나요?

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

어떤 데이터는 모든 필드가 채워져있지만, 어떤 데이터는 비어있는 필드가 있습니다.

꽉찬 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재해서 연결한 경우
비어있는 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재하지 않는 경우

• 비어있는 데이터의 경우, 회원이지만 수강을 등록/시작하지 않아 포인트를 획득하지 않은 회원인 경우에요!

꽉찬 데이터, 비어있는 데이터에서 하나씩 user_id를 뽑아서 orders 테이블에서 조회해볼까요?

꽉찬 데이터의 user_id값 예시: d90e7626
비어있는 데이터의 user_id값 예시: 3b3eac9f

위의 user_id값으로 조회하면 데이터가 정상적으로 한 개의 데이터가 출력되지만, 아래의 user_id값으로 조회하면 데이터가 출력되지 않는 것을 알 수 있습니다!

• Inner Join: 유저 데이터로 Inner Join 이해해보기

여기서 A와 B는 각각의 테이블을 의미합니다. 이 그림은 뭔가, 두 테이블의 교집합을 이야기하고 있는 것 같지 않나요?

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

앗, 여기서는 비어있는 필드가 있는 데이터가 없어요!

그 이유는, 같은 user_id를 두 테이블에서 모두 가지고 있는 데이터만 출력했기 때문이에요.
Left Join을 했을 때 빈 필드가 없는 데이터의 갯수와, Inner Join을 했을때의 전체 데이터의 갯수가 같은지 확인해보면 되겠죠?

SQL 쿼리가 실행되는 순서

select * from enrolleds e
inner join courses c
on e.course_id = c.course_id;

위 쿼리가 실행되는 순서: from → join → select

1. from enrolleds: enrolleds 테이블 데이터 전체를 가져옵니다.
2. inner join courses on e.course_id = c.course_id: courses를 enrolleds 테이블에 붙이는데, enrolleds 테이블의 course_id와 동일한 course_id를 갖는 courses의 테이블을 붙입니다.
3. select * : 붙여진 모든 데이터를 출력합니다.

UNION

• • Select를 두 번 할 게 아니라, 한번에 모아서 보고싶은 경우, 있을걸요!

    • 예를 들면 이렇게!
      

    근데, 그러려면 한 가지 조건이 있어요! 노란색과 파란색 박스의 필드명이 같아야 한답니다. 🙂 (당연하겠죠?)

• Union을 이용해서 아래와 같은 모습을 만들어볼까요?
  

select c1.title, c2.week, count(*) as cnt from courses c1
inner join checkins c2 on c1.course_id = c2.course_id
inner join orders o on c2.user_id = o.user_id
where o.created_at >= '2020-08-01'
group by c1.title, c2.week
order by c1.title, 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 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
)

3주차 숙제

• enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기. user_id도 같이 출력되어야 한다.

• 조인해야 하는 테이블: enrolleds, enrolleds_detail

• 조인하는 필드: enrolled_id

select e.enrolled_id,
	     e.user_id,
	     count(*) as cnt
  from enrolleds e
 inner join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id
 where ed.done = 1
 group by e.enrolled_id, e.user_id
 order by cnt desc