1. Join

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

# 원본
select * from users
where user_id = '4b8a10e6'

# 수정본

'오늘의 다짐' 이벤트 당첨자를 추첨한다면

• 한 테이블에 모든 정보를 담을 수도 있겠지만, 불필요하게 테이블의 크기가 커져 불편해진다.
  그래서, 데이터를 종류별로 쪼개 다른 테이블에 담아놓고 연결이 필요한 경우 연결할 수 있도록 만들어둔다.
  • 예시 : users와 checkins 테이블에 동시에 존재하는 user_id
    • 이런 필드를 두 테이블을 연결시켜주는 열쇠라는 의미로 'key'라고 부른다.

1) 실행 순서

1. Join 하고 싶은 테이블이 2개 있을 때, 먼저 각 테이블의 필드를 살펴본다.
   여기서 두 테이블에 공통되는 필드를 찾는다.

   select * from enrolls
   
   select * from courses

2. 각 테이블에 별칭을 줘서, 연결시킨다.

2) 사용법

(1) Inner Join

예시 1 : users 테이블에 point_users 테이블 연결

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

• Left Join 과는 달리, NULL 값이 없다.
  • 두 테이블에서 모두 가지고 있는 데이터만 출력했기 때문

예시 2 : join + count + group by - '오늘의 다짐' 정보에 과목 정보를 연결해, 과목별 '오늘의 다짐' 개수

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					# 과목별

예시 3 : join + order by - 유저의 포인트 정보가 담긴 테이블에 유저 정보를 연결해, 많은 포인트를 얻은 순서대로 유저의 데이터

select * from point_users p		# 유저의 포인트 정보가 담긴 테이블에
inner join users u				# 유저 정보를 연결해
on p.user_id = u.user_id
order by p.point desc			# 많은 포인트를 얻은 순서대로

예시 4 : join + count + where + like + group by - 주문 정보에 유저 정보를 연결해, 네이버 이메일을 사용하는 유저 중, 성씨별 주문건수

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						# 성씨별 주문건수

예시 5 : join + avg + group by - 결제 수단 별 유저 포인트의 평균값

select o.payment_method, round(avg(p.point),2) from point_users p		# 유저 포인트의 평균값
inner join orders o 
on p.user_id = o.user_id 
group by o.payment_method		# 결제 수단 별

예시 6 : join + count + where + group by + order by - 결제하고 시작하지 않은 유저들을 성씨별로 세어보기

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		# cnt_name 또는 count(*) 사용 가능

예시 7 : 2개의 필드 join + count + where + group by - 과목 별로 시작하지 않은 유저들을 세어보기

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.course_id			# 과목 별로

예시 8 : 2개의 필드 join - 웹개발, 앱개발 종합반의 week 별 체크인 수를 세어보기

select c1.title, c2.week, count(*) as cnt from courses c1		# c1 테이블		# 필드 : title, week
inner join checkins c2 on c1.course_id = c2.course_id		# c2 테이블			
group by c1.title, c2.week
order by c1.title, c2.week		# 첫 번째로 정렬할 것은 c1.title, 두 번째로 정렬할 것은 c2.week

• group by, order by에 콤마로 이어서 두 개 필드를 걸기
• order by c1.title, c2.week desc 로 한다면 week 만 내림차순 정렬된다.

예시 9 : 3개의 테이블 join - 8월 1일 이후에 구매한 고객들의 수

select c1.title, c2.week, count(*) as cnt from courses c1		# c1 테이블
inner join checkins c2 on c1.course_id = c2.course_id		# c2 테이블
inner join orders o on c2.user_id = o.user_id			# o 테이블 
where o.created_at >= '2020-08-01'
group by c1.title, c2.week
order by c1.title, c2.week

• courses에 checkins 를 붙이고 + checkins 에 orders 를 한번 더 붙이기

예시 10 : enrolled_id별 수강완료(done=1)한 강의 개수를 세고, 완료한 강의 수가 많은 순서대로 정렬(user_id도 같이 출력)

select e.enrolled_id,
	   e.user_id,				# user_id도 같이 출력
	   count(*) as cnt			# 강의 개수
  from enrolleds e
 inner join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id
 where ed.done = 1							# 수강완료(done=1)한
 group by e.enrolled_id, e.user_id			# enrolled_id별
 order by cnt desc							# 완료한 강의 수가 많은 순서대로 정렬

(1) Left Join

• 왼쪽에 있는 것을 기준으로 붙인다.

• 어디에 → 무엇을 붙일건지, 순서가 중요!

예시 1 : users 테이블에 point_users 테이블 연결(붙이기)

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

• NULL
  • 왼쪽을 기준으로 붙이는데, 오른쪽에 있던 테이블에는 왼쪽과는 달리 비워져있는 데이터들도 있기 때문

예시 2 : 유저 중에, 포인트가 없는 사람들 (시작하지 않은 사람들)

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		# 포인트가 있는 사람의 경우, is not NULL
group by name

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

select count(pu.point_user_id) as pnt_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											# u 테이블
  left join point_users pu on u.user_id = pu.user_id	# pu 테이블
 where u.created_at between '2020-07-10' and '2020-07-20'		# 7월10일 ~ 7월19일에 가입한 고객 중

• count 은 NULL을 세지 않는다.

2. Union

• 7월, 8월이 따로 있을 때 이 두 개를 이어주고 싶을 경우에 사용

1) 사용법

예시 1

# 원본
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

# 수정본
(
	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'			# 7월
	group by c2.course_id, c2.week
    # order by c2.course_id, c2.week		# union all 에서는 order by가 적용되지 않으므로, 빼줘도 무방함
)
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'			# 8월
	group by c2.course_id, c2.week
  	# order by c2.course_id, c2.week		# union all 에서는 order by가 적용되지 않으므로, 빼줘도 무방함
)

• (7월 부분) 과 (8월 부분)을 union all 한다.
• union all 에서 order by 를 적용시키고 싶다면, SubQuery(서브쿼리) 를 사용해야 한다. 참고: SQL - Subquery (서브쿼리)