-
Join
Join을 사용해서 Key값으로 두 테이블 연결해보기
select * from point_users
left join users
on point_users.user_id = users.user_id
Left Join
여기서 A와 B는 각각의 테이블을 의미합니다. 둘 사이의 겹치는 부분은, 테이블 A와 B의 key 값이 연결되는 부분이에요.
👉 어떤 데이터는 모든 필드가 채워져있지만, 어떤 데이터는 비어있는 필드가 있습니다.
꽉찬 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재해서 연결한 경우 비어있는 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재하지 않는 경우
- 비어있는 데이터의 경우, 회원이지만 수강을 등록/시작하지 않아 포인트를 획득하지 않은 회원인 경우에요!
-> users 테이블과 point_users 테이블을 합치고,
users에는 u 별칭을, point_users에는 p 별칭을 붙이고 나서. left join을 사용.
select * from users u
left join point_users p
on u.user_id = p.user_id
Inner Join
select * from users u -> 교집합이 나옴.
inner join point_users p
on u.user_id = p.user_id
[실습] 1 orders 테이블에 users 테이블 연결해보기
[실습] 2 checkins 테이블에 users 테이블 연결해보기
[실습] 3 enrolleds 테이블에 courses 테이블 연결해보기
-> 무조건 user_id 필드를 이어주는게 아니라, 양 테이블을 보고 공통되는 필드를 이어줘야함.
1 . select * from orders o
inner join users u on o.user_id = u.user_id
2. select * from checkins c
left join users u on c.user_id = u.user_id
3. select * from enrolleds e
left join courses c on e.course_id = c.course_id
1. checkins 테이블에 courses 테이블 연결해서 통계치 내보기
'오늘의 다짐' 정보에 과목 정보를 연결해 과목별 '오늘의 다짐' 갯수를 세어보자!
2. point_users 테이블에 users 테이블 연결해서 순서대로 정렬해보기
유저의 포인트 정보가 담긴 테이블에 유저 정보를 연결해서, 많은 포인트를 얻은 순서대로 유저의 데이터를 뽑아보자!
3. orders 테이블에 users 테이블 연결해서 통계치 내보기
주문 정보에 유저 정보를 연결해 네이버 이메일을 사용하는 유저 중, 성씨별 주문건수를 세어보자!
1. select c1.course_id, count(*) from checkins c1
inner join courses c2 on c1.course_id = c2.course_id
group by c1.course_id
위에서 count를 cnt로 설정하고 title까지 표시하면
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. select * from point_users p
inner join users u on p.user_id = u.user_id
order by p.point desc
위에서 깔끔하게 user_id, name, e-mail, point 만을 추출하려면
select p.user_id, u.name, u.email, p.point from point_users p
inner join users u on p.user_id = u.user_id
order by p.point desc
3. select u.name, count(*) from orders o
inner join users u on o.user_id = u.user_id
where o.email like '%naver.com'
group by u.name
퀴즈
1. 결제 수단 별 유저 포인트의 평균값 구해보기
(어느 결제수단이 가장 열심히 듣고 있나~)
join 할 테이블: point_users 에, orders 를 붙이기
2. 결제하고 시작하지 않은 유저들을 성씨별로 세어보기 (내림차순)
(어느 성이 가장 시작을 안하였는가~)
join 할 테이블: enrolleds 에, users 를 붙이기
3. 과목 별로 시작하지 않은 유저들을 세어보기
join 할 테이블: courses에, enrolleds 를 붙이기4. 웹개발, 앱개발 종합반의 week 별 체크인 수를 세어볼까요? 보기 좋게 정리해보기!
join 할 테이블: courses에 checkins 를 붙이기
5. 연습4번에서, 8월 1일 이후에 구매한 고객들만 발라내어 보세요! (inner join 2번)
join 할 테이블: courses에, checkins 를 붙이고!
+ checkins 에, orders 를 한번 더 붙이기! (구매는 orders 테이블에 있음)1. select o.payment_method, avg(pu.point) from point_users pu
inner join orders o on pu.user_id = o.user_id
group by o.payment_method
select o.payment_method, round(avg(pu.point),2) from point_users pu -> 소수점 둘째자리까
inner join orders o on pu.user_id = o.user_id
group by o.payment_method
2.
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
order by count(*) desc
3.
select c.course_id, c.title, count(*) as cnt from enrolleds e
inner join courses c on e.course_id = c.course_id
where e.is_registered = 0
group by c.course_id
4.
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
5.
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 과 Inner Join
교집합인 inner join 과 달리, left join은 어디에 → 뭐를 붙일건지, !순서! 가 중요
select * from users u
left join point_users pu on u.user_id = pu.user_id -> point가 없는 user도 포함되므로 NULL이 나옴.
NULL을 찾고 뺴는 법. -> NULL을 추출하기 위해서는 Left Join을 해야한다.
(단, count는 NULL을 세지 않는다!)
select * from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is NULL
<퀴즈>
1. 성씨별 등록 안한사람(point_user_id가 NULL인 사람) 구하기. (is NULL)
2. 성씨별 등록 한사람 구하기. (is not NULL)
3. 7월10일 ~ 7월19일에 가입한 고객 중, 포인트를 가진 고객의 숫자, 그리고 전체 숫자,
그리고 비율을 보고 싶어요!
1. 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
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 not NULL
group by u.name
3.
select count(pu.point_user_id) as pnt_user_cnt,
count(u.user_id) as tot_user_cnt,
count(pu.point_user_id)/count(u.user_id) 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'
소수점= 2 적용
select count(pu.point_user_id) as pnt_user_cnt,
count(u.user_id) as tot_user_cnt,
round (count(pu.point_user_id)/count(u.user_id),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를 두 번 할 게 아니라, 한번에 모아서 보고싶은 경우
코드스니펫
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
8월
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
7월
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
7월 + 8월 = Union all
(
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 by 는 정렬이므로 빼줘도 됨. 왜냐하면 union을 사용하면 내부 정렬이 먹지 않기 때문인데,
이 때 유용한 방법은 바로, SubQuery(서브쿼리)
마지막 Quiz
enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기.
user_id도 같이 출력되어야 한다.
- 조인해야 하는 테이블: `enrolleds`, `enrolleds_detail`
- 조인하는 필드: `enrolled_id`select ed.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 ed.enrolled_id, e.user_id
order by cnt desc
'SQL' 카테고리의 다른 글
SQL 기본 (0) 2023.05.16 SQL 4주차(2) (0) 2023.05.08 SQL 4주차(1) (0) 2023.05.08 SQL 2주차 (0) 2023.05.07 SQL 1주차 (0) 2023.05.06