[SQL] Inner Join, Left Join, Union

 

inner join

 

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

---

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

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

 

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

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

 

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

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

 

---

Left Join

 

 

select *
  from users u
  left join point_users p
    on u.user_id = p.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'

 

---

Union

union을 하기 위한 전제조건은 합하고자하는 데이터들의 필드명이 같아야합니다.

 

 

 

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

 

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