SQL 3주차: Join(Inner Join, Left Join), Union all

이번주 SQL에서는 Join에 대해 배웠다.

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

만약 checkins 테이블 정보와 users 테이블 정보를 함께 보고 싶다면? 조인을 쓰면 된다.

두 테이블에서 공통되는 필드를 꼽아 join ... on ... = ... 이런 식으로 쓰면 되는 것이다.

이 때 공통되는 필드를 key라고 한다. 엑셀로 따지면 vlookup이랑 같다.

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

 

이 중 우리는 Left Join이라는 왼쪽에다 붙이는 조인과,

Inner Join이라는 NULL 부분 빼고 교집합 부분만 보이는 조인을 배웠다.

먼저 left join을 먼저 보면,

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

 

그 다음으로 inner join은 다음과 같다.

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

참고로 inner join에는 NULL값이 없고, 두 테이블에서 모두 가지고 있는 데이터만 출력한다.

 

SQL 퀴즈를 풀면서 느낀 점은, 줄마다 순서를 잘 세워서 써야 한다는 점. SQL 쿼리가 실행되는 순서에 유의해야 한다.

만약 이런 코드가 있을 때

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

SQL 쿼리가 실행되는 순서는 from - join - select이다.

또 다른 예로 다음과 같은 코드가 있을 때

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

SQL 쿼리 실행 순서는 from - join - where - group by - select이다.

따라서 밑에서 where등의 조건을 붙여줄 때, where이 먼저 실행된 후 select가 실행된다는 것에 유의해야 한다.

 

그리고 두 테이블 중에 해당 필드가 어디에서 왔는지 표시를 해주어야 한다. u.user_id, p.user_id 처럼.

 

많은 포인트를 얻은 순서대로 유저 데이터 정렬해서 보기

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

결제 수단 별 유저 포인트의 평균값 구해보기

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

결제하고 시작하지 않은 유저들을 성씨별로 세어보기

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

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

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 별 체크인 수

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일 이후에 구매한 고객들만 골라 보기. inner join을 두 번 한다.

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

left join도 써보자.

유저 중에, 포인트가 없는 사람(=즉, 시작하지 않은 사람들)의 통계를 낼 때

is NULL, 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

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

count는 NULL을 세지 않으므로 left join해줘도 된다.

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'

 

 

결과물을 합칠 때 select를 두 번 하기보다는 Union all을 해줄 수 있다. 단, 필드명이 같을 때만 가능하다.

그리고 한 가지 문제점은 order by가 적용되지 않는다. 이를 해결하기 위한 방법으로 SubQuery가 있다. (4주차에 배움)

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

 

enrolled_id별 수강완료(done=1)한 강의 갯수를 세고, 완료한 강의 수가 많은 순서대로 정렬하기. user_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