[MySQL] 수업 3주차 - Join

[수업 목표]

1. 여러 테이블의 정보를 연결하는 Join을 이해한다.
2. 연결된 정보를 바탕으로 보다 풍부한 데이터분석을 연습한다.
3. 아래 위로 결과를 연결하는 Union을 공부한다.

Join이란?

두 테이블의 공통된 정보 (key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것을 의미해요. 예) user_id 필드를 기준으로 users 테이블과 orders 테이블을 연결해서 한 눈에 보고 싶어요!

위의 예시와 같이, 두 테이블의 정보를 연결해서 함께 보고싶을 때가 있겠죠? 그럴 때를 대비해서 무언가 연결된 정보가 있을 때, user_id 처럼 동일한 이름과 정보가 담긴 필드를 두 테이블에 똑같이 담아놓는답니다. 이런 필드를 두 테이블을 연결시켜주는 열쇠라는 의미로 'key'라고 불러요.

 

Join의 종류:

1. Left Join = 두 집합에서 겹치는 관계를 보여줌
2. Inner Join = 두 테이블 사이의 교집합을 의미함 (더 많이 쓰임)

Left join & Inner Join

[Left Join 활용하기]

users 테이블의 user_id와 point_users 테이블의 user_id를 left join 하는 방법

join 활용예시

Null이 생기는 경우 매칭이 안되는 경우임 

• 따라서 Count(*)를 해도 Null은 세지 않음!

where point_user_id is Null 로 Null 만 확인할 수 있음

where point_user_id is not Null 로 Null이 없는 것만 확인할 수 있음

 

[Inner Join 활용하기]

Checkin 테이블과 Users 테이블 연결하기

• 우선 select * from checkins, users를 진행
• 어떤 필드를 공유하는지 key 값 발견하기
• users_id를 공유하는거 확인
• on 별칭.users_id = 별칭.users_id 진행
• 연결 완성

 

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

 

[Union 배우기]

 

Select를 두 번 할 게 아니라, 한번에 모아서 보고싶은 경우

근데, 그러려면 한 가지 조건이 있어요! 노란색과 파란색 박스의 필드명이 같아야 한답니다. 

예시 ( 쿼리 A ) union all ( 쿼리 B) 형식으로 작성

[Join과 기존 문법 활용 해보기]

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

 

 

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

 

select co.title, count(co.title) as checkin_count from checkins ci

inner join courses co on ci.course_id = co.course_id

group by co.title

결과

 

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

 

유저의 포인트 정보가 담긴 테이블에 유저 정보를 연결해서, 많은 포인트를 얻은 순서대로 유저의 데이터를 뽑아보자!

 

SELECT * from point_users pu 

inner join users u on pu.user_id = u.user_id 

order by pu.point DESC

결과

쫌더 이쁘게 user_id, name, email 만 포함해서 정리해보기!

 

 

SELECT pu.user_id, u.name, u.email from point_users pu 

inner join users u on pu.user_id = u.user_id 

order by pu.point DESC

정리 결과

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

주문 정보에 유저 정보를 연결해 네이버 이메일을 사용하는 유저 중, 성씨별 주문건수를 세어보자!

 

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 

 

위 쿼리가 실행되는 순서: 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 필드를 기준으로 뭉쳐진 갯수를 세어서 출력해줍니다.

[퀴즈 풀어보기]

 

 

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

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

 

SELECT o.payment_method as pm, round(AVG(`point`)) as average_point from point_users pu 

inner join orders o on pu.user_id = o.user_id 

group by o.payment_method

 

 

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

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

 

SELECT u.name, count(*) 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. 과목 별로 시작하지 않은 유저들을 세어보기

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

 

SELECT c.course_id, c.title, count(*) as count_notstarted from courses c 

inner join enrolleds e on c.course_id = e.course_id 

where is_registered = 0

group by c.course_id 

 

[팁]

 

1. group by, order by에 콤마로 이어서 두 개 필드를 걸 수 있음!

2. 테이블 3개 묶기!

inner join을 한번 더 하기

 

 

3. SQL은 결과값에 도달하는 방법이 여러가지임!!

 

[3주차 문법 정리]

Ratio	비율 A/B	SELECT count(pu.point_user_id),    count(u.user_id),    count(pu.point_user_id) / count(u.user_id) as ratio
Ratio + round	반올림 적용	round(  count(pu.point_user_id) / count(u.user_id)) as ratio