3주차_ Left Join, Inner Join 사용해보기

Join이란?

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

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

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

 

 

1. point_users 테이블과 users 잇기 - user_id (key) 로 묶기

 

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

 

 

2. 유저 데이터로 Left Join 이해해보기 - Left Join

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

 

 

3. 유저 데이터로 Inner Join 이해해보기 - Inner Join

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

 

 

[응용]  enrolleds 테이블에 courses 테이블 연결해보기 - inner join,  Alias 사용

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

[처리 순서]

- from enrolleds: enrolleds 테이블 데이터 전체를 가져옵니다.

- inner join courses on e.course_id = c.course_id: courses를 enrolleds 테이블에 붙이는데, enrolleds 테이블의 course_id와 동일한 course_id를 갖는 courses의 테이블을 붙입니다.

- select * : 붙여진 모든 데이터를 출력합니다.

 

---

Inner Join 사용하기

 

[응용] 많은 포인트를 얻은 순서대로 유저 데이터 정렬해서 보기

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

 

 

[응용] 네이버 이메일 사용하는 유저의 성씨별 주문건수 세어보기

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

 

 

[응용] 결제 수단 별 유저 포인트의 평균값 구해보기

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

🍯 round (숫자, 자릿수) 이용해서 반올림

 

 

[응용] 결제하고 시작하지 않은 유저들을 성씨별로 세어보기

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

🍯 is_registered = 0 뜻, 시작하지 않은 유저

🍯 desc 내림차순

 

[응용] 웹개발, 앱개발 종합반의 week 별 체크인 수를 세어보기

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

 

 

[응용] 웹개발, 앱개발 종합반의 week 별 체크인 수를 세어보고, 8월 1일 이후에 구매한 고객들만 발라내기

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

 

 

---

Left  Join 사용하기

모든 유저가 포인트를 갖고 있지를 않을 수 있잖아요!

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

 

[응용] 유저 중에, 포인트가 없는 사람(=즉, 시작하지 않은 사람들)의 통계! - 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

- 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 not NULL
group by name

 

 

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

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'

🍯 count 는 NULL을 세지 않습니다.

🍯 Alias(별칭)도 잘 붙여주세요!

🍯 비율은 소수점 둘째자리에서 반올림!

 

 

---

결과물 합치기 : Union

Select를 두 번 할 게 아니라, 한번에 모아서 보고싶은 경우, 있을걸요! 근데, 그러려면 한 가지 조건이 있어요! 노란색과 파란색 박스의 필드명이 같아야 한답니다. 🙂 (당연하겠죠?)

 

(
	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을 사용하면 내부 정렬이 먹지 않아요.

 

 

[응용] enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기. user_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

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

- 조인하는 필드: enrolled_id