[스파르타코딩클럽]3주차 SQL

Join - 두 개의 테이블을 연결해서 볼 수 있어요.

테이블은 용도별로 묶어 두었기 때문에 분리되어있다.

==================================================

Join이란?

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

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

SQL의 Join은 엑셀의 vlookup과 동일하다고 생각하시면 됩니다 :-) 

==================================================

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 (교집합)

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

여기서는 비어있는 필드가 있는 데이터가 없어요!

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

==================================================

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

위 쿼리가 실행되는 순서: from → join → select

1. from enrolleds: enrolleds 테이블 데이터 전체를 가져옵니다.
2. inner join courses on e.course_id = c.course_id: courses를 enrolleds 테이블에 붙이는데, enrolleds 테이블의 course_id와 동일한 course_id를 갖는 courses의 테이블을 붙입니다.
3. select * : 붙여진 모든 데이터를 출력합니다.

==================================================

'오늘의 다짐' 정보에 과목 정보를 연결해 과목별 '오늘의 다짐' 갯수를 세어보자!

SELECT c.course_id , c2.title , COUNT(*) as cnt 
FROM checkins c inner join courses c2
on c.course_id = c2.course_id 
GROUP by c.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 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

위 쿼리가 실행되는 순서: from → join → where → group by → select

1. from orders o: orders 테이블 데이터 전체를 가져오고 o라는 별칭을 붙입니다.

2. inner join users u on o.user_id = u.user_id : users 테이블을 orders 테이블에 붙이는데, 
   orders 테이블의 user_id와 동일한 user_id를 갖는 users 테이블 데이터를 붙입니다.
   (*users 테이블에 u라는 별칭을 붙입니다)

3. where u.email like '%naver.com': users 테이블 email 필드값이 naver.com으로 끝나는 값만 가져옵니다.

4. group by u.name: users 테이블의 name값이 같은 값들을 뭉쳐줍니다.

5. select u.name, count(u.name) as count_name : users 테이블의 name필드와 name 필드를 
   기준으로 뭉쳐진 갯수를 세어서 출력해줍니다.

==================================================

결제 수단 별 유저 포인트의 평균값 구해보기
(어느 결제수단이 가장 열심히 듣고 있나~)

join 할 테이블: `point_users` 에, `orders` 를 붙이기

SELECT o.payment_method , ROUND(AVG(pu.point),0)
FROM orders o inner join point_users pu 
on o.user_id = pu.user_id
GROUP BY o.payment_method 


결제하고 시작하지 않은 유저들을 성씨별로 세어보기
(어느 성이 가장 시작을 안하였는가~)

join 할 테이블: enrolleds 에, users 를 붙이기

SELECT name, count(*) cnt_name
FROM enrolleds e inner join users u 
on e.user_id = u.user_id
WHERE e.is_registered = 0
GROUP by name
ORDER BY cnt_name desc


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

join 할 테이블: `courses`에, `enrolleds` 를 붙이기

SELECT c.course_id , c.title , COUNT(*) cnt_notstart
FROM courses c inner join enrolleds e 
on c.course_id = e.course_id
WHERE e.is_registered = 0
GROUP BY c.title 

==================================================

웹개발, 앱개발 종합반의 week 별 체크인 수를 세어볼까요? 보기 좋게 정리해보기!

join 할 테이블: courses에, checkins 를 붙이기

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 


연습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

==================================================

inner join 은 교집합, left join 은 첫번째 원에 붙이는 것!

그래서! left join은 어디에 → 뭐를 붙일건지, 순서가 중요하답니다!


==================================

union 에서는 order가 적용되지 않는다.

==================================

숙제: enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 
완료한 강의 수가 많은 순서대로 정렬해보기. user_id도 같이 출력되어야 한다.

select e.enrolled_id , e.user_id , COUNT(*) as max_count
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 max_count desc