Join
두 테이블의 공통 정보(key 값)를 기준으로 테이블을 연결해 한 테이블처럼 보는 것
- 동일한 이름과 정보가 담긴 필드를 두 테이블에 똑같이 담아놓는데, 이런 필드를 두 테이블을 연결해주는 열쇠라는 의미로 '키(key)'라고 부름
- key 값을 사용해 연결하고 싶은 테이블에 가서 같은 값을 가지는 key를 찾는데, 같은 key를 가지는 데이터가 여러 개 있으면 어느 데이터를 가져와서 연결해야 할지 알 수 없음
- cf. SQL의 Join은 엑셀의 vlookup과 동일
Join의 종류
Left Join: A와 B라는 테이블이 있는 경우, A쪽에 해당하는 데이터
- ex. 꽉찬 데이터 - 데이터의 A 필드값이 B 테이블에 연결해서 존재하는 경우
- ex. 비어있는 데이터 - 데이터의 A 필드값이 B 테이블에 존재하지 않는 경우
Inner Join: A와 B라는 테이블이 있는 경우, A와 B 모두에 해당하는 데이터
- ex. 같은 A를 두 테이블에서 모두 가지고 있는 데이터만 출력
Join 연습
실습1] orders 테이블에 users 테이블 연결
-- 주문 정보에 유저 정보를 연결해 분석 준비하기 -- 주문을 위해서는 회원정보가 필요하니, -- orders 테이블에 담긴 user_id는 모두 users 테이블에 존재 select * from orders o inner join users u on o.user_id = u.user_id;실습2] checkins 테이블에 users 테이블 연결
-- 오늘의 다짐 테이블에 유저 정보를 연결해 분석 준비 -- 연결의 기준이 되는 테이블을 from 절에, -- 기준이 되는 테이블에 붙이고 싶은 테이블을 join 절에 위치해 놓기 select * from checkins c inner join users u on c.users_id = u.users_id;실습3] enrolleds 테이블에 courses 테이블 연결
-- 수강등록 테이블에 과목 정보를 연결해 분석 준비 select * from enrolleds e inner join courses c on e.course_id = c.course_id;
- 쿼리 실행 순서: from → join → select
- from에 들어간 테이블을 기준으로 다른 테이블이 붙는다고 생각하기
Join + 배웠던 문법
실습1] checkins 테이블에 courses 테이블 연결해 통계치 내기
-- 오늘의 다짐 정보에 과목 정보를 연결해 과목별 오늘의 다짐 개수 세기 select c.title, count(*) as checkin_count from checkins ch inner join courses c on ch.course_id = c.course_id group by c.title;실습2] point_users 테이블에 users 테이블 연결해 순서대로 정렬
-- 유저의 포인트 정보가 담긴 테이블에 유저 정보를 연결해, -- 많은 포인트를 얻은 순서대로 유저의 데이터 뽑기 select * from point_users p inner join users u on p.user_id = u.user_id order by p.point desc;실습3] 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;
- 쿼리 실행 순서: from → join -> where → group by → select
- join의 실행 순서는 항상 from과 붙어 다닌다고 생각하기
쿼리 작성해보기
Join 연습 1
결제 수단 별 유저 포인트의 평균값 구하기
-- Join할 테이블: point_users, orders select o.payment_method, round(avg(p.point), 0) as avg_point from point_users p inner join orders o on p.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 c.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 course_id;
쿼리 작성해보기 (2)
Join 연습 4
웹개발, 앱개발 종합반의 week 별 체크인 수 세기
-- group by, order by에 필드 두개 넣기 select c.title, ch.week, count(*) as cnt from courses c inner join checkins ch on c.course_id = ch.course_id group by c.title, ch.week order by c.title, ch.week;
Join 연습 5
연습 4에서 8월 1일 이후에 구매한 고객들만 걸러내기
-- Join 할 테이블: courses에 checkins 붙이고, -- checkins에 orders한 번 더 붙이기 select c.title, ch.week, count(*) as cnt from courses c inner join checkins ch on c.course_id = ch.course_id inner join orders o on ch.user_id = o.user_id where o.created_at >= '2020-08-01' group by c.title, ch.week order by c.title, ch.week;
Left Join 연습
Inner Join vs Left Join
- Inner Join은 교집합, Left Join은 첫 번째 원에 붙이는 것
- Left Join은 어디에 무엇을 붙일건지 순서가 중요 ☆
Left Join은 언제 사용할까?
- 한 쪽의 필드 데이터를 다른 쪽의 필드 데이터에 모두 포함하지 않은 경우
- ex. 모든 유저가 포인트를 갖고 있지 않을 수 있음
select * from users u left join point_users pu on u.user_id = pu.user_id; -- 이 상태에서는 이런 것이 가능 -- 유저 중, 포인트가 없는 사람(=시작하지 않은 사람)의 통계 -- is null(null값인 것), is not null(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
퀴즈] 7월 10일 ~ 7월 19일에 가입한 고객 중,
포인트를 가진 고객의 숫자, 그리고 비율을 보고 싶다면?-- count는 NULL을 세지 않음 select count(pu.point_user_id) as pnt_user_cnt, count(*) as tot_user_cnt, round(count(pu.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';
결과물 합치기 - Union
Select 두 번이 아닌, 한 번에 모아서 보고 싶은 경우에 사용
-- 예시 ( 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 order 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 order 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 )
- 위의 경우, order by가 적용되지 않음(정렬이 깨져서 나옴)
- 이 때 SubQuery(서브쿼리)를 사용하면 됨
(❁´◡`❁)