[SQL] 기초개념 - 3주차

1. JOIN이란?

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

 

1) join의 종류 :

- left join : 테이블 A와 b의 공통되는 일부key값 

출처 : 스파르타코딩클럽

- inner join : 교집합 개념

출처 : 스파르타코딩클럽

2. INNER JOIN

1)실습 (join만 사용)

- inner join을 사용해서 orders 테이블에 users 테이블 연결해보기

select * from orders o inner join users u on o.user_id = u.user_id;

- inner join을 사용해서 checkins 테이블에 users 테이블 연결해보기

select * from checkins c inner join users u on c.user_id = u.user_id;

- inner join을 사용해서 enrolleds 테이블에 courses 테이블 연결해보기

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

2) 위 SQL 쿼리가 실행되는 순서 :

from → join → select

 

3) 실습 (join+다른 문법 사용)

- checkins 테이블에 courses 테이블 연결해서 통계치 내보기

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

- point_users 테이블에 users 테이블 연결해서 순서대로 정렬해보기

select * from point_users p inner join users u  on p.user_id = u.user_id order by p.point desc

- orders 테이블에 users 테이블 연결해서 통계치 내보기

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

4) 위 SQL 쿼리가 실행되는 순서 :

from → join → where → group by → select

 

5) 퀴즈

- 결제 수단 별 유저 포인트의 평균값 구해보기 (join 할 테이블: point_users 에, orders 를 붙이기)

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

- 결제하고 시작하지 않은 유저들을 성씨별로 세어보기 (join 할 테이블: enrolleds 에, users 를 붙이기)

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

- 과목 별로 시작하지 않은 유저들을 세어보기 (join 할 테이블: courses에, enrolleds 를 붙이기)

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

- 웹개발, 앱개발 종합반의 week 별 체크인 수를 세어볼까요 + 보기 좋게 정리 (join 할 테이블: courses에, checkins 를 붙이기)

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

- 위 퀴즈에서, 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

3. LEFT JOIN

1) 실습

모든 유저가 곤련된 데이터를 가지고 있지 않은 경우 사용

- users 테이블과 ↔ point_users 테이블을 left join

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

- 7월10일 ~ 7월19일에 가입한 고객 중, 포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 보자

select count(point_user_id) as pnt_user_cnt,        count(*) as tot_user_cnt,        round(count(point_user_id)/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'

2) is NUL, is not NULL

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

* count는 NULL값의 수는 세지 않는다.

4. UNION 이란?

- 결과물 합치기 : 두개의 테이블을 하나로 합치기 위해서 union all사용

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

 

---

5. 퀴즈풀어보기

- 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, e.user_id  order by cnt desc