[스파르타 내일배움] SQL 3주차 - Join (Inner Join, Left Join, Union)

강의명

[왕초보] 엑셀보다 쉬운, SQL 46회차

이범규 튜터 

 

 

---

Weekly I learned

 

Join 

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

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

Join의 종류: Left Join, Inner Join

 

Left Join 

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

• 어떤 데이터는 모든 필드가 채워져있지만, 어떤 데이터는 비어있는 필드가 있습니다.
• 꽉찬 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재해서 연결한 경우
• 비어있는 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재하지 않는 경우

 

 

Inner Join

• 여기서는 비어있는 필드가 있는 데이터가 없어요!
• 그 이유는, 같은 user_id를 두 테이블에서 모두 가지고 있는 데이터만 출력했기 때문이에요.

 

과목별 오늘의 다짐 갯수 세어보기

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

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

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

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

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

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

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 e.is_registered = 0
group by c.course_id 
order by cnt desc

[퀴즈] 웹개발, 앱개발 종합반의 week 별 체크인 수

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

**8월 1일 이후에 구매한 고객들만 발라내기 

select c.title, c2.week, count(*) as cnt from courses c 
inner join checkins c2 on c.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 c.title, c2.week 
order by c.title, c2.week

 

 

inner join vs left join

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

users 테이블과 ↔ point_users 테이블을 left join 해봅시다

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

유저 중에, 포인트가 없는 사람(=즉, 시작하지 않은 사람들)의 통계!

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

 

 

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
)

[숙제]

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