- Join
Left Join과 Inner Join 2가지가 있다.
1) Left Join
왼쪽을 기준으로 오른쪽을 붙인다.
select * from users u
left join point_users pu
on u.user_id = pu.user_id
2) Inner Join
둘이 중첩된 것을 나타낸다.
select * from users u
inner join point_users pu
on u.user_id = pu.user_id
실습 1)
orders 테이블에 users 테이블 연결해보기
select * from orders o
inner join users u
on o.user_id = u.user_id
※inner join이 조금 더 쉽다 처음 연습할때는 inner join으로 하자
실습 2)
checkins 테이블에 users 테이블 연결해보기
select * from checkins c
inner join users u
on c.user_id = u.user_id
실습 3)
enrolleds 테이블에 courses 테이블 연결해보기
select * from enrolleds e
inner join courses c
on e.course_id =c.course_id
- 배웠던 문법을 Join과 함께 사용해보기
1) checkins 테이블에 courses 테이블 연결해서 통계치 내보기
과목별 오늘의 다짐 갯수 세어보기
select c1.course_id, c2.title, count(*) as cnt from checkins c1
inner join courses c2
on c1.course_id = c2.course_id
group by c1.course_id
2) point_users 테이블에 users 테이블 연결해서 순서대로 정렬해보기
많은 포인트를 얻은 순서대로 유저 데이터 정렬해서 보기
select pu.user_id, u.name, u.email , pu.point from point_users pu
inner join users u
on pu.user_id = u.user_id
order by pu.point DESC
3) orders 테이블에 users 테이블 연결해서 통계치 내보기
네이버 이메일 사용하는 유저의 성씨별 주문건수 세어보기
select u.name , count(*) as cnt from orders o
inner join users u
on o.user_id = u.user_id
where o.email like '%naver.com'
group by u.name
퀴즈
Join 연습1
결제 수단 별 유저 포인트의 평균값 구해보기
join 할 테이블: point_users 에, orders 를 붙이기
select o.payment_method, round(avg(pu.point),0) as avg_point from point_users pu
inner join orders o
on pu.user_id = o.user_id
group BY o.payment_method
Join 연습2
결제하고 시작하지 않은 유저들을 성씨별로 세어보기
join 할 테이블: enrolleds 에, users 를 붙이기
select u.name, count(*) as cnt_name from enrolleds e
inner join users u on e.user_id = u.user_id
where e.is_registered = 0
group by u.name
order by cnt_name desc
Join 연습3
과목 별로 시작하지 않은 유저들을 세어보기
join 할 테이블: courses에, enrolleds 를 붙이기
select e.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
Join 연습4
웹개발, 앱개발 종합반의 week 별 체크인 수를 세어볼까요? 보기 좋게 정리해보기!
join 할 테이블: courses에, checkins 를 붙이기
select c1.title, c2.week, count(*) as cnt from courses c1
inner join checkins c2 on c1.course_id = c2.course_id
group by c1.title , c2.week
order by c1.title , c2.week
Join 연습5
연습4번에서, 8월 1일 이후에 구매한 고객들만 발라내어 보세요!
join 할 테이블: courses에, checkins 를 붙이고! + checkins 에, orders 를 한번 더 붙이기!
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
left join은 어디에 → 뭐를 붙일건지, 순서가 중요하다
회원인데 포인트가 없는 사람
1) 포인트가 없는사람
select from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is NULL
2) 회원인데 포인트가 없는 사람
select u.name , count() as cnt from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is NULL
group by u.name
3) 포인트가 있는사람들만
select u.name , count(*) as cnt 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 u.name
퀴즈
7월10일 ~ 7월19일에 가입한 고객 중,
포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 보고 싶어요!
select count(pu.point_user_id) as pnt_user_cnt,
count(u.user_id) as tot_user_cnt,
round(count(pu.point)/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'
- 결과물 합치기! Union 배우기
(
select '7월' as month, 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
)
union all
(
select '8월' as month, 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
)
이렇게 하면 내부 order는 안먹히게 된다. 즉 빼도 같은 값이 나온다.
그러므로 order을 빼고 밖에서 다시 지정해주면 된다.
(
select '7월' as month, 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
)
union all
(
select '8월' as month, 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
)
숙제
문 :
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
order by cnt desc
