[목표]

1. 여러 테이블의 정보를 연결하는 Join을 이해
2. 연결된 정보를 바탕으로 보다 풍부한 데이터분석을 연습
3. 아래 위로 결과를 연결하는 Union을 공부

01. Join

: 여러 테이블을 연결해 한 눈에 보고 싶다면?

1) Join 이란?

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

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

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

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

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

👉 [오늘의 꿀팁!] 혹시 엑셀을 잘 쓰신다면?
SQL의 Join은 엑셀의 vlookup과 동일하다고 생각하시면 됩니다 :-)

2) Join의 종류: Left Join, Inner Join

▶︎ Left Join 이해하기

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

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

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

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

▶︎ Inner Join 이해하기

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

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

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

그 이유는, 같은 user_id를 두 테이블에서 모두 가지고 있는 데이터만 출력했기 때문이에요.

02. Join 본격 사용해보기

1) Join 함께 연습해보기

▶︎ orders 테이블에 users 테이블 연결해보기

• 주문 정보에 유저 정보를 연결해 분석을 위한 준비를 하려고 합니다! 우선 inner Join을 사용해서 주문 정보에, 유저 정보를 붙여서 볼까요?

select * from orders o
 inner join users u on o.user_id = u.user_id;

▶︎ checkins 테이블에 users 테이블 연결해보기

• '오늘의 다짐' 테이블에 유저 정보를 연결해 분석을 위한 준비를 하려고 합니다! 우선 Inner Join을 사용해서 '오늘의 다짐' 테이블에, 유저 테이블을 붙여서 볼까요?

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

👉 [오늘의 팁!]
연결의 기준이 되고싶은 테이블을 from 절에,
기준이 되는 테이블에 붙이고 싶은 테이블을 Join 절에 위치해 놓습니다.

▶︎ enrolleds 테이블에 courses 테이블 연결해보기

• '수강 등록' 테이블에 과목 정보를 연결해 분석을 위한 준비를 하려고 합니다! 우선 Inner Join을 사용해서 '수강 등록' 테이블에, 과목 테이블을 붙여서 볼까요?

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

2) Join : SQL 쿼리가 실행되는 순서

👉 위 쿼리가 실행되는 순서: 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 * : 붙여진 모든 데이터를 출력합니다.

• 항상 from에 들어간 테이블을 기준으로, 다른 테이블이 붙는다고 생각하면 편합니다!

03. 배웠던 문법을 Join과 함께 사용해보기

1) 배웠던 문법 Join과 함께 연습해보기

▶︎ checkins 테이블에 courses 테이블 연결해서 통계치 내보기

• '오늘의 다짐' 정보에 과목 정보를 연결해 과목별 '오늘의 다짐' 갯수를 세어보자!

select co.title, count(co.title) as checkin_count from checkins ci
 inner join courses co on ci.course_id = co.course_id 
 group by co.title

👉 [오늘의 팁!]
2주차에 배운 alias는 이렇게 사용하면 편합니다. 연결되는 테이블이 많아지면서 필드명과 테이블명이 헷갈려 실수할 수 있는데, 이렇게 alias를 지정해 주면 편하고 깔끔하게 SQL 쿼리를 작성할 수 있어요.

▶︎ point_users 테이블에 users 테이블 연결해서 순서대로 정렬해보기

• 유저의 포인트 정보가 담긴 테이블에 유저 정보를 연결해서, 많은 포인트를 얻은 순서대로 유저의 데이터를 뽑아보자!

select * from point_users p
 inner join users u on p.user_id = u.user_id
 order by p.point desc

▶︎ orders 테이블에 users 테이블 연결해서 통계치 내보기

• 주문 정보에 유저 정보를 연결해 네이버 이메일을 사용하는 유저 중, 성씨별 주문건수를 세어보자!

select u.name, count(u.name) as count_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

2) SQL 쿼리가 실행되는 순서

👉 위 쿼리가 실행되는 순서: from → join → where → group by → select

1. from orders o: orders 테이블 데이터 전체를 가져오고 o라는 별칭을 붙입니다.
2. inner join users u on o.user_id = u.user_id : users 테이블을 orders 테이블에 붙이는데, orders 테이블의 user_id와 동일한 user_id를 갖는 users 테이블 데이터를 붙입니다. (*users 테이블에 u라는 별칭을 붙입니다)
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) as count_name : users 테이블의 name필드와 name 필드를 기준으로 뭉쳐진 갯수를 세어서 출력해줍니다.

• Join의 실행 순서는 항상 from 과 붙어다닌다고 생각하면 편해요!

04. 이제는 실전! 본격 쿼리 작성

퀴즈 1) 결제 수단 별 유저 포인트의 평균값 구해보기

• join 할 테이블: point_users 에, orders 를 붙이기
• 꿀팁! → round(숫자,자릿수) 를 이용해서 반올림을 해볼까요?

select o.payment_method, round(AVG(p.point)) from point_users p
 inner join orders o on p.user_id = o.user_id 
 group by o.payment_method

퀴즈 2) 결제하고 시작하지 않은 유저들을 성씨별로 세어보기

• join 할 테이블: enrolleds 에, users 를 붙이기
• 꿀팁! → is_registered = 0 인 사람들을 세어보아요!
• 꿀팁! → order by 를 이용해서 내림차순으로 정렬하면 보기 좋겠죠?

select name, count(*) as cnt_name from enrolleds e
 inner join users u on e.user_id = u.user_id 
 where is_registered = 0
 group by name
 order by cnt_name desc

퀴즈 3) 과목 별로 시작하지 않은 유저들을 세어보기

• join 할 테이블: courses에, enrolleds 를 붙이기
• 꿀팁! → is_registered = 0 인 사람들을 세어보아요!

select c.course_id, c.title, count(*) as cnt_notstart from courses c
inner join enrolleds e 
on c.course_id = e.course_id
where is_registered = 0
group by c.course_id

05. 한번 더 총복습!

연습 1) 웹개발, 앱개발 종합반의 week 별 체크인 수를 세어볼까요? 보기 좋게 정리해보기!

• join 할 테이블: checkins에, courses 를 붙이기
• 꿀팁! → group by, order by에 콤마로 이어서 두 개 필드를 걸어보세요!

select c2.title, c1.week, count(*) from checkins c1
 inner join courses c2 on c1.course_id = c2.course_id 
 group by c2.title, c1.week 
 order by c2.title, c1.week 

연습 2) 연습1번에서, 8월 1일 이후에 구매한 고객들만 발라내어 보세요!

• join 할 테이블: checkins에, courses 를 붙이고! + checkins 에, orders 를 한번 더 붙이기!
• 꿀팁! → checkins 테이블에 inner join을 한번 더 걸고, where 절로 마무리!

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

06. Left Join 복습

1) 다시한번 복습! inner join vs left join

• inner join 은 교집합, left join 은 첫번째 원에 붙이는 것!
• left join은 어디에 → 뭐를 붙일건지, 순서가 중요해요!

2) Left join 언제쓸까요?

예를 들면 모든 유저가 포인트를 갖고 있지를 않을 수 있잖아요!
users 테이블과 ↔ point_users 테이블을 left join 해봅시다.

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

이 상태에선, 이런 게 가능해요.
💡 유저 중에, 포인트가 없는 사람(=즉, 시작하지 않은 사람들)의 통계!

• 속닥속닥) is NULL , is not 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 name, count(*) from users u
  left join point_users pu on u.user_id = pu.user_id
 where pu.point_user_id is not NULL
 group by name

3) 여기서 퀴즈! - 막해보기

▶︎ 7월10일 ~ 7월19일에 가입한 고객 중,
포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 보고 싶어요!

• 힌트1 → count 은 NULL을 세지 않는답니다!
• 힌트2 → Alias(별칭)도 잘 붙여주세요!
• 힌트3 → 비율은 소수점 둘째자리에서 반올림!

select count(point_user_id) as pnt_user_cnt,
       count(*) as tot_user_cnt,
       round(count(point_user_id)/count(*),2) as ratio
  from users u
  left join point_users pu on u.user_id = pu.user_id
 where u.created_at between '2020-07-10' and '2020-07-20'

07. 결과물 합치기! Union 배우기

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

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

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

• 8월

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

• 7월

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 '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
)

• 앗, 그런데, 한 가지! 정렬이 깨졌네요!? 😂
  👉 네 맞습니다! union을 사용하면 내부 정렬이 먹지 않아요.
  이 때 유용한 방법이 있지요. 바로, SubQuery(서브쿼리)!
  4주차에 공부한다고 하니 기대해주세요!

08. 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

3주차 수업 완강했어요! 👏

💡공부를 마치며...

1) 연결의 기준이 되고싶은 테이블을 from 절에,
기준이 되는 테이블에 붙이고 싶은 테이블을 Join 절에 위치해 놓습니다.
ㄴ 기준 정하는게 좀 헷갈린다...더 많은 예제를 통해 확인해 봐야겠다.