[22.02.11] SQL - Left join, Inner join (3주차)

Join이란?

여러 정보를 한 눈에 보고 싶을 때 두 테이블의 공통된 정보(key 값)를 기준으로 연결해서 한 테이블처럼 보는 것.

ex) user_id 필드를 기준으로 users 테이블과 orders 테이블을 연결해서 보고 싶다.

SELECT * FROM users u
left join point_users p
on u.user_id = p.user_id

 

Left Join

A 데이터의 필드는 모두 채워져 있지만, B 데이터에는 비어있는 필드가 있다.

꽉찬 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재해서 연결한 경우

비어있는 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재하지 않는 경우

 

 

Inner Join

두 테이블의 교집합.

비어있는 필드가 있는 데이터가 없음.

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

 

 

Union

select를 두 번 할 게 아니라, 한번에 모아보고 싶을 때

A 테이블과 B 테이블의 필드명이 같아야 함

union 안에 있으면 order by는 먹히지가 않음

[(A+order by)+(B+order by)] 가 아니라 [(A)+(B) order by] 는 됨

예시)

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

 

 

 

 

 

---

 

[연습] orders 테이블에 users 테이블 연결하기

SELECT * FROM orders o  
inner join users u
on o.user_id = u.user_id;

 

[연습] checkins 테이블에 users 테이블 연결하기

SELECT * FROM checkins c 
inner join users u 
on c.user_id = u.user_id;

 

[연습] enrolleds 테이블에 courses 테이블 연결하기

SELECT * FROM enrolleds e
inner join courses c
on e.course_id = c.course_id;

 

---

[연습] 과목별 오늘의 다짐 갯수 세어보기

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

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

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

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

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

 

---

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

내가 쓴 쿼리 = 정답 쿼리

SELECT o.payment_method, round(avg(pu.point),0) FROM point_users pu 
inner join orders o on pu.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
order by cnt DESC;

 

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

내가 쓴 쿼리 = 정답 쿼리

select c.course_id, c.title, count(*) as cnt from courses c
inner join enrolleds e on c.course_id = e.course_id 
WHERE e.is_registered = 0
group BY c.course_id;

 

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

내가 쓴 쿼리 = 정답 쿼리

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

 

[문제] 위 문제에서, 8월 1일 이후에 구매한 고객들만 발라내어 보기

내가 쓴 쿼리

select c.title, c2.week, o.created_at, count(*) 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 LIKE '2020-08%'
GROUP by c.title, c2.week
order by c.title, c2.week;

정답 쿼리

select c.title, c2.week, count(*) 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;

= 둘 다 나오는 값은 똑같음

 

 

[문제] 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_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';

어려워서 가닥도 못 잡았음

 

---

 

[숙제] 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 done ='1'
group by e.enrolled_id, e.user_id 
order by cnt desc;