[SQL] inner join, left join, Union all

 

 

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

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

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

Join의 종류: Left Join, Inner Join

 

 

1) left join :  null 값도 함께 join이 됩니다.

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

 

2) inner join을 사용하면 null값이 없는 부분만 조인이 됩니다.

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

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

 

 

- 퀴즈 

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

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

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;

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

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

SELECT u2.name, count(u2.name) FROM orders o 
inner join users u2 
on o.user_id = u2.user_id 
WHERE o.email like '%naver.com'
group by u2.name

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

 

 

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

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

꿀팁! → round(숫자,자릿수) 를 이용해서 반올림을 해볼까요?

SELECT o.payment_method, round(avg(pu.point),2) as avg_point from point_users pu 
inner join orders o on pu.user_id  = o.user_id 
group by o.payment_method ;

 

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

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

꿀팁! → is_registered = 0 인 사람들을 세어보아요!

꿀팁! → order by 를 이용해서 내림차순으로 정렬하면 보기 좋겠죠?

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

 

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

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

꿀팁! → is_registered = 0 인 사람들을 세어보아요!

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 c.title

 

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

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

꿀팁! → group by, order by에 콤마로 이어서 두 개 필드를 걸어보세요!

SELECT c.title, c2.week, count(checkin_id) as cnt   from courses c 
inner join checkins c2 on c.course_id  = c2.course_id 
group by c.title, c2.week 
order by c.title, cnt desc

 

-  대부분 inner join을 이용하고, left join 을 사용하는 경우는 

" 없는 정보를 통하여 통계를 내고 싶을때 left join을 이용하면 됩니다."

 

// 회원 가입 하였는데, 포인트가 없는 사람
SELECT u.name, count(*) as cnt from users u 
left join point_users pu on u.user_id = pu.user_id 
where pu.point_user_id  is NULL 
GROUP by u.name
// 회원 가입 하였는데, 포인트가 있 사람
SELECT u.name, count(*) as cnt 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 u.name

 

(1) 연습문제

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

아래와 같은 결과를 보고 싶다면 어떻게 해야할까요?

 

• 이렇게 저렇게 해볼까요?힌트2 → Alias(별칭)도 잘 붙여주세요!
• 힌트3 → 비율은 소수점 둘째자리에서 반올림!
• 힌트1 → count 은 NULL을 세지 않는답니다!

SELECT count(pu.point > 0) as put_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';


//put_user_cnt 를 다르게 작성
SELECT  count(pu.point_user_id) as put_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'

 

 

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

(
	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-07-01'
	group 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
)

 

라스트 퀴즈)

 

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

 

• 조인해야 하는 테이블: enrolleds, enrolleds_detail
• 조인하는 필드: enrolled_id

SELECT e.enrolled_id , e.user_id , count(e.enrolled_id) 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 
order by max_count desc